X-Git-Url: https://git.8kb.co.uk/?p=postgresql%2Fpg_jsonb_opx;a=blobdiff_plain;f=expected%2Fjsonb_opx.out;fp=expected%2Fjsonb_opx.out;h=e11187b6470ab64b103db3be3b8b37556e04b77e;hp=d7cd3c98133860175325f967764deeb3adf36d70;hb=b2272ef16fe0e6aa9b769f4517e9fe7177e3f444;hpb=bf6318c60bd05043800c698a6b14f6aaa17a4824 diff --git a/expected/jsonb_opx.out b/expected/jsonb_opx.out index d7cd3c9..e11187b 100755 --- a/expected/jsonb_opx.out +++ b/expected/jsonb_opx.out @@ -2,7 +2,43 @@ CREATE EXTENSION jsonb_opx; ------------------------------------------------------------------------------- -- Tests for jsonb - text ------------------------------------------------------------------------------- --- text deletion from array containers will only delete string types as only strings can be keys: +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 types currently SELECT '[1, "1", "2", 2]'::jsonb - '2'::text; ?column? ------------- @@ -53,6 +89,26 @@ SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '3'::text; {"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? @@ -90,10 +146,16 @@ SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'b'::text; {"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) + ------------------------------------------------------------------------------- -- Tests for jsonb - text[] ------------------------------------------------------------------------------- --- text[] deletion from array containers will only delete string types as only strings can be keys: +-- text deletion from array containers will only delete string types currently SELECT '[1, "1", "2", 2]'::jsonb - array['1','2']; ?column? ---------- @@ -161,6 +223,30 @@ SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['a','d']; {} (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 or nulls SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['1',' 2']; ?column? @@ -174,13 +260,14 @@ SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '{a}'::text[]; [2, {"a": 1, "b": 2}] (1 row) -SELECT '["1",2]'::jsonb - ARRAY[null]; +-- here we treat SQL nulls and json nulls as equal - bad? +SELECT '["1",null,2]'::jsonb - ARRAY[null]; ?column? ---------- ["1", 2] (1 row) -SELECT '["1",null,2]'::jsonb - ARRAY[null]; +SELECT '["1",2]'::jsonb - ARRAY[null]; ?column? ---------- ["1", 2] @@ -210,13 +297,26 @@ SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"a": 4, "b": 2}'::js {"c": 3, "d": {"a": 4}} (1 row) --- but a match of all nested values should narcg +-- 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? @@ -297,7 +397,8 @@ SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::js (1 row) -- known issues !!!! --- lookups of lhs values in rhs jsonb use findJsonbValueFromContainer which does not allow looking up non-scalar elements resulting in "invalid jsonb scalar type" +-- lookups of lhs values in rhs jsonb use findJsonbValueFromContainer which does +-- not allow looking up non-scalar elements resulting in "invalid jsonb scalar type" SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[[1]]'::jsonb; ERROR: invalid jsonb scalar type SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb; @@ -312,6 +413,12 @@ SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"a": 4, "b": 2, "d": 4}'::jsonb; {"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? @@ -359,17 +466,55 @@ SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb || '["a","b"]'::jsonb; [{"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 +-- Tests for jsonb #= jsonb ------------------------------------------------------------------------------- --- any keys existing in left argument have values replaced with those from righ argument +-- 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 +-- 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? -------------------------------- @@ -400,6 +545,12 @@ SELECT '{"a":1, "b":2}'::jsonb #= '"a"'::jsonb; {"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) + ------------------------------------------------------------------------------- -- Tests for jsonb #- text[] ------------------------------------------------------------------------------- @@ -559,12 +710,95 @@ SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['a']; [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 -------------------- @@ -664,6 +898,18 @@ SELECT jsonb_replace_path('[null, "a"]', ARRAY[null], '"b"'::jsonb); SELECT jsonb_replace_path('[1,2,3,"4"]', ARRAY['4'], '"5"'::jsonb); jsonb_replace_path -------------------- - "5" + [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)