1 \i /usr/local/pgsql/share/contrib/jsonb_opx.sql
2 -- CREATE OR REPLACE FUNCTION public.jsonb_delete (jsonb, text)
4 -- AS 'SELECT jsonb_delete($1, ARRAY[$2]);'
5 -- LANGUAGE SQL IMMUTABLE STRICT;
6 -- COMMENT ON FUNCTION public.jsonb_delete(jsonb, text) IS 'delete key in second argument from first argument';
7 CREATE OR REPLACE FUNCTION public.jsonb_delete (jsonb, text)
9 AS '$libdir/jsonb_opx', 'jsonb_delete_key'
10 LANGUAGE C IMMUTABLE STRICT;
11 COMMENT ON FUNCTION public.jsonb_delete(jsonb, text) IS 'delete key in second argument from first argument';
12 -- DROP OPERATOR - (jsonb, text);
13 CREATE OPERATOR - ( PROCEDURE = public.jsonb_delete, LEFTARG = jsonb, RIGHTARG = text);
14 COMMENT ON OPERATOR - (jsonb, text) IS 'delete key from left operand';
16 CREATE OR REPLACE FUNCTION public.jsonb_delete(jsonb, text[])
18 AS '$libdir/jsonb_opx', 'jsonb_delete_keys'
19 LANGUAGE C IMMUTABLE STRICT;
20 COMMENT ON FUNCTION public.jsonb_delete(jsonb, text[]) IS 'delete keys in second argument from first argument';
21 -- DROP OPERATOR - (jsonb, text[]);
22 CREATE OPERATOR - ( PROCEDURE = public.jsonb_delete, LEFTARG = jsonb, RIGHTARG = text[]);
23 COMMENT ON OPERATOR - (jsonb, text[]) IS 'delete keys from left operand';
25 CREATE OR REPLACE FUNCTION public.jsonb_delete(jsonb, jsonb)
27 AS '$libdir/jsonb_opx', 'jsonb_delete_jsonb'
28 LANGUAGE C IMMUTABLE STRICT;
29 COMMENT ON FUNCTION public.jsonb_delete(jsonb, jsonb) IS 'delete matching pairs in second argument from first argument';
30 -- DROP OPERATOR - (jsonb, jsonb);
31 CREATE OPERATOR - ( PROCEDURE = public.jsonb_delete, LEFTARG = jsonb, RIGHTARG = jsonb);
32 COMMENT ON OPERATOR - (jsonb, jsonb) IS 'delete matching pairs from left operand';
34 CREATE OR REPLACE FUNCTION public.jsonb_concat(jsonb, jsonb)
36 AS '$libdir/jsonb_opx', 'jsonb_concat_jsonb'
37 LANGUAGE C IMMUTABLE STRICT;
38 COMMENT ON FUNCTION public.jsonb_concat(jsonb, jsonb) IS 'concatenate first and second jsonb arguments';
39 -- DROP OPERATOR || (jsonb, jsonb);
40 CREATE OPERATOR || ( PROCEDURE = public.jsonb_concat, LEFTARG = jsonb, RIGHTARG = jsonb);
41 COMMENT ON OPERATOR || (jsonb, jsonb) IS 'concatenate jsonb types';
42 -------------------------------------------------------------------------------
43 -- Tests for jsonb - text
44 -------------------------------------------------------------------------------
45 -- text deletion from array containers will only delete string types as only strings can be keys:
46 SELECT '[1, "1", "2", 2]'::jsonb - '2'::text;
52 -- simple text deletion from an object container
53 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b'::text;
59 -- simple text deletion from an object container should only match keys
60 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '3'::text;
62 --------------------------
63 {"a": 1, "b": 2, "c": 3}
67 SELECT '["1", "2", true, false]'::jsonb - '2'::text;
73 SELECT '["1", "2", "2", "2"]'::jsonb - '2'::text;
79 -------------------------------------------------------------------------------
80 -- Tests for jsonb - text[]
81 -------------------------------------------------------------------------------
82 -- text[] deletion from array containers will only delete string types as only strings can be keys:
83 SELECT '[1, "1", "2", 2]'::jsonb - array['1','2'];
89 -- simple text[] deletion from an object container
90 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a',' b'];
96 -- simple text[] deletion from an object container should only match keys
97 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['1',' 2'];
99 --------------------------
100 {"a": 1, "b": 2, "c": 3}
103 -------------------------------------------------------------------------------
104 -- Tests for jsonb - jsonb
105 -------------------------------------------------------------------------------
106 -- jsonb deletion from an object should match on key/value
107 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
113 -- jsonb deletion from an array should only match on key
114 SELECT '["a", "b", "c"]'::jsonb - '{"a": 4, "b": 2}'::jsonb;
120 -- jsonb deletion from nested objectys should not be part matched
121 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"a": 4, "b": 2}'::jsonb
122 -- but a match of all nested values should narcg
123 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"d": {"a": 4}, "b": 2}'::jsonb
125 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": false}}'::jsonb - '{"d": {"a": false}, "b": 2}'::jsonb
126 -------------------------------------------------------------------------------
127 -- Tests for jsonb || jsonb
128 -------------------------------------------------------------------------------
129 -- duplicates should automatically be removed by lower level logic
130 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"a": 4, "b": 2, "d": 4}'::jsonb;
131 ERROR: syntax error at or near "SELECT"
132 LINE 3: SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{...
134 -- concatentation of arrays
135 SELECT '["a", "b"]'::jsonb || '["c"]'::jsonb;
141 -- concatentation of scalars and arrays should be wrapped into arrays
142 SELECT '["a", "b"]'::jsonb || '"c"'::jsonb;
148 -- likewise concatentation of objects and arrays should be wrapped into arrays
149 SELECT '["a", "b"]'::jsonb || '{"a": 4, "b": 2}'::jsonb;
151 ------------------------------
152 ["a", "b", {"a": 4, "b": 2}]
155 -- and all concatentation should be in natural order supplied
156 SELECT '{"a": 4, "b": 2}'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
158 ----------------------------------------
159 [{"a": 4, "b": 2}, "a", "b", "c", "d"]
163 SELECT 'false'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
165 -----------------------------
166 [false, "a", "b", "c", "d"]