1 CREATE EXTENSION jsonb_opx VERSION '1.1';
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 elements
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 -- function is strict, so - null returns null - assume SQL nulls and jsonb nulls are not equal anyway
156 SELECT '["1", "2", true, null]'::jsonb - null::text;
162 -------------------------------------------------------------------------------
163 -- Tests for jsonb - numeric
164 -------------------------------------------------------------------------------
165 -- Only matches numeric array element types
166 SELECT '[1, "1", "2", 2]'::jsonb - 2;
172 SELECT '[2]'::jsonb - 2;
178 SELECT '2'::jsonb - 2;
184 -- Does nothing for objects
185 SELECT '{"2":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 2;
187 --------------------------------------------
188 {"2": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}
191 -------------------------------------------------------------------------------
192 -- Tests for jsonb - boolean
193 -------------------------------------------------------------------------------
194 -- Only matches boolean array element types
195 SELECT '[1, "1", false, true, null]'::jsonb - false;
197 ----------------------
201 SELECT '[1, "1", false, true, null]'::jsonb - true;
203 -----------------------
204 [1, "1", false, null]
207 -------------------------------------------------------------------------------
208 -- Tests for jsonb - text[]
209 -------------------------------------------------------------------------------
210 -- text deletion from array containers will only delete string types currently
211 SELECT '[1, "1", "2", 2]'::jsonb - array['1','2'];
217 -- simple text[] deletion from an object container
218 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b'];
224 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a ','b ',' c'];
226 --------------------------
227 {"a": 1, "b": 2, "c": 3}
230 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b','c'];
236 SELECT '{"a": 1, "b": 2, "c": {"b": 3}}'::jsonb - ARRAY['a','b'];
242 SELECT '{"a": 1, "b": 2, "c": {"b": [1,2,3]}}'::jsonb - ARRAY['a','b'];
244 -------------------------
245 {"c": {"b": [1, 2, 3]}}
248 SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','b'];
254 SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','c'];
260 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','d'];
262 ------------------------------------
263 {"a": {"b": 3, "c": [1, 2, 3, 4]}}
266 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','a'];
272 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['a','d'];
278 SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb - ARRAY['a','d'];
284 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'];
286 ----------------------------------------------------
287 {"d": [2, [2], [1, 2, {"a": 2}, {"b": [1, [2]]}]]}
290 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'];
292 -------------------------------------------------------------
293 {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}}
296 SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - '{a}'::text[];
302 -- simple text[] deletion from an object container should only match keys
303 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['1',' 2'];
305 --------------------------
306 {"a": 1, "b": 2, "c": 3}
309 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '{a}'::text[];
311 -----------------------
312 [2, {"a": 1, "b": 2}]
315 -- SQL nulls and jsonb nulls are not equal
316 SELECT '["1",null,2]'::jsonb - ARRAY[null];
322 SELECT '["1",2]'::jsonb - ARRAY[null];
328 -------------------------------------------------------------------------------
329 -- Tests for jsonb - numeric[]
330 -------------------------------------------------------------------------------
331 -- Only matches numeric array element types
332 SELECT '[1, "1", "2", 2]'::jsonb - ARRAY[2];
338 SELECT '[1, "1", "2", 2]'::jsonb - ARRAY[1,2];
344 SELECT '[2]'::jsonb - ARRAY[1,2];
350 SELECT '2'::jsonb - ARRAY[1,2];
356 -- Does nothing for objects
357 SELECT '{"2":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY[1,2];
359 --------------------------------------------
360 {"2": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}
363 -------------------------------------------------------------------------------
364 -- Tests for jsonb - boolean[]
365 -------------------------------------------------------------------------------
366 -- Only matches boolean array element types
367 SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[false];
369 ----------------------
373 SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[true];
375 -----------------------
376 [1, "1", false, null]
379 SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[true, false];
385 -- Again nulls are not equal
386 SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[true, false, null];
392 -------------------------------------------------------------------------------
393 -- Tests for jsonb - jsonb
394 -------------------------------------------------------------------------------
395 -- jsonb deletion from an object should match on key/value
396 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
402 -- jsonb deletion from an array should only match on key
403 SELECT '["a", "b", "c"]'::jsonb - '{"a": 4, "b": 2}'::jsonb;
409 -- jsonb deletion from nested objects should not be part matched
410 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
412 -------------------------
413 {"c": 3, "d": {"a": 4}}
416 -- but a match of all nested values should
417 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"d": {"a": 4}, "b": 2}'::jsonb;
423 SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - '{"d":2}'::jsonb;
425 --------------------------------------------------------------------
426 {"a": {"b": {"c": [1, [2, 3, [4]], {"d": 1}]}, "c": [1, 2, 3, 4]}}
429 -- jsonb nulls are equal
430 SELECT '{"a": 1, "b": 2, "c": null}'::jsonb - '{"a": 4, "c": null}'::jsonb;
437 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": false}}'::jsonb - '{"d": {"a": false}, "b": 2}'::jsonb;
443 SELECT '{"a": "test", "b": 2.2, "c": {"a": false}}'::jsonb - '{"a": "test2", "c": {"a": false}, "b": 2.2}'::jsonb;
449 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;
451 -------------------------------------------------------------------------
452 {"a": "test", "b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
455 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;
457 ------------------------------------------------------------
458 {"b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
461 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;
463 ---------------------------------------------------------------
464 {"a": "test", "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
467 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;
469 ------------------------------------------------------
470 {"a": "test", "b": 2.2, "d": true, "e": [1, 2, "a"]}
473 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;
475 --------------------------------------------------------------
476 {"a": "test", "b": 2.2, "c": {"a": false}, "e": [1, 2, "a"]}
479 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;
481 -------------------------------------------------------------------------
482 {"a": "test", "b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
485 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;
487 -------------------------------------------------------
488 {"a": "test", "b": 2.2, "c": {"a": false}, "d": true}
491 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;
493 --------------------------------------------------
494 {"c": {"a": false}, "d": true, "e": [1, 2, "a"]}
497 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;
499 -------------------------------
500 {"d": true, "e": [1, 2, "a"]}
503 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;
509 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;
515 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[[1]]'::jsonb;
517 ----------------------------
518 ["a", 2, {"a": 1, "b": 2}]
521 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb;
523 ----------------------------
524 ["a", 2, {"a": 1, "b": 2}]
527 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1, "b":2}]'::jsonb;
533 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '["a"]'::jsonb;
535 -----------------------
536 [2, {"a": 1, "b": 2}]
539 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[2]'::jsonb;
541 -------------------------
542 ["a", {"a": 1, "b": 2}]
545 -------------------------------------------------------------------------------
546 -- Tests for jsonb || jsonb
547 -------------------------------------------------------------------------------
548 -- duplicates should automatically be removed by lower level logic
549 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"a": 4, "b": 2, "d": 4}'::jsonb;
551 ----------------------------------
552 {"a": 4, "b": 2, "c": 3, "d": 4}
555 SELECT '{"a": 1, "b": null, "c": 3}'::jsonb || '{"a": 4, "b": null, "d": 4}'::jsonb;
557 -------------------------------------
558 {"a": 4, "b": null, "c": 3, "d": 4}
561 -- concatentation of arrays
562 SELECT '["a", "b"]'::jsonb || '["c"]'::jsonb;
568 -- concatentation of scalars and arrays should be wrapped into arrays
569 SELECT '["a", "b"]'::jsonb || '"c"'::jsonb;
575 -- likewise concatentation of objects and arrays should be wrapped into arrays
576 SELECT '["a", "b"]'::jsonb || '{"a": 4, "b": 2}'::jsonb;
578 ------------------------------
579 ["a", "b", {"a": 4, "b": 2}]
582 -- and all concatentation should be in natural order supplied
583 SELECT '{"a": 4, "b": 2}'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
585 ----------------------------------------
586 [{"a": 4, "b": 2}, "a", "b", "c", "d"]
590 SELECT 'false'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
592 -----------------------------
593 [false, "a", "b", "c", "d"]
596 SELECT '["a","b"]'::jsonb || '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb;
598 --------------------------------------------------------
599 ["a", "b", {"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}]
602 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb || '["a","b"]'::jsonb;
604 --------------------------------------------------------
605 [{"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}, "a", "b"]
608 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;
610 ----------------------------------------------------------------------------------------------------------------------------------------
611 [{"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}]
614 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;
616 ------------------------------------------------------------------------------------------------------------------------------------
617 ["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}]
620 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;
622 ---------------------------------------------------------------------
623 {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2}
626 -------------------------------------------------------------------------------
627 -- Tests for jsonb #= jsonb
628 -------------------------------------------------------------------------------
629 -- any keys existing in left argument have values replaced with those from righ
631 SELECT '{"a": 1}'::jsonb #= '{"a": [1,2,3,4]}'::jsonb;
633 ---------------------
637 SELECT '{"a": 1}'::jsonb #= '{"a": [1,2,3,4], "b":2}'::jsonb;
639 ---------------------
643 SELECT '{"a": 1, "b":1}'::jsonb #= '{"a": [1,2,3,4], "b":2}'::jsonb;
645 -----------------------------
646 {"a": [1, 2, 3, 4], "b": 2}
649 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;
651 ------------------------------------------------------------------------------------
652 {"a": [1, 2, 3, 4], "b": {"f": 100, "j": {"k": 200}}, "c": 4, "d": {"test": true}}
655 -- note that as we are matching only keys and replacing values operation on an
656 -- scalar/array elements effectively does nothing
657 SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '["a","b","c"]'::jsonb;
659 --------------------------------
660 {"a": [1, 2], "b": 2, "c": 12}
663 SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '[1,2,3]'::jsonb;
665 --------------------------------
666 {"a": [1, 2], "b": 2, "c": 12}
669 SELECT '[1,2,3]'::jsonb #= '[1,2,3,4]'::jsonb;
675 SELECT '"a"'::jsonb #= '{"a":1, "b":2}'::jsonb;
681 SELECT '{"a":1, "b":2}'::jsonb #= '"a"'::jsonb;
687 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;
689 ---------------------------------------------------------------------
690 {"a": {"b": 3, "c": [1, {"r": [true, {"u": 2}]}, 3, 4, 5]}, "d": 2}
693 SELECT '["a","b","c"]'::jsonb #= '{"a":1}'::jsonb;
699 -------------------------------------------------------------------------------
700 -- Tests for jsonb #- text[]
701 -------------------------------------------------------------------------------
702 SELECT '"a"'::jsonb #- ARRAY['b'];
708 SELECT '["a"]'::jsonb #- ARRAY['b'];
714 SELECT '{"a":1}'::jsonb #- ARRAY['b'];
720 SELECT '"a"'::jsonb #- ARRAY['a'];
726 SELECT '["a"]'::jsonb #- ARRAY['a'];
732 SELECT '{"a":1}'::jsonb #- ARRAY['a'];
738 SELECT '["a", "b"]'::jsonb #- ARRAY['a'];
744 SELECT '{"a":1, "b":2}'::jsonb #- ARRAY['a'];
750 SELECT '{"a":{"b":1}, "c":2}'::jsonb #- ARRAY['a'];
756 SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['a'];
762 SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['b'];
764 ---------------------
768 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['c'];
770 -------------------------------------
771 {"a": {"b": [1, 2, 3, ["a", "b"]]}}
774 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a'];
780 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','c'];
782 ---------------------------------------------
783 {"a": {"b": [1, 2, 3, ["a", "b"]]}, "c": 2}
786 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b'];
792 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b','c'];
794 ---------------------------------------------
795 {"a": {"b": [1, 2, 3, ["a", "b"]]}, "c": 2}
798 SELECT '{"a":{"b":{"c":1}, "c":[1,2,3,["a","b"]]}, "d":3}'::jsonb #- ARRAY['a','b','c'];
800 ------------------------------------------------------
801 {"a": {"b": {}, "c": [1, 2, 3, ["a", "b"]]}, "d": 3}
804 SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b'];
806 ------------------------------------
807 {"a": {"c": [1, 2, 3, 4]}, "d": 2}
810 SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c'];
812 ---------------------------------------------
813 {"a": {"b": [1, 2, 3, ["a", "b"]]}, "d": 2}
816 SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a',null];
818 ----------------------------------------------------------------
819 {"a": {"b": [1, 2, 3, ["a", "b"]], "c": [1, 2, 3, 4]}, "d": 2}
822 SELECT '{"a":{"b":[1,2,3,["a",{"b":3}]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b'];
824 ------------------------------------
825 {"a": {"c": [1, 2, 3, 4]}, "d": 2}
828 SELECT '{"a":{"b":3, "d":[1,{"Z":[1,[2,3]]}]}}'::jsonb #- ARRAY['a','d'];
834 SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['a'];
836 ----------------------------------
837 [{"b": [1, 2, 3, 4, 5]}, 1, "c"]
840 SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['c'];
842 ----------------------------------
843 ["a", {"b": [1, 2, 3, 4, 5]}, 1]
846 SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['b'];
848 ------------------------------------
849 [1, [2, [3, [4, [5, 6, 7]]]], "a"]
852 SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['a'];
854 ------------------------------------
855 [1, [2, [3, [4, [5, 6, 7]]]], "b"]
858 SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b'];
860 -------------------------------------------------------------
861 {"a": {"c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2}
864 SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c'];
866 -------------------------
867 {"a": {"b": 3}, "d": 2}
870 SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','d'];
872 ---------------------------------------------------------------------
873 {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2}
876 SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['d'];
878 -------------------------------------------------------------
879 {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}}
882 SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c','r'];
884 ---------------------------------------------------------------------
885 {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2}
888 SELECT '{"a":{"b":3, "c":{"r":{"u":1}}}, "d":2}'::jsonb #- ARRAY['a','c','r'];
890 ----------------------------------
891 {"a": {"b": 3, "c": {}}, "d": 2}
894 SELECT '{"a":{"b":3, "c":{"r":{"u":1}}}, "d":2}'::jsonb #- ARRAY['a','c','r','u'];
896 -----------------------------------------
897 {"a": {"b": 3, "c": {"r": {}}}, "d": 2}
900 -- expected limitation: cannot call with path deeper than 1 on a non-object
901 SELECT '["a", "b"]'::jsonb #- ARRAY['a','b'];
902 ERROR: cannot call with path deeper than 1 on a non-object
903 -------------------------------------------------------------------------------
904 -- Tests for jsonb_replace_path jsonb text[]
905 -------------------------------------------------------------------------------
906 -- if the replacement on an object/array is passed as a scalar/array the value/element
908 SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '3'::jsonb);
914 SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '[3]'::jsonb);
920 SELECT jsonb_replace_path('["a", "b"]', ARRAY['a'], '3'::jsonb);
926 SELECT jsonb_replace_path('["a", "b"]', ARRAY['a'], '[3]'::jsonb);
932 -- if the replacement on an object/array is passed as an object the whole key-value
933 -- pair is replaced. This difference is perhaps confusing, but otherwise there is
934 -- no way to directly replace a key without deletion and concatenation.
935 SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"z":3}'::jsonb);
941 SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"a":{"z":3}}'::jsonb);
943 -------------------------
944 {"a": {"z": 3}, "b": 2}
947 SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"f":3}'::jsonb);
953 SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a'], '{"f":3}'::jsonb);
959 SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a','b'], '{"f":3}'::jsonb);
965 SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','b'], '{"f":3}'::jsonb);
967 -------------------------
968 {"a": {"c": 1, "f": 3}}
971 SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','c'], '{"f":3}'::jsonb);
973 -------------------------
974 {"a": {"b": 1, "f": 3}}
977 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','b'], '{"f":3}'::jsonb);
979 --------------------------------------
980 {"a": {"c": 2, "d": [1, 2], "f": 3}}
983 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','c'], '{"f":3}'::jsonb);
985 --------------------------------------
986 {"a": {"b": 1, "d": [1, 2], "f": 3}}
989 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','d'], '{"f":3}'::jsonb);
991 ---------------------------------
992 {"a": {"b": 1, "c": 2, "f": 3}}
995 SELECT jsonb_replace_path('{"a":{"d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb);
1001 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','b'], '{"f":3}'::jsonb);
1003 -------------------------------------------------------
1004 {"a": {"c": 2, "d": [1, {"Z": [1, [2, 3]]}], "f": 3}}
1007 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','c'], '{"f":3}'::jsonb);
1009 -------------------------------------------------------
1010 {"a": {"b": 1, "d": [1, {"Z": [1, [2, 3]]}], "f": 3}}
1013 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb);
1015 ---------------------------------
1016 {"a": {"b": 1, "c": 2, "f": 3}}
1019 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}}}');
1021 ---------------------------------------------------------------------------------------------------
1022 {"a": 1, "b": null, "e": {"20": [100, "c"]}, "f": [[1], 2], "g": "test", "h": {"i": {"j": null}}}
1025 SELECT jsonb_replace_path('"a"', ARRAY['a'], '{"f":10}'::jsonb);
1027 --------------------
1031 SELECT jsonb_replace_path('"a"', ARRAY['z'], '{"f":10}'::jsonb);
1033 --------------------
1037 SELECT jsonb_replace_path('[null, "a"]', ARRAY[null], '"b"'::jsonb);
1039 --------------------
1043 SELECT jsonb_replace_path('[1,2,3,"4"]', ARRAY['4'], '"5"'::jsonb);
1045 --------------------
1049 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);
1051 ----------------------------------------------------------------------------
1052 {"a": {"b": 3, "c": {"r": {"a": {"b": 3, "c": {"r": {"u": 1}}}}}}, "d": 2}
1055 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);
1057 --------------------------------------------------------------------------------------------------
1058 {"a": {"a": {"b": 3, "c": {"r": {"u": 1}}}, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2}