1 CREATE EXTENSION jsonb_opx;
2 -------------------------------------------------------------------------------
3 -- Tests for jsonb - text
4 -------------------------------------------------------------------------------
5 -- text deletion from array containers will only delete string types as only strings can be keys:
6 SELECT '[1, "1", "2", 2]'::jsonb - '2'::text;
12 -- simple text deletion from an object container
13 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b'::text;
19 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b '::text;
21 --------------------------
22 {"a": 1, "b": 2, "c": 3}
25 SELECT '{"a": 1, "b": 2, "c": {"b": 3}}'::jsonb - 'b'::text;
27 -------------------------
28 {"a": 1, "c": {"b": 3}}
31 SELECT '{"a": 1, "b": 2, "c": {"b": [1,2,3]}}'::jsonb - 'b'::text;
33 ---------------------------------
34 {"a": 1, "c": {"b": [1, 2, 3]}}
37 SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'b'::text;
39 --------------------------
40 {"a": 1, "c": [1, 2, 3]}
43 SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'c'::text;
49 -- simple text deletion from an object container should only match keys
50 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '3'::text;
52 --------------------------
53 {"a": 1, "b": 2, "c": 3}
57 SELECT '["1", "2", true, false]'::jsonb - '2'::text;
63 SELECT '["1", "2", "2", "2"]'::jsonb - '2'::text;
69 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - 'a'::text;
71 -----------------------
75 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'a'::text;
81 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'd'::text;
83 ------------------------------------
84 {"a": {"b": 3, "c": [1, 2, 3, 4]}}
87 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'b'::text;
89 --------------------------------------------
90 {"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}
93 -------------------------------------------------------------------------------
94 -- Tests for jsonb - text[]
95 -------------------------------------------------------------------------------
96 -- text[] deletion from array containers will only delete string types as only strings can be keys:
97 SELECT '[1, "1", "2", 2]'::jsonb - array['1','2'];
103 -- simple text[] deletion from an object container
104 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b'];
110 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a ','b ',' c'];
112 --------------------------
113 {"a": 1, "b": 2, "c": 3}
116 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b','c'];
122 SELECT '{"a": 1, "b": 2, "c": {"b": 3}}'::jsonb - ARRAY['a','b'];
128 SELECT '{"a": 1, "b": 2, "c": {"b": [1,2,3]}}'::jsonb - ARRAY['a','b'];
130 -------------------------
131 {"c": {"b": [1, 2, 3]}}
134 SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','b'];
140 SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','c'];
146 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','d'];
148 ------------------------------------
149 {"a": {"b": 3, "c": [1, 2, 3, 4]}}
152 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','a'];
158 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['a','d'];
164 -- simple text[] deletion from an object container should only match keys or nulls
165 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['1',' 2'];
167 --------------------------
168 {"a": 1, "b": 2, "c": 3}
171 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '{a}'::text[];
173 -----------------------
174 [2, {"a": 1, "b": 2}]
177 SELECT '["1",2]'::jsonb - ARRAY[null];
183 SELECT '["1",null,2]'::jsonb - ARRAY[null];
189 -------------------------------------------------------------------------------
190 -- Tests for jsonb - jsonb
191 -------------------------------------------------------------------------------
192 -- jsonb deletion from an object should match on key/value
193 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
199 -- jsonb deletion from an array should only match on key
200 SELECT '["a", "b", "c"]'::jsonb - '{"a": 4, "b": 2}'::jsonb;
206 -- jsonb deletion from nested objects should not be part matched
207 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
209 -------------------------
210 {"c": 3, "d": {"a": 4}}
213 -- but a match of all nested values should narcg
214 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"d": {"a": 4}, "b": 2}'::jsonb;
221 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": false}}'::jsonb - '{"d": {"a": false}, "b": 2}'::jsonb;
227 SELECT '{"a": "test", "b": 2.2, "c": {"a": false}}'::jsonb - '{"a": "test2", "c": {"a": false}, "b": 2.2}'::jsonb;
233 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;
235 -------------------------------------------------------------------------
236 {"a": "test", "b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
239 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;
241 ------------------------------------------------------------
242 {"b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
245 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;
247 ---------------------------------------------------------------
248 {"a": "test", "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
251 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;
253 ------------------------------------------------------
254 {"a": "test", "b": 2.2, "d": true, "e": [1, 2, "a"]}
257 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;
259 --------------------------------------------------------------
260 {"a": "test", "b": 2.2, "c": {"a": false}, "e": [1, 2, "a"]}
263 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;
265 -------------------------------------------------------------------------
266 {"a": "test", "b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
269 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;
271 -------------------------------------------------------
272 {"a": "test", "b": 2.2, "c": {"a": false}, "d": true}
275 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;
277 --------------------------------------------------
278 {"c": {"a": false}, "d": true, "e": [1, 2, "a"]}
281 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;
283 -------------------------------
284 {"d": true, "e": [1, 2, "a"]}
287 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;
293 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;
300 -- lookups of lhs values in rhs jsonb use findJsonbValueFromContainer which does not allow looking up non-scalar elements resulting in "invalid jsonb scalar type"
301 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[[1]]'::jsonb;
302 ERROR: invalid jsonb scalar type
303 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb;
304 ERROR: invalid jsonb scalar type
305 -------------------------------------------------------------------------------
306 -- Tests for jsonb || jsonb
307 -------------------------------------------------------------------------------
308 -- duplicates should automatically be removed by lower level logic
309 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"a": 4, "b": 2, "d": 4}'::jsonb;
311 ----------------------------------
312 {"a": 4, "b": 2, "c": 3, "d": 4}
315 -- concatentation of arrays
316 SELECT '["a", "b"]'::jsonb || '["c"]'::jsonb;
322 -- concatentation of scalars and arrays should be wrapped into arrays
323 SELECT '["a", "b"]'::jsonb || '"c"'::jsonb;
329 -- likewise concatentation of objects and arrays should be wrapped into arrays
330 SELECT '["a", "b"]'::jsonb || '{"a": 4, "b": 2}'::jsonb;
332 ------------------------------
333 ["a", "b", {"a": 4, "b": 2}]
336 -- and all concatentation should be in natural order supplied
337 SELECT '{"a": 4, "b": 2}'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
339 ----------------------------------------
340 [{"a": 4, "b": 2}, "a", "b", "c", "d"]
344 SELECT 'false'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
346 -----------------------------
347 [false, "a", "b", "c", "d"]
350 SELECT '["a","b"]'::jsonb || '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb;
352 --------------------------------------------------------
353 ["a", "b", {"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}]
356 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb || '["a","b"]'::jsonb;
358 --------------------------------------------------------
359 [{"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}, "a", "b"]
362 -------------------------------------------------------------------------------
363 -- Tests for jsonb =# jsonb
364 -------------------------------------------------------------------------------
365 -- any keys existing in left argument have values replaced with those from righ argument
366 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;
368 ------------------------------------------------------------------------------------
369 {"a": [1, 2, 3, 4], "b": {"f": 100, "j": {"k": 200}}, "c": 4, "d": {"test": true}}
372 -- note that as we are matching only keys and replacing values operation on an scalar/array elements effectively does nothing
373 SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '["a","b","c"]'::jsonb;
375 --------------------------------
376 {"a": [1, 2], "b": 2, "c": 12}
379 SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '[1,2,3]'::jsonb;
381 --------------------------------
382 {"a": [1, 2], "b": 2, "c": 12}
385 SELECT '[1,2,3]'::jsonb #= '[1,2,3,4]'::jsonb;
391 SELECT '"a"'::jsonb #= '{"a":1, "b":2}'::jsonb;
397 SELECT '{"a":1, "b":2}'::jsonb #= '"a"'::jsonb;
403 -------------------------------------------------------------------------------
404 -- Tests for jsonb #- text[]
405 -------------------------------------------------------------------------------
406 SELECT '"a"'::jsonb #- ARRAY['b'];
412 SELECT '["a"]'::jsonb #- ARRAY['b'];
418 SELECT '{"a":1}'::jsonb #- ARRAY['b'];
424 SELECT '"a"'::jsonb #- ARRAY['a'];
430 SELECT '["a"]'::jsonb #- ARRAY['a'];
436 SELECT '{"a":1}'::jsonb #- ARRAY['a'];
442 SELECT '["a", "b"]'::jsonb #- ARRAY['a'];
448 SELECT '{"a":1, "b":2}'::jsonb #- ARRAY['a'];
454 SELECT '{"a":{"b":1}, "c":2}'::jsonb #- ARRAY['a'];
460 SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['a'];
466 SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['b'];
468 ---------------------
472 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['c'];
474 -------------------------------------
475 {"a": {"b": [1, 2, 3, ["a", "b"]]}}
478 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a'];
484 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','c'];
486 ---------------------------------------------
487 {"a": {"b": [1, 2, 3, ["a", "b"]]}, "c": 2}
490 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b'];
496 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b','c'];
498 ---------------------------------------------
499 {"a": {"b": [1, 2, 3, ["a", "b"]]}, "c": 2}
502 SELECT '{"a":{"b":{"c":1}, "c":[1,2,3,["a","b"]]}, "d":3}'::jsonb #- ARRAY['a','b','c'];
504 ------------------------------------------------------
505 {"a": {"b": {}, "c": [1, 2, 3, ["a", "b"]]}, "d": 3}
508 SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b'];
510 ------------------------------------
511 {"a": {"c": [1, 2, 3, 4]}, "d": 2}
514 SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c'];
516 ---------------------------------------------
517 {"a": {"b": [1, 2, 3, ["a", "b"]]}, "d": 2}
520 SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a',null];
522 ----------------------------------------------------------------
523 {"a": {"b": [1, 2, 3, ["a", "b"]], "c": [1, 2, 3, 4]}, "d": 2}
526 SELECT '{"a":{"b":[1,2,3,["a",{"b":3}]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b'];
528 ------------------------------------
529 {"a": {"c": [1, 2, 3, 4]}, "d": 2}
532 SELECT '{"a":{"b":3, "d":[1,{"Z":[1,[2,3]]}]}}'::jsonb #- ARRAY['a','d'];
538 SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['a'];
540 ----------------------------------
541 [{"b": [1, 2, 3, 4, 5]}, 1, "c"]
544 SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['c'];
546 ----------------------------------
547 ["a", {"b": [1, 2, 3, 4, 5]}, 1]
550 SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['b'];
552 ------------------------------------
553 [1, [2, [3, [4, [5, 6, 7]]]], "a"]
556 SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['a'];
558 ------------------------------------
559 [1, [2, [3, [4, [5, 6, 7]]]], "b"]
562 -- expected limitation: cannot call with path deeper than 1 on a non-object
563 SELECT '["a", "b"]'::jsonb #- ARRAY['a','b'];
564 ERROR: cannot call with path deeper than 1 on a non-object
565 -------------------------------------------------------------------------------
566 -- Tests for jsonb_replace_path jsonb text[]
567 -------------------------------------------------------------------------------
568 SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"f":3}'::jsonb);
574 SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a'], '{"f":3}'::jsonb);
580 SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a','b'], '{"f":3}'::jsonb);
586 SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','b'], '{"f":3}'::jsonb);
588 -------------------------
589 {"a": {"c": 1, "f": 3}}
592 SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','c'], '{"f":3}'::jsonb);
594 -------------------------
595 {"a": {"b": 1, "f": 3}}
598 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','b'], '{"f":3}'::jsonb);
600 --------------------------------------
601 {"a": {"c": 2, "d": [1, 2], "f": 3}}
604 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','c'], '{"f":3}'::jsonb);
606 --------------------------------------
607 {"a": {"b": 1, "d": [1, 2], "f": 3}}
610 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','d'], '{"f":3}'::jsonb);
612 ---------------------------------
613 {"a": {"b": 1, "c": 2, "f": 3}}
616 SELECT jsonb_replace_path('{"a":{"d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb);
622 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','b'], '{"f":3}'::jsonb);
624 -------------------------------------------------------
625 {"a": {"c": 2, "d": [1, {"Z": [1, [2, 3]]}], "f": 3}}
628 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','c'], '{"f":3}'::jsonb);
630 -------------------------------------------------------
631 {"a": {"b": 1, "d": [1, {"Z": [1, [2, 3]]}], "f": 3}}
634 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb);
636 ---------------------------------
637 {"a": {"b": 1, "c": 2, "f": 3}}
640 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}}}');
642 ---------------------------------------------------------------------------------------------------
643 {"a": 1, "b": null, "e": {"20": [100, "c"]}, "f": [[1], 2], "g": "test", "h": {"i": {"j": null}}}
646 SELECT jsonb_replace_path('"a"', ARRAY['a'], '{"f":10}'::jsonb);
652 SELECT jsonb_replace_path('"a"', ARRAY['z'], '{"f":10}'::jsonb);
658 SELECT jsonb_replace_path('[null, "a"]', ARRAY[null], '"b"'::jsonb);
664 SELECT jsonb_replace_path('[1,2,3,"4"]', ARRAY['4'], '"5"'::jsonb);