1 CREATE EXTENSION jsonb_delete VERSION '1.0';
2 -- jsonb deletion from an object should match on key/value
3 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
9 -- jsonb deletion from an array should only match on element
10 SELECT '["a", "b", "c"]'::jsonb - '{"a": 4, "b": 2}'::jsonb;
16 -- jsonb deletion from nested objects should not be part matched
17 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
19 -------------------------
20 {"c": 3, "d": {"a": 4}}
23 -- but a match of all nested values should
24 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"d": {"a": 4}, "b": 2}'::jsonb;
30 SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - '{"d":2}'::jsonb;
32 --------------------------------------------------------------------
33 {"a": {"b": {"c": [1, [2, 3, [4]], {"d": 1}]}, "c": [1, 2, 3, 4]}}
36 -- jsonb nulls are equal
37 SELECT '{"a": 1, "b": 2, "c": null}'::jsonb - '{"a": 4, "c": null}'::jsonb;
44 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": false}}'::jsonb - '{"d": {"a": false}, "b": 2}'::jsonb;
50 SELECT '{"a": "test", "b": 2.2, "c": {"a": false}}'::jsonb - '{"a": "test2", "c": {"a": false}, "b": 2.2}'::jsonb;
56 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;
58 -------------------------------------------------------------------------
59 {"a": "test", "b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
62 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;
64 ------------------------------------------------------------
65 {"b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
68 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;
70 ---------------------------------------------------------------
71 {"a": "test", "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
74 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;
76 ------------------------------------------------------
77 {"a": "test", "b": 2.2, "d": true, "e": [1, 2, "a"]}
80 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;
82 --------------------------------------------------------------
83 {"a": "test", "b": 2.2, "c": {"a": false}, "e": [1, 2, "a"]}
86 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;
88 -------------------------------------------------------------------------
89 {"a": "test", "b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
92 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;
94 -------------------------------------------------------
95 {"a": "test", "b": 2.2, "c": {"a": false}, "d": true}
98 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;
100 --------------------------------------------------
101 {"c": {"a": false}, "d": true, "e": [1, 2, "a"]}
104 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;
106 -------------------------------
107 {"d": true, "e": [1, 2, "a"]}
110 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;
116 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;
122 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[[1]]'::jsonb;
124 ----------------------------
125 ["a", 2, {"a": 1, "b": 2}]
128 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb;
130 ----------------------------
131 ["a", 2, {"a": 1, "b": 2}]
134 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1, "b":2}]'::jsonb;
140 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '["a"]'::jsonb;
142 -----------------------
143 [2, {"a": 1, "b": 2}]
146 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[2]'::jsonb;
148 -------------------------
149 ["a", {"a": 1, "b": 2}]