-CREATE EXTENSION jsonb_opx;
+CREATE EXTENSION jsonb_opx VERSION '1.1';
-------------------------------------------------------------------------------
-- 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 elements
SELECT '[1, "1", "2", 2]'::jsonb - '2'::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?
{"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 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?
----------
{}
(1 row)
--- simple text[] deletion from an object container should only match keys or nulls
+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?
--------------------------
[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)
-SELECT '["1",null,2]'::jsonb - ARRAY[null];
+-------------------------------------------------------------------------------
+-- 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", 2]
+ []
+(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)
-------------------------------------------------------------------------------
-- jsonb deletion from an array should only match on key
SELECT '["a", "b", "c"]'::jsonb - '{"a": 4, "b": 2}'::jsonb;
- ?column?
-----------
- ["c"]
+ ?column?
+-----------------
+ ["a", "b", "c"]
(1 row)
-- jsonb deletion from nested objects should not be part matched
{"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?
{}
(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"
SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[[1]]'::jsonb;
-ERROR: invalid jsonb scalar type
+ ?column?
+----------------------------
+ ["a", 2, {"a": 1, "b": 2}]
+(1 row)
+
SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb;
-ERROR: invalid jsonb scalar type
+ ?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
-------------------------------------------------------------------------------
{"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": 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?
--------------------------------
{"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['a'];
?column?
----------
-
+ []
(1 row)
SELECT '["a"]'::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
--------------------
SELECT jsonb_replace_path('[null, "a"]', ARRAY[null], '"b"'::jsonb);
jsonb_replace_path
--------------------
- ["b", "a"]
+ [null, "a"]
(1 row)
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)
projects / postgresql / pg_jsonb_opx / blobdiff