X-Git-Url: https://git.8kb.co.uk/?p=postgresql%2Fpg_jsonb_opx;a=blobdiff_plain;f=sql%2Fjsonb_opx.sql;fp=sql%2Fjsonb_opx.sql;h=7ad02b4b481c5d988e936dfe5b2f56efa474fc7f;hp=a270fe96aa14c75cf2c28135cc1f0a0b090dbbab;hb=b2272ef16fe0e6aa9b769f4517e9fe7177e3f444;hpb=bf6318c60bd05043800c698a6b14f6aaa17a4824

diff --git a/sql/jsonb_opx.sql b/sql/jsonb_opx.sql
index a270fe9..7ad02b4 100755
--- a/sql/jsonb_opx.sql
+++ b/sql/jsonb_opx.sql
@@ -4,7 +4,14 @@ 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;
+SELECT '["a"]'::jsonb - 'b'::text;
+SELECT '"a"'::jsonb - 'b'::text;
+SELECT '[1]'::jsonb - 'b'::text;
+SELECT '1'::jsonb - 'b'::text;
+SELECT '["a", {"a":1}, "b"]'::jsonb - 'b'::text;
+
+-- text deletion from array containers will only delete string types currently
 SELECT '[1, "1", "2", 2]'::jsonb - '2'::text;
 
 -- simple text deletion from an object container
@@ -18,6 +25,13 @@ SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'c'::text;
 -- simple text deletion from an object container should only match keys
 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '3'::text;
 
+-- deleting everything always results in an empty container
+SELECT '["a"]'::jsonb - 'a'::text;
+SELECT '{"a":1}'::jsonb - 'a'::text;
+
+-- even for scalars, but this should perhaps error
+SELECT '"a"'::jsonb - 'a'::text;
+
 -- others
 SELECT '["1", "2", true, false]'::jsonb - '2'::text;
 SELECT '["1", "2", "2", "2"]'::jsonb - '2'::text;
@@ -25,12 +39,13 @@ SELECT '["a",2,{"a":1, "b":2}]'::jsonb - 'a'::text;
 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'a'::text;
 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'd'::text;
 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'b'::text;
+SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - 'a'::text;
 
 -------------------------------------------------------------------------------
 -- 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'];
 
 -- simple text[] deletion from an object container
@@ -44,12 +59,18 @@ SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','c'];
 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','d'];
 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','a'];
 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['a','d'];
+SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb - ARRAY['a','d'];
+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'];
+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'];
+SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - '{a}'::text[]; 
 
 -- simple text[] deletion from an object container should only match keys or nulls
 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['1',' 2'];
 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '{a}'::text[];
-SELECT '["1",2]'::jsonb - ARRAY[null];
+
+-- here we treat SQL nulls and json nulls as equal - bad?
 SELECT '["1",null,2]'::jsonb - ARRAY[null];
+SELECT '["1",2]'::jsonb - ARRAY[null];
 
 -------------------------------------------------------------------------------
 -- Tests for jsonb - jsonb
@@ -64,8 +85,12 @@ SELECT '["a", "b", "c"]'::jsonb - '{"a": 4, "b": 2}'::jsonb;
 -- 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;
 
--- 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;
+SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - '{"d":2}'::jsonb;
+
+-- jsonb nulls are equal
+SELECT '{"a": 1, "b": 2, "c": null}'::jsonb - '{"a": 4, "c": null}'::jsonb;
 
 -- others
 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": false}}'::jsonb - '{"d": {"a": false}, "b": 2}'::jsonb;
@@ -83,7 +108,8 @@ SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::js
 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;
 
 -- 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;
 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb;
 
@@ -93,6 +119,7 @@ SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::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;
+SELECT '{"a": 1, "b": null, "c": 3}'::jsonb || '{"a": 4, "b": null, "d": 4}'::jsonb;
 
 -- concatentation of arrays
 SELECT '["a", "b"]'::jsonb || '["c"]'::jsonb;
@@ -110,20 +137,31 @@ SELECT '{"a": 4, "b": 2}'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
 SELECT 'false'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
 SELECT '["a","b"]'::jsonb || '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb;
 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb || '["a","b"]'::jsonb;
+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;
+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;
+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;
 
 -------------------------------------------------------------------------------
--- 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;
+SELECT '{"a": 1}'::jsonb #= '{"a": [1,2,3,4], "b":2}'::jsonb;
+SELECT '{"a": 1, "b":1}'::jsonb #= '{"a": [1,2,3,4], "b":2}'::jsonb;
+
 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;
 
--- 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;
 SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '[1,2,3]'::jsonb;
 SELECT '[1,2,3]'::jsonb #= '[1,2,3,4]'::jsonb;
 SELECT '"a"'::jsonb #= '{"a":1, "b":2}'::jsonb;
 SELECT '{"a":1, "b":2}'::jsonb #= '"a"'::jsonb;
+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;
+
 
 -------------------------------------------------------------------------------
 -- Tests for jsonb #- text[] 
@@ -162,6 +200,13 @@ SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['a'];
 SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['c'];
 SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['b'];
 SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['a'];
+SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b']; 
+SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c']; 
+SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','d'];
+SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['d'];
+SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c','r']; 
+SELECT '{"a":{"b":3, "c":{"r":{"u":1}}}, "d":2}'::jsonb #- ARRAY['a','c','r'];  
+SELECT '{"a":{"b":3, "c":{"r":{"u":1}}}, "d":2}'::jsonb #- ARRAY['a','c','r','u'];
 
 -- expected limitation: cannot call with path deeper than 1 on a non-object
 SELECT '["a", "b"]'::jsonb #- ARRAY['a','b'];
@@ -169,6 +214,19 @@ SELECT '["a", "b"]'::jsonb #- ARRAY['a','b'];
 -------------------------------------------------------------------------------
 -- 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);
+SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '[3]'::jsonb);
+SELECT jsonb_replace_path('["a", "b"]', ARRAY['a'], '3'::jsonb);
+SELECT jsonb_replace_path('["a", "b"]', ARRAY['a'], '[3]'::jsonb);
+-- 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);
+SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"a":{"z":3}}'::jsonb);
+
 SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"f":3}'::jsonb);
 SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a'], '{"f":3}'::jsonb);
 SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a','b'], '{"f":3}'::jsonb);
@@ -189,3 +247,6 @@ SELECT jsonb_replace_path('"a"', ARRAY['a'], '{"f":10}'::jsonb);
 SELECT jsonb_replace_path('"a"', ARRAY['z'], '{"f":10}'::jsonb); 
 SELECT jsonb_replace_path('[null, "a"]', ARRAY[null], '"b"'::jsonb); 
 SELECT jsonb_replace_path('[1,2,3,"4"]', ARRAY['4'], '"5"'::jsonb); 
+
+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);
+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);