CREATE EXTENSION jsonb_opx VERSION '1.1'; ------------------------------------------------------------------------------- -- Tests for jsonb - text ------------------------------------------------------------------------------- SELECT '["a", "b"]'::jsonb - 'b'::text; ?column? ---------- ["a"] (1 row) SELECT '["a"]'::jsonb - 'b'::text; ?column? ---------- ["a"] (1 row) SELECT '"a"'::jsonb - 'b'::text; ?column? ---------- "a" (1 row) SELECT '[1]'::jsonb - 'b'::text; ?column? ---------- [1] (1 row) SELECT '1'::jsonb - 'b'::text; ?column? ---------- 1 (1 row) SELECT '["a", {"a":1}, "b"]'::jsonb - 'b'::text; ?column? ----------------- ["a", {"a": 1}] (1 row) -- text deletion from array containers will only delete string elements SELECT '[1, "1", "2", 2]'::jsonb - '2'::text; ?column? ------------- [1, "1", 2] (1 row) -- simple text deletion from an object container SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b'::text; ?column? ------------------ {"a": 1, "c": 3} (1 row) SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b '::text; ?column? -------------------------- {"a": 1, "b": 2, "c": 3} (1 row) SELECT '{"a": 1, "b": 2, "c": {"b": 3}}'::jsonb - 'b'::text; ?column? ------------------------- {"a": 1, "c": {"b": 3}} (1 row) SELECT '{"a": 1, "b": 2, "c": {"b": [1,2,3]}}'::jsonb - 'b'::text; ?column? --------------------------------- {"a": 1, "c": {"b": [1, 2, 3]}} (1 row) SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'b'::text; ?column? -------------------------- {"a": 1, "c": [1, 2, 3]} (1 row) SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'c'::text; ?column? ------------------ {"a": 1, "b": 2} (1 row) -- simple text deletion from an object container should only match keys SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '3'::text; ?column? -------------------------- {"a": 1, "b": 2, "c": 3} (1 row) -- deleting everything always results in an empty container SELECT '["a"]'::jsonb - 'a'::text; ?column? ---------- [] (1 row) SELECT '{"a":1}'::jsonb - 'a'::text; ?column? ---------- {} (1 row) -- even for scalars, but this should perhaps error SELECT '"a"'::jsonb - 'a'::text; ?column? ---------- [] (1 row) -- others SELECT '["1", "2", true, false]'::jsonb - '2'::text; ?column? -------------------- ["1", true, false] (1 row) SELECT '["1", "2", "2", "2"]'::jsonb - '2'::text; ?column? ---------- ["1"] (1 row) SELECT '["a",2,{"a":1, "b":2}]'::jsonb - 'a'::text; ?column? ----------------------- [2, {"a": 1, "b": 2}] (1 row) SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'a'::text; ?column? ---------- {"d": 2} (1 row) SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'd'::text; ?column? ------------------------------------ {"a": {"b": 3, "c": [1, 2, 3, 4]}} (1 row) SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'b'::text; ?column? -------------------------------------------- {"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2} (1 row) SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - 'a'::text; ?column? ---------- {"d": 2} (1 row) -- function is strict, so - null returns null - assume SQL nulls and jsonb nulls are not equal anyway SELECT '["1", "2", true, null]'::jsonb - null::text; ?column? ---------- (1 row) ------------------------------------------------------------------------------- -- Tests for jsonb - numeric ------------------------------------------------------------------------------- -- Only matches numeric array element types SELECT '[1, "1", "2", 2]'::jsonb - 2; ?column? --------------- [1, "1", "2"] (1 row) SELECT '[2]'::jsonb - 2; ?column? ---------- [] (1 row) SELECT '2'::jsonb - 2; ?column? ---------- [] (1 row) -- Does nothing for objects SELECT '{"2":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 2; ?column? -------------------------------------------- {"2": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2} (1 row) ------------------------------------------------------------------------------- -- Tests for jsonb - boolean ------------------------------------------------------------------------------- -- Only matches boolean array element types SELECT '[1, "1", false, true, null]'::jsonb - false; ?column? ---------------------- [1, "1", true, null] (1 row) SELECT '[1, "1", false, true, null]'::jsonb - true; ?column? ----------------------- [1, "1", false, null] (1 row) ------------------------------------------------------------------------------- -- Tests for jsonb - text[] ------------------------------------------------------------------------------- -- text deletion from array containers will only delete string types currently SELECT '[1, "1", "2", 2]'::jsonb - array['1','2']; ?column? ---------- [1, 2] (1 row) -- simple text[] deletion from an object container SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b']; ?column? ---------- {"c": 3} (1 row) SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a ','b ',' c']; ?column? -------------------------- {"a": 1, "b": 2, "c": 3} (1 row) SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b','c']; ?column? ---------- {} (1 row) SELECT '{"a": 1, "b": 2, "c": {"b": 3}}'::jsonb - ARRAY['a','b']; ?column? ----------------- {"c": {"b": 3}} (1 row) SELECT '{"a": 1, "b": 2, "c": {"b": [1,2,3]}}'::jsonb - ARRAY['a','b']; ?column? ------------------------- {"c": {"b": [1, 2, 3]}} (1 row) SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','b']; ?column? ------------------ {"c": [1, 2, 3]} (1 row) SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','c']; ?column? ---------- {"b": 2} (1 row) SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','d']; ?column? ------------------------------------ {"a": {"b": 3, "c": [1, 2, 3, 4]}} (1 row) SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','a']; ?column? ---------- {"d": 2} (1 row) SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['a','d']; ?column? ---------- {} (1 row) SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb - ARRAY['a','d']; ?column? ---------- {} (1 row) SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":[2,[2],[1,2,{"a":2},{"b":[1,[2]]}]]}'::jsonb - ARRAY['a']; ?column? ---------------------------------------------------- {"d": [2, [2], [1, 2, {"a": 2}, {"b": [1, [2]]}]]} (1 row) SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":[2,[2],[1,2,{"a":2},{"b":[1,[2]]}]]}'::jsonb - ARRAY['d']; ?column? ------------------------------------------------------------- {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}} (1 row) SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - '{a}'::text[]; ?column? ---------- {"d": 2} (1 row) -- simple text[] deletion from an object container should only match keys SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['1',' 2']; ?column? -------------------------- {"a": 1, "b": 2, "c": 3} (1 row) SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '{a}'::text[]; ?column? ----------------------- [2, {"a": 1, "b": 2}] (1 row) -- SQL nulls and jsonb nulls are not equal SELECT '["1",null,2]'::jsonb - ARRAY[null]; ?column? ---------------- ["1", null, 2] (1 row) SELECT '["1",2]'::jsonb - ARRAY[null]; ?column? ---------- ["1", 2] (1 row) ------------------------------------------------------------------------------- -- Tests for jsonb - numeric[] ------------------------------------------------------------------------------- -- Only matches numeric array element types SELECT '[1, "1", "2", 2]'::jsonb - ARRAY[2]; ?column? --------------- [1, "1", "2"] (1 row) SELECT '[1, "1", "2", 2]'::jsonb - ARRAY[1,2]; ?column? ------------ ["1", "2"] (1 row) SELECT '[2]'::jsonb - ARRAY[1,2]; ?column? ---------- [] (1 row) SELECT '2'::jsonb - ARRAY[1,2]; ?column? ---------- [] (1 row) -- Does nothing for objects SELECT '{"2":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY[1,2]; ?column? -------------------------------------------- {"2": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2} (1 row) ------------------------------------------------------------------------------- -- Tests for jsonb - boolean[] ------------------------------------------------------------------------------- -- Only matches boolean array element types SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[false]; ?column? ---------------------- [1, "1", true, null] (1 row) SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[true]; ?column? ----------------------- [1, "1", false, null] (1 row) SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[true, false]; ?column? ---------------- [1, "1", null] (1 row) -- Again nulls are not equal SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[true, false, null]; ?column? ---------------- [1, "1", null] (1 row) ------------------------------------------------------------------------------- -- Tests for jsonb - jsonb ------------------------------------------------------------------------------- -- jsonb deletion from an object should match on key/value SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{"a": 4, "b": 2}'::jsonb; ?column? ------------------ {"a": 1, "c": 3} (1 row) -- jsonb deletion from an array should only match on key SELECT '["a", "b", "c"]'::jsonb - '{"a": 4, "b": 2}'::jsonb; ?column? ----------------- ["a", "b", "c"] (1 row) -- jsonb deletion from nested objects should not be part matched SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"a": 4, "b": 2}'::jsonb; ?column? ------------------------- {"c": 3, "d": {"a": 4}} (1 row) -- but a match of all nested values should SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"d": {"a": 4}, "b": 2}'::jsonb; ?column? ------------------ {"a": 4, "c": 3} (1 row) SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - '{"d":2}'::jsonb; ?column? -------------------------------------------------------------------- {"a": {"b": {"c": [1, [2, 3, [4]], {"d": 1}]}, "c": [1, 2, 3, 4]}} (1 row) -- jsonb nulls are equal SELECT '{"a": 1, "b": 2, "c": null}'::jsonb - '{"a": 4, "c": null}'::jsonb; ?column? ------------------ {"a": 1, "b": 2} (1 row) -- others SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": false}}'::jsonb - '{"d": {"a": false}, "b": 2}'::jsonb; ?column? ------------------ {"a": 4, "c": 3} (1 row) SELECT '{"a": "test", "b": 2.2, "c": {"a": false}}'::jsonb - '{"a": "test2", "c": {"a": false}, "b": 2.2}'::jsonb; ?column? --------------- {"a": "test"} (1 row) SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb; ?column? ------------------------------------------------------------------------- {"a": "test", "b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]} (1 row) SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb; ?column? ------------------------------------------------------------ {"b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]} (1 row) SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.2, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb; ?column? --------------------------------------------------------------- {"a": "test", "c": {"a": false}, "d": true, "e": [1, 2, "a"]} (1 row) SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": false}, "d":false, "e":[1,2,3]}'::jsonb; ?column? ------------------------------------------------------ {"a": "test", "b": 2.2, "d": true, "e": [1, 2, "a"]} (1 row) SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":true, "e":[1,2,3]}'::jsonb; ?column? -------------------------------------------------------------- {"a": "test", "b": 2.2, "c": {"a": false}, "e": [1, 2, "a"]} (1 row) SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,"a",2]}'::jsonb; ?column? ------------------------------------------------------------------------- {"a": "test", "b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]} (1 row) SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,2,"a"]}'::jsonb; ?column? ------------------------------------------------------- {"a": "test", "b": 2.2, "c": {"a": false}, "d": true} (1 row) SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb; ?column? -------------------------------------------------- {"c": {"a": false}, "d": true, "e": [1, 2, "a"]} (1 row) SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": false}, "d":false, "e":[1,2,3]}'::jsonb; ?column? ------------------------------- {"d": true, "e": [1, 2, "a"]} (1 row) SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,3]}'::jsonb; ?column? -------------------- {"e": [1, 2, "a"]} (1 row) SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb; ?column? ---------- {} (1 row) SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[[1]]'::jsonb; ?column? ---------------------------- ["a", 2, {"a": 1, "b": 2}] (1 row) SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb; ?column? ---------------------------- ["a", 2, {"a": 1, "b": 2}] (1 row) SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1, "b":2}]'::jsonb; ?column? ---------- ["a", 2] (1 row) SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '["a"]'::jsonb; ?column? ----------------------- [2, {"a": 1, "b": 2}] (1 row) SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[2]'::jsonb; ?column? ------------------------- ["a", {"a": 1, "b": 2}] (1 row) ------------------------------------------------------------------------------- -- Tests for jsonb || jsonb ------------------------------------------------------------------------------- -- duplicates should automatically be removed by lower level logic SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"a": 4, "b": 2, "d": 4}'::jsonb; ?column? ---------------------------------- {"a": 4, "b": 2, "c": 3, "d": 4} (1 row) SELECT '{"a": 1, "b": null, "c": 3}'::jsonb || '{"a": 4, "b": null, "d": 4}'::jsonb; ?column? ------------------------------------- {"a": 4, "b": null, "c": 3, "d": 4} (1 row) -- concatentation of arrays SELECT '["a", "b"]'::jsonb || '["c"]'::jsonb; ?column? ----------------- ["a", "b", "c"] (1 row) -- concatentation of scalars and arrays should be wrapped into arrays SELECT '["a", "b"]'::jsonb || '"c"'::jsonb; ?column? ----------------- ["a", "b", "c"] (1 row) -- likewise concatentation of objects and arrays should be wrapped into arrays SELECT '["a", "b"]'::jsonb || '{"a": 4, "b": 2}'::jsonb; ?column? ------------------------------ ["a", "b", {"a": 4, "b": 2}] (1 row) -- and all concatentation should be in natural order supplied SELECT '{"a": 4, "b": 2}'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb; ?column? ---------------------------------------- [{"a": 4, "b": 2}, "a", "b", "c", "d"] (1 row) -- others SELECT 'false'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb; ?column? ----------------------------- [false, "a", "b", "c", "d"] (1 row) SELECT '["a","b"]'::jsonb || '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb; ?column? -------------------------------------------------------- ["a", "b", {"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}] (1 row) SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb || '["a","b"]'::jsonb; ?column? -------------------------------------------------------- [{"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}, "a", "b"] (1 row) SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb || '["a",["b","c",["3",1,2,[9,3,{"s":"o"},"x"]]],{"f":9}]'::jsonb; ?column? ---------------------------------------------------------------------------------------------------------------------------------------- [{"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2}, "a", ["b", "c", ["3", 1, 2, [9, 3, {"s": "o"}, "x"]]], {"f": 9}] (1 row) SELECT'["a",["b","c",["3",1,2,[9,3,{"s":"o"},"x"]]],{"f":9}]'::jsonb || '["a",["b","c",["3",1,2,[9,3,{"s":"o"},"x"]]],{"f":9}]'::jsonb; ?column? ------------------------------------------------------------------------------------------------------------------------------------ ["a", ["b", "c", ["3", 1, 2, [9, 3, {"s": "o"}, "x"]]], {"f": 9}, "a", ["b", "c", ["3", 1, 2, [9, 3, {"s": "o"}, "x"]]], {"f": 9}] (1 row) SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb || '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb; ?column? --------------------------------------------------------------------- {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2} (1 row) ------------------------------------------------------------------------------- -- Tests for jsonb #= jsonb ------------------------------------------------------------------------------- -- any keys existing in left argument have values replaced with those from righ -- argument SELECT '{"a": 1}'::jsonb #= '{"a": [1,2,3,4]}'::jsonb; ?column? --------------------- {"a": [1, 2, 3, 4]} (1 row) SELECT '{"a": 1}'::jsonb #= '{"a": [1,2,3,4], "b":2}'::jsonb; ?column? --------------------- {"a": [1, 2, 3, 4]} (1 row) SELECT '{"a": 1, "b":1}'::jsonb #= '{"a": [1,2,3,4], "b":2}'::jsonb; ?column? ----------------------------- {"a": [1, 2, 3, 4], "b": 2} (1 row) SELECT '{"a": 1, "b": 2, "c":[1,2,3], "d":{"test":false}}'::jsonb #= '{"a": [1,2,3,4], "b": {"f":100, "j":{"k":200}}, "c": 4, "d":{"test":true}}'::jsonb; ?column? ------------------------------------------------------------------------------------ {"a": [1, 2, 3, 4], "b": {"f": 100, "j": {"k": 200}}, "c": 4, "d": {"test": true}} (1 row) -- note that as we are matching only keys and replacing values operation on an -- scalar/array elements effectively does nothing SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '["a","b","c"]'::jsonb; ?column? -------------------------------- {"a": [1, 2], "b": 2, "c": 12} (1 row) SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '[1,2,3]'::jsonb; ?column? -------------------------------- {"a": [1, 2], "b": 2, "c": 12} (1 row) SELECT '[1,2,3]'::jsonb #= '[1,2,3,4]'::jsonb; ?column? ----------- [1, 2, 3] (1 row) SELECT '"a"'::jsonb #= '{"a":1, "b":2}'::jsonb; ?column? ---------- "a" (1 row) SELECT '{"a":1, "b":2}'::jsonb #= '"a"'::jsonb; ?column? ------------------ {"a": 1, "b": 2} (1 row) SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #= '{"a":{"b":3, "c":[1,{"r":[true,{"u":2}]},3,4,5]}}'::jsonb; ?column? --------------------------------------------------------------------- {"a": {"b": 3, "c": [1, {"r": [true, {"u": 2}]}, 3, 4, 5]}, "d": 2} (1 row) SELECT '["a","b","c"]'::jsonb #= '{"a":1}'::jsonb; ?column? ----------------- ["a", "b", "c"] (1 row) ------------------------------------------------------------------------------- -- Tests for jsonb #- text[] ------------------------------------------------------------------------------- SELECT '"a"'::jsonb #- ARRAY['b']; ?column? ---------- "a" (1 row) SELECT '["a"]'::jsonb #- ARRAY['b']; ?column? ---------- ["a"] (1 row) SELECT '{"a":1}'::jsonb #- ARRAY['b']; ?column? ---------- {"a": 1} (1 row) SELECT '"a"'::jsonb #- ARRAY['a']; ?column? ---------- [] (1 row) SELECT '["a"]'::jsonb #- ARRAY['a']; ?column? ---------- [] (1 row) SELECT '{"a":1}'::jsonb #- ARRAY['a']; ?column? ---------- {} (1 row) SELECT '["a", "b"]'::jsonb #- ARRAY['a']; ?column? ---------- ["b"] (1 row) SELECT '{"a":1, "b":2}'::jsonb #- ARRAY['a']; ?column? ---------- {"b": 2} (1 row) SELECT '{"a":{"b":1}, "c":2}'::jsonb #- ARRAY['a']; ?column? ---------- {"c": 2} (1 row) SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['a']; ?column? ---------- {"b": 2} (1 row) SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['b']; ?column? --------------------- {"a": [1, 2, 3, 4]} (1 row) SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['c']; ?column? ------------------------------------- {"a": {"b": [1, 2, 3, ["a", "b"]]}} (1 row) SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a']; ?column? ---------- {"c": 2} (1 row) SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','c']; ?column? --------------------------------------------- {"a": {"b": [1, 2, 3, ["a", "b"]]}, "c": 2} (1 row) SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b']; ?column? ------------------- {"a": {}, "c": 2} (1 row) SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b','c']; ?column? --------------------------------------------- {"a": {"b": [1, 2, 3, ["a", "b"]]}, "c": 2} (1 row) SELECT '{"a":{"b":{"c":1}, "c":[1,2,3,["a","b"]]}, "d":3}'::jsonb #- ARRAY['a','b','c']; ?column? ------------------------------------------------------ {"a": {"b": {}, "c": [1, 2, 3, ["a", "b"]]}, "d": 3} (1 row) SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b']; ?column? ------------------------------------ {"a": {"c": [1, 2, 3, 4]}, "d": 2} (1 row) SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c']; ?column? --------------------------------------------- {"a": {"b": [1, 2, 3, ["a", "b"]]}, "d": 2} (1 row) SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a',null]; ?column? ---------------------------------------------------------------- {"a": {"b": [1, 2, 3, ["a", "b"]], "c": [1, 2, 3, 4]}, "d": 2} (1 row) SELECT '{"a":{"b":[1,2,3,["a",{"b":3}]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b']; ?column? ------------------------------------ {"a": {"c": [1, 2, 3, 4]}, "d": 2} (1 row) SELECT '{"a":{"b":3, "d":[1,{"Z":[1,[2,3]]}]}}'::jsonb #- ARRAY['a','d']; ?column? ----------------- {"a": {"b": 3}} (1 row) SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['a']; ?column? ---------------------------------- [{"b": [1, 2, 3, 4, 5]}, 1, "c"] (1 row) SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['c']; ?column? ---------------------------------- ["a", {"b": [1, 2, 3, 4, 5]}, 1] (1 row) SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['b']; ?column? ------------------------------------ [1, [2, [3, [4, [5, 6, 7]]]], "a"] (1 row) SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['a']; ?column? ------------------------------------ [1, [2, [3, [4, [5, 6, 7]]]], "b"] (1 row) SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b']; ?column? ------------------------------------------------------------- {"a": {"c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2} (1 row) SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c']; ?column? ------------------------- {"a": {"b": 3}, "d": 2} (1 row) SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','d']; ?column? --------------------------------------------------------------------- {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2} (1 row) SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['d']; ?column? ------------------------------------------------------------- {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}} (1 row) SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c','r']; ?column? --------------------------------------------------------------------- {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2} (1 row) SELECT '{"a":{"b":3, "c":{"r":{"u":1}}}, "d":2}'::jsonb #- ARRAY['a','c','r']; ?column? ---------------------------------- {"a": {"b": 3, "c": {}}, "d": 2} (1 row) SELECT '{"a":{"b":3, "c":{"r":{"u":1}}}, "d":2}'::jsonb #- ARRAY['a','c','r','u']; ?column? ----------------------------------------- {"a": {"b": 3, "c": {"r": {}}}, "d": 2} (1 row) -- expected limitation: cannot call with path deeper than 1 on a non-object SELECT '["a", "b"]'::jsonb #- ARRAY['a','b']; ERROR: cannot call with path deeper than 1 on a non-object ------------------------------------------------------------------------------- -- Tests for jsonb_replace_path jsonb text[] ------------------------------------------------------------------------------- -- if the replacement on an object/array is passed as a scalar/array the value/element -- is replaced SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '3'::jsonb); jsonb_replace_path -------------------- {"a": 3, "b": 2} (1 row) SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '[3]'::jsonb); jsonb_replace_path -------------------- {"a": [3], "b": 2} (1 row) SELECT jsonb_replace_path('["a", "b"]', ARRAY['a'], '3'::jsonb); jsonb_replace_path -------------------- [3, "b"] (1 row) SELECT jsonb_replace_path('["a", "b"]', ARRAY['a'], '[3]'::jsonb); jsonb_replace_path -------------------- [3, "b"] (1 row) -- if the replacement on an object/array is passed as an object the whole key-value -- pair is replaced. This difference is perhaps confusing, but otherwise there is -- no way to directly replace a key without deletion and concatenation. SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"z":3}'::jsonb); jsonb_replace_path -------------------- {"b": 2, "z": 3} (1 row) SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"a":{"z":3}}'::jsonb); jsonb_replace_path ------------------------- {"a": {"z": 3}, "b": 2} (1 row) SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"f":3}'::jsonb); jsonb_replace_path -------------------- {"b": 2, "f": 3} (1 row) SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a'], '{"f":3}'::jsonb); jsonb_replace_path -------------------- {"f": 3} (1 row) SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a','b'], '{"f":3}'::jsonb); jsonb_replace_path -------------------- {"a": {"f": 3}} (1 row) SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','b'], '{"f":3}'::jsonb); jsonb_replace_path ------------------------- {"a": {"c": 1, "f": 3}} (1 row) SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','c'], '{"f":3}'::jsonb); jsonb_replace_path ------------------------- {"a": {"b": 1, "f": 3}} (1 row) SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','b'], '{"f":3}'::jsonb); jsonb_replace_path -------------------------------------- {"a": {"c": 2, "d": [1, 2], "f": 3}} (1 row) SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','c'], '{"f":3}'::jsonb); jsonb_replace_path -------------------------------------- {"a": {"b": 1, "d": [1, 2], "f": 3}} (1 row) SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','d'], '{"f":3}'::jsonb); jsonb_replace_path --------------------------------- {"a": {"b": 1, "c": 2, "f": 3}} (1 row) SELECT jsonb_replace_path('{"a":{"d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb); jsonb_replace_path -------------------- {"a": {"f": 3}} (1 row) SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','b'], '{"f":3}'::jsonb); jsonb_replace_path ------------------------------------------------------- {"a": {"c": 2, "d": [1, {"Z": [1, [2, 3]]}], "f": 3}} (1 row) SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','c'], '{"f":3}'::jsonb); jsonb_replace_path ------------------------------------------------------- {"a": {"b": 1, "d": [1, {"Z": [1, [2, 3]]}], "f": 3}} (1 row) SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb); jsonb_replace_path --------------------------------- {"a": {"b": 1, "c": 2, "f": 3}} (1 row) SELECT jsonb_replace_path('{"a":1, "b":null, "c":[1,2,null], "c":{"d":11}, "e":{"20":[100,"c"]}}', ARRAY['c'], '{"f":[[1],2], "g":"test", "h":{"i":{"j":null}}}'); jsonb_replace_path --------------------------------------------------------------------------------------------------- {"a": 1, "b": null, "e": {"20": [100, "c"]}, "f": [[1], 2], "g": "test", "h": {"i": {"j": null}}} (1 row) SELECT jsonb_replace_path('"a"', ARRAY['a'], '{"f":10}'::jsonb); jsonb_replace_path -------------------- {"f": 10} (1 row) SELECT jsonb_replace_path('"a"', ARRAY['z'], '{"f":10}'::jsonb); jsonb_replace_path -------------------- "a" (1 row) SELECT jsonb_replace_path('[null, "a"]', ARRAY[null], '"b"'::jsonb); jsonb_replace_path -------------------- [null, "a"] (1 row) SELECT jsonb_replace_path('[1,2,3,"4"]', ARRAY['4'], '"5"'::jsonb); jsonb_replace_path -------------------- [1, 2, 3, "5"] (1 row) SELECT jsonb_replace_path('{"a":{"b":3, "c":{"r":{"u":1}}}, "d":2}'::jsonb, ARRAY['a','c','r','u'],'{"a":{"b":3, "c":{"r":{"u":1}}}}'::jsonb); jsonb_replace_path ---------------------------------------------------------------------------- {"a": {"b": 3, "c": {"r": {"a": {"b": 3, "c": {"r": {"u": 1}}}}}}, "d": 2} (1 row) SELECT jsonb_replace_path('{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb, ARRAY['a','b'], '{"a":{"b":3, "c":{"r":{"u":1}}}}'::jsonb); jsonb_replace_path -------------------------------------------------------------------------------------------------- {"a": {"a": {"b": 3, "c": {"r": {"u": 1}}}, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2} (1 row)