1 CREATE EXTENSION jsonb_opx;
2 -------------------------------------------------------------------------------
3 -- Tests for jsonb - text
4 -------------------------------------------------------------------------------
5 SELECT '["a", "b"]'::jsonb - 'b'::text;
11 SELECT '["a"]'::jsonb - 'b'::text;
17 SELECT '"a"'::jsonb - 'b'::text;
23 SELECT '[1]'::jsonb - 'b'::text;
29 SELECT '1'::jsonb - 'b'::text;
35 SELECT '["a", {"a":1}, "b"]'::jsonb - 'b'::text;
41 -- text deletion from array containers will only delete string types currently
42 SELECT '[1, "1", "2", 2]'::jsonb - '2'::text;
48 -- simple text deletion from an object container
49 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b'::text;
55 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b '::text;
57 --------------------------
58 {"a": 1, "b": 2, "c": 3}
61 SELECT '{"a": 1, "b": 2, "c": {"b": 3}}'::jsonb - 'b'::text;
63 -------------------------
64 {"a": 1, "c": {"b": 3}}
67 SELECT '{"a": 1, "b": 2, "c": {"b": [1,2,3]}}'::jsonb - 'b'::text;
69 ---------------------------------
70 {"a": 1, "c": {"b": [1, 2, 3]}}
73 SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'b'::text;
75 --------------------------
76 {"a": 1, "c": [1, 2, 3]}
79 SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'c'::text;
85 -- simple text deletion from an object container should only match keys
86 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '3'::text;
88 --------------------------
89 {"a": 1, "b": 2, "c": 3}
92 -- deleting everything always results in an empty container
93 SELECT '["a"]'::jsonb - 'a'::text;
99 SELECT '{"a":1}'::jsonb - 'a'::text;
105 -- even for scalars, but this should perhaps error
106 SELECT '"a"'::jsonb - 'a'::text;
113 SELECT '["1", "2", true, false]'::jsonb - '2'::text;
119 SELECT '["1", "2", "2", "2"]'::jsonb - '2'::text;
125 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - 'a'::text;
127 -----------------------
128 [2, {"a": 1, "b": 2}]
131 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'a'::text;
137 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'd'::text;
139 ------------------------------------
140 {"a": {"b": 3, "c": [1, 2, 3, 4]}}
143 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'b'::text;
145 --------------------------------------------
146 {"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}
149 SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - 'a'::text;
155 -------------------------------------------------------------------------------
156 -- Tests for jsonb - text[]
157 -------------------------------------------------------------------------------
158 -- text deletion from array containers will only delete string types currently
159 SELECT '[1, "1", "2", 2]'::jsonb - array['1','2'];
165 -- simple text[] deletion from an object container
166 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b'];
172 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a ','b ',' c'];
174 --------------------------
175 {"a": 1, "b": 2, "c": 3}
178 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b','c'];
184 SELECT '{"a": 1, "b": 2, "c": {"b": 3}}'::jsonb - ARRAY['a','b'];
190 SELECT '{"a": 1, "b": 2, "c": {"b": [1,2,3]}}'::jsonb - ARRAY['a','b'];
192 -------------------------
193 {"c": {"b": [1, 2, 3]}}
196 SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','b'];
202 SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','c'];
208 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','d'];
210 ------------------------------------
211 {"a": {"b": 3, "c": [1, 2, 3, 4]}}
214 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','a'];
220 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['a','d'];
226 SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb - ARRAY['a','d'];
232 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'];
234 ----------------------------------------------------
235 {"d": [2, [2], [1, 2, {"a": 2}, {"b": [1, [2]]}]]}
238 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'];
240 -------------------------------------------------------------
241 {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}}
244 SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - '{a}'::text[];
250 -- simple text[] deletion from an object container should only match keys or nulls
251 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['1',' 2'];
253 --------------------------
254 {"a": 1, "b": 2, "c": 3}
257 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '{a}'::text[];
259 -----------------------
260 [2, {"a": 1, "b": 2}]
263 -- here we treat SQL nulls and json nulls as equal - bad?
264 SELECT '["1",null,2]'::jsonb - ARRAY[null];
270 SELECT '["1",2]'::jsonb - ARRAY[null];
276 -------------------------------------------------------------------------------
277 -- Tests for jsonb - jsonb
278 -------------------------------------------------------------------------------
279 -- jsonb deletion from an object should match on key/value
280 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
286 -- jsonb deletion from an array should only match on key
287 SELECT '["a", "b", "c"]'::jsonb - '{"a": 4, "b": 2}'::jsonb;
293 -- jsonb deletion from nested objects should not be part matched
294 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
296 -------------------------
297 {"c": 3, "d": {"a": 4}}
300 -- but a match of all nested values should
301 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"d": {"a": 4}, "b": 2}'::jsonb;
307 SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - '{"d":2}'::jsonb;
309 --------------------------------------------------------------------
310 {"a": {"b": {"c": [1, [2, 3, [4]], {"d": 1}]}, "c": [1, 2, 3, 4]}}
313 -- jsonb nulls are equal
314 SELECT '{"a": 1, "b": 2, "c": null}'::jsonb - '{"a": 4, "c": null}'::jsonb;
321 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": false}}'::jsonb - '{"d": {"a": false}, "b": 2}'::jsonb;
327 SELECT '{"a": "test", "b": 2.2, "c": {"a": false}}'::jsonb - '{"a": "test2", "c": {"a": false}, "b": 2.2}'::jsonb;
333 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;
335 -------------------------------------------------------------------------
336 {"a": "test", "b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
339 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;
341 ------------------------------------------------------------
342 {"b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
345 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;
347 ---------------------------------------------------------------
348 {"a": "test", "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
351 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;
353 ------------------------------------------------------
354 {"a": "test", "b": 2.2, "d": true, "e": [1, 2, "a"]}
357 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;
359 --------------------------------------------------------------
360 {"a": "test", "b": 2.2, "c": {"a": false}, "e": [1, 2, "a"]}
363 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;
365 -------------------------------------------------------------------------
366 {"a": "test", "b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
369 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;
371 -------------------------------------------------------
372 {"a": "test", "b": 2.2, "c": {"a": false}, "d": true}
375 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;
377 --------------------------------------------------
378 {"c": {"a": false}, "d": true, "e": [1, 2, "a"]}
381 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;
383 -------------------------------
384 {"d": true, "e": [1, 2, "a"]}
387 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;
393 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;
400 -- lookups of lhs values in rhs jsonb use findJsonbValueFromContainer which does
401 -- not allow looking up non-scalar elements resulting in "invalid jsonb scalar type"
402 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[[1]]'::jsonb;
403 ERROR: invalid jsonb scalar type
404 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb;
405 ERROR: invalid jsonb scalar type
406 -------------------------------------------------------------------------------
407 -- Tests for jsonb || jsonb
408 -------------------------------------------------------------------------------
409 -- duplicates should automatically be removed by lower level logic
410 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"a": 4, "b": 2, "d": 4}'::jsonb;
412 ----------------------------------
413 {"a": 4, "b": 2, "c": 3, "d": 4}
416 SELECT '{"a": 1, "b": null, "c": 3}'::jsonb || '{"a": 4, "b": null, "d": 4}'::jsonb;
418 -------------------------------------
419 {"a": 4, "b": null, "c": 3, "d": 4}
422 -- concatentation of arrays
423 SELECT '["a", "b"]'::jsonb || '["c"]'::jsonb;
429 -- concatentation of scalars and arrays should be wrapped into arrays
430 SELECT '["a", "b"]'::jsonb || '"c"'::jsonb;
436 -- likewise concatentation of objects and arrays should be wrapped into arrays
437 SELECT '["a", "b"]'::jsonb || '{"a": 4, "b": 2}'::jsonb;
439 ------------------------------
440 ["a", "b", {"a": 4, "b": 2}]
443 -- and all concatentation should be in natural order supplied
444 SELECT '{"a": 4, "b": 2}'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
446 ----------------------------------------
447 [{"a": 4, "b": 2}, "a", "b", "c", "d"]
451 SELECT 'false'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
453 -----------------------------
454 [false, "a", "b", "c", "d"]
457 SELECT '["a","b"]'::jsonb || '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb;
459 --------------------------------------------------------
460 ["a", "b", {"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}]
463 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb || '["a","b"]'::jsonb;
465 --------------------------------------------------------
466 [{"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}, "a", "b"]
469 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;
471 ----------------------------------------------------------------------------------------------------------------------------------------
472 [{"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}]
475 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;
477 ------------------------------------------------------------------------------------------------------------------------------------
478 ["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}]
481 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;
483 ---------------------------------------------------------------------
484 {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2}
487 -------------------------------------------------------------------------------
488 -- Tests for jsonb #= jsonb
489 -------------------------------------------------------------------------------
490 -- any keys existing in left argument have values replaced with those from righ
492 SELECT '{"a": 1}'::jsonb #= '{"a": [1,2,3,4]}'::jsonb;
494 ---------------------
498 SELECT '{"a": 1}'::jsonb #= '{"a": [1,2,3,4], "b":2}'::jsonb;
500 ---------------------
504 SELECT '{"a": 1, "b":1}'::jsonb #= '{"a": [1,2,3,4], "b":2}'::jsonb;
506 -----------------------------
507 {"a": [1, 2, 3, 4], "b": 2}
510 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;
512 ------------------------------------------------------------------------------------
513 {"a": [1, 2, 3, 4], "b": {"f": 100, "j": {"k": 200}}, "c": 4, "d": {"test": true}}
516 -- note that as we are matching only keys and replacing values operation on an
517 -- scalar/array elements effectively does nothing
518 SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '["a","b","c"]'::jsonb;
520 --------------------------------
521 {"a": [1, 2], "b": 2, "c": 12}
524 SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '[1,2,3]'::jsonb;
526 --------------------------------
527 {"a": [1, 2], "b": 2, "c": 12}
530 SELECT '[1,2,3]'::jsonb #= '[1,2,3,4]'::jsonb;
536 SELECT '"a"'::jsonb #= '{"a":1, "b":2}'::jsonb;
542 SELECT '{"a":1, "b":2}'::jsonb #= '"a"'::jsonb;
548 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;
550 ---------------------------------------------------------------------
551 {"a": {"b": 3, "c": [1, {"r": [true, {"u": 2}]}, 3, 4, 5]}, "d": 2}
554 -------------------------------------------------------------------------------
555 -- Tests for jsonb #- text[]
556 -------------------------------------------------------------------------------
557 SELECT '"a"'::jsonb #- ARRAY['b'];
563 SELECT '["a"]'::jsonb #- ARRAY['b'];
569 SELECT '{"a":1}'::jsonb #- ARRAY['b'];
575 SELECT '"a"'::jsonb #- ARRAY['a'];
581 SELECT '["a"]'::jsonb #- ARRAY['a'];
587 SELECT '{"a":1}'::jsonb #- ARRAY['a'];
593 SELECT '["a", "b"]'::jsonb #- ARRAY['a'];
599 SELECT '{"a":1, "b":2}'::jsonb #- ARRAY['a'];
605 SELECT '{"a":{"b":1}, "c":2}'::jsonb #- ARRAY['a'];
611 SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['a'];
617 SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['b'];
619 ---------------------
623 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['c'];
625 -------------------------------------
626 {"a": {"b": [1, 2, 3, ["a", "b"]]}}
629 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a'];
635 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','c'];
637 ---------------------------------------------
638 {"a": {"b": [1, 2, 3, ["a", "b"]]}, "c": 2}
641 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b'];
647 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b','c'];
649 ---------------------------------------------
650 {"a": {"b": [1, 2, 3, ["a", "b"]]}, "c": 2}
653 SELECT '{"a":{"b":{"c":1}, "c":[1,2,3,["a","b"]]}, "d":3}'::jsonb #- ARRAY['a','b','c'];
655 ------------------------------------------------------
656 {"a": {"b": {}, "c": [1, 2, 3, ["a", "b"]]}, "d": 3}
659 SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b'];
661 ------------------------------------
662 {"a": {"c": [1, 2, 3, 4]}, "d": 2}
665 SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c'];
667 ---------------------------------------------
668 {"a": {"b": [1, 2, 3, ["a", "b"]]}, "d": 2}
671 SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a',null];
673 ----------------------------------------------------------------
674 {"a": {"b": [1, 2, 3, ["a", "b"]], "c": [1, 2, 3, 4]}, "d": 2}
677 SELECT '{"a":{"b":[1,2,3,["a",{"b":3}]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b'];
679 ------------------------------------
680 {"a": {"c": [1, 2, 3, 4]}, "d": 2}
683 SELECT '{"a":{"b":3, "d":[1,{"Z":[1,[2,3]]}]}}'::jsonb #- ARRAY['a','d'];
689 SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['a'];
691 ----------------------------------
692 [{"b": [1, 2, 3, 4, 5]}, 1, "c"]
695 SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['c'];
697 ----------------------------------
698 ["a", {"b": [1, 2, 3, 4, 5]}, 1]
701 SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['b'];
703 ------------------------------------
704 [1, [2, [3, [4, [5, 6, 7]]]], "a"]
707 SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['a'];
709 ------------------------------------
710 [1, [2, [3, [4, [5, 6, 7]]]], "b"]
713 SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b'];
715 -------------------------------------------------------------
716 {"a": {"c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2}
719 SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c'];
721 -------------------------
722 {"a": {"b": 3}, "d": 2}
725 SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','d'];
727 ---------------------------------------------------------------------
728 {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2}
731 SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['d'];
733 -------------------------------------------------------------
734 {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}}
737 SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c','r'];
739 ---------------------------------------------------------------------
740 {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2}
743 SELECT '{"a":{"b":3, "c":{"r":{"u":1}}}, "d":2}'::jsonb #- ARRAY['a','c','r'];
745 ----------------------------------
746 {"a": {"b": 3, "c": {}}, "d": 2}
749 SELECT '{"a":{"b":3, "c":{"r":{"u":1}}}, "d":2}'::jsonb #- ARRAY['a','c','r','u'];
751 -----------------------------------------
752 {"a": {"b": 3, "c": {"r": {}}}, "d": 2}
755 -- expected limitation: cannot call with path deeper than 1 on a non-object
756 SELECT '["a", "b"]'::jsonb #- ARRAY['a','b'];
757 ERROR: cannot call with path deeper than 1 on a non-object
758 -------------------------------------------------------------------------------
759 -- Tests for jsonb_replace_path jsonb text[]
760 -------------------------------------------------------------------------------
761 -- if the replacement on an object/array is passed as a scalar/array the value/element
763 SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '3'::jsonb);
769 SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '[3]'::jsonb);
775 SELECT jsonb_replace_path('["a", "b"]', ARRAY['a'], '3'::jsonb);
781 SELECT jsonb_replace_path('["a", "b"]', ARRAY['a'], '[3]'::jsonb);
787 -- if the replacement on an object/array is passed as an object the whole key-value
788 -- pair is replaced. This difference is perhaps confusing, but otherwise there is
789 -- no way to directly replace a key without deletion and concatenation.
790 SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"z":3}'::jsonb);
796 SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"a":{"z":3}}'::jsonb);
798 -------------------------
799 {"a": {"z": 3}, "b": 2}
802 SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"f":3}'::jsonb);
808 SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a'], '{"f":3}'::jsonb);
814 SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a','b'], '{"f":3}'::jsonb);
820 SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','b'], '{"f":3}'::jsonb);
822 -------------------------
823 {"a": {"c": 1, "f": 3}}
826 SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','c'], '{"f":3}'::jsonb);
828 -------------------------
829 {"a": {"b": 1, "f": 3}}
832 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','b'], '{"f":3}'::jsonb);
834 --------------------------------------
835 {"a": {"c": 2, "d": [1, 2], "f": 3}}
838 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','c'], '{"f":3}'::jsonb);
840 --------------------------------------
841 {"a": {"b": 1, "d": [1, 2], "f": 3}}
844 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','d'], '{"f":3}'::jsonb);
846 ---------------------------------
847 {"a": {"b": 1, "c": 2, "f": 3}}
850 SELECT jsonb_replace_path('{"a":{"d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb);
856 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','b'], '{"f":3}'::jsonb);
858 -------------------------------------------------------
859 {"a": {"c": 2, "d": [1, {"Z": [1, [2, 3]]}], "f": 3}}
862 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','c'], '{"f":3}'::jsonb);
864 -------------------------------------------------------
865 {"a": {"b": 1, "d": [1, {"Z": [1, [2, 3]]}], "f": 3}}
868 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb);
870 ---------------------------------
871 {"a": {"b": 1, "c": 2, "f": 3}}
874 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}}}');
876 ---------------------------------------------------------------------------------------------------
877 {"a": 1, "b": null, "e": {"20": [100, "c"]}, "f": [[1], 2], "g": "test", "h": {"i": {"j": null}}}
880 SELECT jsonb_replace_path('"a"', ARRAY['a'], '{"f":10}'::jsonb);
886 SELECT jsonb_replace_path('"a"', ARRAY['z'], '{"f":10}'::jsonb);
892 SELECT jsonb_replace_path('[null, "a"]', ARRAY[null], '"b"'::jsonb);
898 SELECT jsonb_replace_path('[1,2,3,"4"]', ARRAY['4'], '"5"'::jsonb);
904 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);
906 ----------------------------------------------------------------------------
907 {"a": {"b": 3, "c": {"r": {"a": {"b": 3, "c": {"r": {"u": 1}}}}}}, "d": 2}
910 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);
912 --------------------------------------------------------------------------------------------------
913 {"a": {"a": {"b": 3, "c": {"r": {"u": 1}}}, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2}