2 SELECT '""'::jsonb; -- OK.
8 SELECT $$''$$::jsonb; -- ERROR, single quotes are not allowed
9 ERROR: invalid input syntax for type json
10 LINE 1: SELECT $$''$$::jsonb;
12 DETAIL: Token "'" is invalid.
13 CONTEXT: JSON data, line 1: '...
14 SELECT '"abc"'::jsonb; -- OK
20 SELECT '"abc'::jsonb; -- ERROR, quotes not closed
21 ERROR: invalid input syntax for type json
22 LINE 1: SELECT '"abc'::jsonb;
24 DETAIL: Token ""abc" is invalid.
25 CONTEXT: JSON data, line 1: "abc
27 def"'::jsonb; -- ERROR, unescaped newline in string constant
28 ERROR: invalid input syntax for type json
31 DETAIL: Character with value 0x0a must be escaped.
32 CONTEXT: JSON data, line 1: "abc
33 SELECT '"\n\"\\"'::jsonb; -- OK, legal escapes
39 SELECT '"\v"'::jsonb; -- ERROR, not a valid JSON escape
40 ERROR: invalid input syntax for type json
41 LINE 1: SELECT '"\v"'::jsonb;
43 DETAIL: Escape sequence "\v" is invalid.
44 CONTEXT: JSON data, line 1: "\v...
45 SELECT '"\u"'::jsonb; -- ERROR, incomplete escape
46 ERROR: invalid input syntax for type json
47 LINE 1: SELECT '"\u"'::jsonb;
49 DETAIL: "\u" must be followed by four hexadecimal digits.
50 CONTEXT: JSON data, line 1: "\u"
51 SELECT '"\u00"'::jsonb; -- ERROR, incomplete escape
52 ERROR: invalid input syntax for type json
53 LINE 1: SELECT '"\u00"'::jsonb;
55 DETAIL: "\u" must be followed by four hexadecimal digits.
56 CONTEXT: JSON data, line 1: "\u00"
57 SELECT '"\u000g"'::jsonb; -- ERROR, g is not a hex digit
58 ERROR: invalid input syntax for type json
59 LINE 1: SELECT '"\u000g"'::jsonb;
61 DETAIL: "\u" must be followed by four hexadecimal digits.
62 CONTEXT: JSON data, line 1: "\u000g...
63 SELECT '"\u0045"'::jsonb; -- OK, legal escape
69 SELECT '"\u0000"'::jsonb; -- ERROR, we don't support U+0000
70 ERROR: unsupported Unicode escape sequence
71 LINE 1: SELECT '"\u0000"'::jsonb;
73 DETAIL: \u0000 cannot be converted to text.
74 CONTEXT: JSON data, line 1: ...
75 -- use octet_length here so we don't get an odd unicode char in the
77 SELECT octet_length('"\uaBcD"'::jsonb::text); -- OK, uppercase and lower case both OK
78 ERROR: unsupported Unicode escape sequence
79 LINE 1: SELECT octet_length('"\uaBcD"'::jsonb::text);
81 DETAIL: Unicode escape values cannot be used for code point values above 007F when the server encoding is not UTF8.
82 CONTEXT: JSON data, line 1: ...
84 SELECT '1'::jsonb; -- OK
90 SELECT '0'::jsonb; -- OK
96 SELECT '01'::jsonb; -- ERROR, not valid according to JSON spec
97 ERROR: invalid input syntax for type json
98 LINE 1: SELECT '01'::jsonb;
100 DETAIL: Token "01" is invalid.
101 CONTEXT: JSON data, line 1: 01
102 SELECT '0.1'::jsonb; -- OK
108 SELECT '9223372036854775808'::jsonb; -- OK, even though it's too large for int8
110 ---------------------
114 SELECT '1e100'::jsonb; -- OK
116 -------------------------------------------------------------------------------------------------------
117 10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
120 SELECT '1.3e100'::jsonb; -- OK
122 -------------------------------------------------------------------------------------------------------
123 13000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
126 SELECT '1f2'::jsonb; -- ERROR
127 ERROR: invalid input syntax for type json
128 LINE 1: SELECT '1f2'::jsonb;
130 DETAIL: Token "1f2" is invalid.
131 CONTEXT: JSON data, line 1: 1f2
132 SELECT '0.x1'::jsonb; -- ERROR
133 ERROR: invalid input syntax for type json
134 LINE 1: SELECT '0.x1'::jsonb;
136 DETAIL: Token "0.x1" is invalid.
137 CONTEXT: JSON data, line 1: 0.x1
138 SELECT '1.3ex100'::jsonb; -- ERROR
139 ERROR: invalid input syntax for type json
140 LINE 1: SELECT '1.3ex100'::jsonb;
142 DETAIL: Token "1.3ex100" is invalid.
143 CONTEXT: JSON data, line 1: 1.3ex100
145 SELECT '[]'::jsonb; -- OK
151 SELECT '[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]'::jsonb; -- OK
153 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
154 [[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
157 SELECT '[1,2]'::jsonb; -- OK
163 SELECT '[1,2,]'::jsonb; -- ERROR, trailing comma
164 ERROR: invalid input syntax for type json
165 LINE 1: SELECT '[1,2,]'::jsonb;
167 DETAIL: Expected JSON value, but found "]".
168 CONTEXT: JSON data, line 1: [1,2,]
169 SELECT '[1,2'::jsonb; -- ERROR, no closing bracket
170 ERROR: invalid input syntax for type json
171 LINE 1: SELECT '[1,2'::jsonb;
173 DETAIL: The input string ended unexpectedly.
174 CONTEXT: JSON data, line 1: [1,2
175 SELECT '[1,[2]'::jsonb; -- ERROR, no closing bracket
176 ERROR: invalid input syntax for type json
177 LINE 1: SELECT '[1,[2]'::jsonb;
179 DETAIL: The input string ended unexpectedly.
180 CONTEXT: JSON data, line 1: [1,[2]
182 SELECT '{}'::jsonb; -- OK
188 SELECT '{"abc"}'::jsonb; -- ERROR, no value
189 ERROR: invalid input syntax for type json
190 LINE 1: SELECT '{"abc"}'::jsonb;
192 DETAIL: Expected ":", but found "}".
193 CONTEXT: JSON data, line 1: {"abc"}
194 SELECT '{"abc":1}'::jsonb; -- OK
200 SELECT '{1:"abc"}'::jsonb; -- ERROR, keys must be strings
201 ERROR: invalid input syntax for type json
202 LINE 1: SELECT '{1:"abc"}'::jsonb;
204 DETAIL: Expected string or "}", but found "1".
205 CONTEXT: JSON data, line 1: {1...
206 SELECT '{"abc",1}'::jsonb; -- ERROR, wrong separator
207 ERROR: invalid input syntax for type json
208 LINE 1: SELECT '{"abc",1}'::jsonb;
210 DETAIL: Expected ":", but found ",".
211 CONTEXT: JSON data, line 1: {"abc",...
212 SELECT '{"abc"=1}'::jsonb; -- ERROR, totally wrong separator
213 ERROR: invalid input syntax for type json
214 LINE 1: SELECT '{"abc"=1}'::jsonb;
216 DETAIL: Token "=" is invalid.
217 CONTEXT: JSON data, line 1: {"abc"=...
218 SELECT '{"abc"::1}'::jsonb; -- ERROR, another wrong separator
219 ERROR: invalid input syntax for type json
220 LINE 1: SELECT '{"abc"::1}'::jsonb;
222 DETAIL: Expected JSON value, but found ":".
223 CONTEXT: JSON data, line 1: {"abc"::...
224 SELECT '{"abc":1,"def":2,"ghi":[3,4],"hij":{"klm":5,"nop":[6]}}'::jsonb; -- OK
226 --------------------------------------------------------------------
227 {"abc": 1, "def": 2, "ghi": [3, 4], "hij": {"klm": 5, "nop": [6]}}
230 SELECT '{"abc":1:2}'::jsonb; -- ERROR, colon in wrong spot
231 ERROR: invalid input syntax for type json
232 LINE 1: SELECT '{"abc":1:2}'::jsonb;
234 DETAIL: Expected "," or "}", but found ":".
235 CONTEXT: JSON data, line 1: {"abc":1:...
236 SELECT '{"abc":1,3}'::jsonb; -- ERROR, no value
237 ERROR: invalid input syntax for type json
238 LINE 1: SELECT '{"abc":1,3}'::jsonb;
240 DETAIL: Expected string, but found "3".
241 CONTEXT: JSON data, line 1: {"abc":1,3...
243 SET max_stack_depth = '100kB';
244 SELECT repeat('[', 10000)::jsonb;
245 ERROR: stack depth limit exceeded
246 HINT: Increase the configuration parameter "max_stack_depth" (currently 100kB), after ensuring the platform's stack depth limit is adequate.
247 SELECT repeat('{"a":', 10000)::jsonb;
248 ERROR: stack depth limit exceeded
249 HINT: Increase the configuration parameter "max_stack_depth" (currently 100kB), after ensuring the platform's stack depth limit is adequate.
250 RESET max_stack_depth;
251 -- Miscellaneous stuff.
252 SELECT 'true'::jsonb; -- OK
258 SELECT 'false'::jsonb; -- OK
264 SELECT 'null'::jsonb; -- OK
270 SELECT ' true '::jsonb; -- OK, even with extra whitespace
276 SELECT 'true false'::jsonb; -- ERROR, too many values
277 ERROR: invalid input syntax for type json
278 LINE 1: SELECT 'true false'::jsonb;
280 DETAIL: Expected end of input, but found "false".
281 CONTEXT: JSON data, line 1: true false
282 SELECT 'true, false'::jsonb; -- ERROR, too many values
283 ERROR: invalid input syntax for type json
284 LINE 1: SELECT 'true, false'::jsonb;
286 DETAIL: Expected end of input, but found ",".
287 CONTEXT: JSON data, line 1: true,...
288 SELECT 'truf'::jsonb; -- ERROR, not a keyword
289 ERROR: invalid input syntax for type json
290 LINE 1: SELECT 'truf'::jsonb;
292 DETAIL: Token "truf" is invalid.
293 CONTEXT: JSON data, line 1: truf
294 SELECT 'trues'::jsonb; -- ERROR, not a keyword
295 ERROR: invalid input syntax for type json
296 LINE 1: SELECT 'trues'::jsonb;
298 DETAIL: Token "trues" is invalid.
299 CONTEXT: JSON data, line 1: trues
300 SELECT ''::jsonb; -- ERROR, no value
301 ERROR: invalid input syntax for type json
302 LINE 1: SELECT ''::jsonb;
304 DETAIL: The input string ended unexpectedly.
305 CONTEXT: JSON data, line 1:
306 SELECT ' '::jsonb; -- ERROR, no value
307 ERROR: invalid input syntax for type json
308 LINE 1: SELECT ' '::jsonb;
310 DETAIL: The input string ended unexpectedly.
311 CONTEXT: JSON data, line 1:
312 -- make sure jsonb is passed through json generators without being escaped
313 SELECT array_to_json(ARRAY [jsonb '{"a":1}', jsonb '{"b":[2,3]}']);
315 --------------------------
316 [{"a": 1},{"b": [2, 3]}]
319 -- to_jsonb, timestamps
320 select to_jsonb(timestamp '2014-05-28 12:22:35.614298');
322 ------------------------------
323 "2014-05-28T12:22:35.614298"
327 SET LOCAL TIME ZONE 10.5;
328 select to_jsonb(timestamptz '2014-05-28 12:22:35.614298-04');
330 ------------------------------------
331 "2014-05-29T02:52:35.614298+10:30"
334 SET LOCAL TIME ZONE -8;
335 select to_jsonb(timestamptz '2014-05-28 12:22:35.614298-04');
337 ------------------------------------
338 "2014-05-28T08:22:35.614298-08:00"
342 select to_jsonb(date '2014-05-28');
348 select to_jsonb(date 'Infinity');
354 select to_jsonb(timestamp 'Infinity');
360 select to_jsonb(timestamptz 'Infinity');
367 CREATE TEMP TABLE rows AS
368 SELECT x, 'txt' || x as y
369 FROM generate_series(1,3) AS x;
371 FROM ( SELECT $$a$$ || x AS b, y AS c,
372 ARRAY[ROW(x.*,ARRAY[1,2,3]),
373 ROW(y.*,ARRAY[4,5,6])] AS z
374 FROM generate_series(1,2) x,
375 generate_series(4,5) y) q;
377 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
378 [{"b": "a1", "c": 4, "z": [{"f1": 1, "f2": [1, 2, 3]}, {"f1": 4, "f2": [4, 5, 6]}]}, {"b": "a1", "c": 5, "z": [{"f1": 1, "f2": [1, 2, 3]}, {"f1": 5, "f2": [4, 5, 6]}]}, {"b": "a2", "c": 4, "z": [{"f1": 2, "f2": [1, 2, 3]}, {"f1": 4, "f2": [4, 5, 6]}]}, {"b": "a2", "c": 5, "z": [{"f1": 2, "f2": [1, 2, 3]}, {"f1": 5, "f2": [4, 5, 6]}]}]
381 SELECT jsonb_agg(q ORDER BY x, y)
384 -----------------------------------------------------------------------
385 [{"x": 1, "y": "txt1"}, {"x": 2, "y": "txt2"}, {"x": 3, "y": "txt3"}]
388 UPDATE rows SET x = NULL WHERE x = 1;
389 SELECT jsonb_agg(q ORDER BY x NULLS FIRST, y)
392 --------------------------------------------------------------------------
393 [{"x": null, "y": "txt1"}, {"x": 2, "y": "txt2"}, {"x": 3, "y": "txt3"}]
396 -- jsonb extraction functions
397 CREATE TEMP TABLE test_jsonb (
401 INSERT INTO test_jsonb VALUES
402 ('scalar','"a scalar"'),
403 ('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'),
404 ('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}');
405 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
411 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array';
417 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'object';
423 SELECT test_json -> 'field2' FROM test_jsonb WHERE json_type = 'object';
429 SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'scalar';
435 SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'array';
441 SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'object';
447 SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'scalar';
453 SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'array';
459 SELECT test_json -> 9 FROM test_jsonb WHERE json_type = 'array';
465 SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'object';
471 SELECT test_json ->> 6 FROM test_jsonb WHERE json_type = 'array';
477 SELECT test_json ->> 7 FROM test_jsonb WHERE json_type = 'array';
483 SELECT test_json ->> 'field4' FROM test_jsonb WHERE json_type = 'object';
489 SELECT test_json ->> 'field5' FROM test_jsonb WHERE json_type = 'object';
495 SELECT test_json ->> 'field6' FROM test_jsonb WHERE json_type = 'object';
501 SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'scalar';
507 SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'array';
513 SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'object';
519 SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'scalar';
520 ERROR: cannot call jsonb_object_keys on a scalar
521 SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'array';
522 ERROR: cannot call jsonb_object_keys on an array
523 SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'object';
535 SELECT (test_json->'field3') IS NULL AS expect_false FROM test_jsonb WHERE json_type = 'object';
541 SELECT (test_json->>'field3') IS NULL AS expect_true FROM test_jsonb WHERE json_type = 'object';
547 SELECT (test_json->3) IS NULL AS expect_false FROM test_jsonb WHERE json_type = 'array';
553 SELECT (test_json->>3) IS NULL AS expect_true FROM test_jsonb WHERE json_type = 'array';
560 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::text;
566 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::int;
572 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 1;
578 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 'z';
584 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> '';
590 select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 1;
596 select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 3;
602 select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 'z';
608 select '{"a": "c", "b": null}'::jsonb -> 'b';
614 select '"foo"'::jsonb -> 1;
620 select '"foo"'::jsonb -> 'z';
626 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> null::text;
632 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> null::int;
638 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 1;
644 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 'z';
650 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> '';
656 select '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 1;
662 select '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 3;
668 select '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 'z';
674 select '{"a": "c", "b": null}'::jsonb ->> 'b';
680 select '"foo"'::jsonb ->> 1;
686 select '"foo"'::jsonb ->> 'z';
692 -- equality and inequality
693 SELECT '{"x":"y"}'::jsonb = '{"x":"y"}'::jsonb;
699 SELECT '{"x":"y"}'::jsonb = '{"x":"z"}'::jsonb;
705 SELECT '{"x":"y"}'::jsonb <> '{"x":"y"}'::jsonb;
711 SELECT '{"x":"y"}'::jsonb <> '{"x":"z"}'::jsonb;
718 SELECT jsonb_contains('{"a":"b", "b":1, "c":null}', '{"a":"b"}');
724 SELECT jsonb_contains('{"a":"b", "b":1, "c":null}', '{"a":"b", "c":null}');
730 SELECT jsonb_contains('{"a":"b", "b":1, "c":null}', '{"a":"b", "g":null}');
736 SELECT jsonb_contains('{"a":"b", "b":1, "c":null}', '{"g":null}');
742 SELECT jsonb_contains('{"a":"b", "b":1, "c":null}', '{"a":"c"}');
748 SELECT jsonb_contains('{"a":"b", "b":1, "c":null}', '{"a":"b"}');
754 SELECT jsonb_contains('{"a":"b", "b":1, "c":null}', '{"a":"b", "c":"q"}');
760 SELECT '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"b"}';
766 SELECT '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"b", "c":null}';
772 SELECT '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"b", "g":null}';
778 SELECT '{"a":"b", "b":1, "c":null}'::jsonb @> '{"g":null}';
784 SELECT '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"c"}';
790 SELECT '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"b"}';
796 SELECT '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"b", "c":"q"}';
802 SELECT '[1,2]'::jsonb @> '[1,2,2]'::jsonb;
808 SELECT '[1,1,2]'::jsonb @> '[1,2,2]'::jsonb;
814 SELECT '[[1,2]]'::jsonb @> '[[1,2,2]]'::jsonb;
820 SELECT '[1,2,2]'::jsonb <@ '[1,2]'::jsonb;
826 SELECT '[1,2,2]'::jsonb <@ '[1,1,2]'::jsonb;
832 SELECT '[[1,2,2]]'::jsonb <@ '[[1,2]]'::jsonb;
838 SELECT jsonb_contained('{"a":"b"}', '{"a":"b", "b":1, "c":null}');
844 SELECT jsonb_contained('{"a":"b", "c":null}', '{"a":"b", "b":1, "c":null}');
850 SELECT jsonb_contained('{"a":"b", "g":null}', '{"a":"b", "b":1, "c":null}');
856 SELECT jsonb_contained('{"g":null}', '{"a":"b", "b":1, "c":null}');
862 SELECT jsonb_contained('{"a":"c"}', '{"a":"b", "b":1, "c":null}');
868 SELECT jsonb_contained('{"a":"b"}', '{"a":"b", "b":1, "c":null}');
874 SELECT jsonb_contained('{"a":"b", "c":"q"}', '{"a":"b", "b":1, "c":null}');
880 SELECT '{"a":"b"}'::jsonb <@ '{"a":"b", "b":1, "c":null}';
886 SELECT '{"a":"b", "c":null}'::jsonb <@ '{"a":"b", "b":1, "c":null}';
892 SELECT '{"a":"b", "g":null}'::jsonb <@ '{"a":"b", "b":1, "c":null}';
898 SELECT '{"g":null}'::jsonb <@ '{"a":"b", "b":1, "c":null}';
904 SELECT '{"a":"c"}'::jsonb <@ '{"a":"b", "b":1, "c":null}';
910 SELECT '{"a":"b"}'::jsonb <@ '{"a":"b", "b":1, "c":null}';
916 SELECT '{"a":"b", "c":"q"}'::jsonb <@ '{"a":"b", "b":1, "c":null}';
922 -- Raw scalar may contain another raw scalar, array may contain a raw scalar
923 SELECT '[5]'::jsonb @> '[5]';
929 SELECT '5'::jsonb @> '5';
935 SELECT '[5]'::jsonb @> '5';
941 -- But a raw scalar cannot contain an array
942 SELECT '5'::jsonb @> '[5]';
948 -- In general, one thing should always contain itself. Test array containment:
949 SELECT '["9", ["7", "3"], 1]'::jsonb @> '["9", ["7", "3"], 1]'::jsonb;
955 SELECT '["9", ["7", "3"], ["1"]]'::jsonb @> '["9", ["7", "3"], ["1"]]'::jsonb;
961 -- array containment string matching confusion bug
962 SELECT '{ "name": "Bob", "tags": [ "enim", "qui"]}'::jsonb @> '{"tags":["qu"]}';
969 SELECT jsonb_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
975 SELECT jsonb_array_length('[]');
981 SELECT jsonb_array_length('{"f1":1,"f2":[5,6]}');
982 ERROR: cannot get array length of a non-array
983 SELECT jsonb_array_length('4');
984 ERROR: cannot get array length of a scalar
986 SELECT jsonb_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}');
994 SELECT jsonb_each('{"a":{"b":"c","c":"b","1":"first"},"b":[1,2],"c":"cc","1":"first","n":null}'::jsonb) AS q;
996 ------------------------------------------------------
998 (a,"{""1"": ""first"", ""b"": ""c"", ""c"": ""b""}")
1004 SELECT * FROM jsonb_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q;
1014 SELECT * FROM jsonb_each('{"a":{"b":"c","c":"b","1":"first"},"b":[1,2],"c":"cc","1":"first","n":null}'::jsonb) AS q;
1016 -----+------------------------------------
1018 a | {"1": "first", "b": "c", "c": "b"}
1024 SELECT jsonb_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":"null"}');
1026 --------------------
1033 SELECT jsonb_each_text('{"a":{"b":"c","c":"b","1":"first"},"b":[1,2],"c":"cc","1":"first","n":null}'::jsonb) AS q;
1035 ------------------------------------------------------
1037 (a,"{""1"": ""first"", ""b"": ""c"", ""c"": ""b""}")
1043 SELECT * FROM jsonb_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q;
1053 SELECT * FROM jsonb_each_text('{"a":{"b":"c","c":"b","1":"first"},"b":[1,2],"c":"cc","1":"first","n":null}'::jsonb) AS q;
1055 -----+------------------------------------
1057 a | {"1": "first", "b": "c", "c": "b"}
1064 SELECT jsonb_exists('{"a":null, "b":"qq"}', 'a');
1070 SELECT jsonb_exists('{"a":null, "b":"qq"}', 'b');
1076 SELECT jsonb_exists('{"a":null, "b":"qq"}', 'c');
1082 SELECT jsonb_exists('{"a":"null", "b":"qq"}', 'a');
1088 SELECT jsonb '{"a":null, "b":"qq"}' ? 'a';
1094 SELECT jsonb '{"a":null, "b":"qq"}' ? 'b';
1100 SELECT jsonb '{"a":null, "b":"qq"}' ? 'c';
1106 SELECT jsonb '{"a":"null", "b":"qq"}' ? 'a';
1112 -- array exists - array elements should behave as keys
1113 SELECT count(*) from testjsonb WHERE j->'array' ? 'bar';
1119 -- type sensitive array exists - should return no rows (since "exists" only
1120 -- matches strings that are either object keys or array elements)
1121 SELECT count(*) from testjsonb WHERE j->'array' ? '5'::text;
1127 -- However, a raw scalar is *contained* within the array
1128 SELECT count(*) from testjsonb WHERE j->'array' @> '5'::jsonb;
1134 SELECT jsonb_exists_any('{"a":null, "b":"qq"}', ARRAY['a','b']);
1140 SELECT jsonb_exists_any('{"a":null, "b":"qq"}', ARRAY['b','a']);
1146 SELECT jsonb_exists_any('{"a":null, "b":"qq"}', ARRAY['c','a']);
1152 SELECT jsonb_exists_any('{"a":null, "b":"qq"}', ARRAY['c','d']);
1158 SELECT jsonb_exists_any('{"a":null, "b":"qq"}', '{}'::text[]);
1164 SELECT jsonb '{"a":null, "b":"qq"}' ?| ARRAY['a','b'];
1170 SELECT jsonb '{"a":null, "b":"qq"}' ?| ARRAY['b','a'];
1176 SELECT jsonb '{"a":null, "b":"qq"}' ?| ARRAY['c','a'];
1182 SELECT jsonb '{"a":null, "b":"qq"}' ?| ARRAY['c','d'];
1188 SELECT jsonb '{"a":null, "b":"qq"}' ?| '{}'::text[];
1194 SELECT jsonb_exists_all('{"a":null, "b":"qq"}', ARRAY['a','b']);
1200 SELECT jsonb_exists_all('{"a":null, "b":"qq"}', ARRAY['b','a']);
1206 SELECT jsonb_exists_all('{"a":null, "b":"qq"}', ARRAY['c','a']);
1212 SELECT jsonb_exists_all('{"a":null, "b":"qq"}', ARRAY['c','d']);
1218 SELECT jsonb_exists_all('{"a":null, "b":"qq"}', '{}'::text[]);
1224 SELECT jsonb '{"a":null, "b":"qq"}' ?& ARRAY['a','b'];
1230 SELECT jsonb '{"a":null, "b":"qq"}' ?& ARRAY['b','a'];
1236 SELECT jsonb '{"a":null, "b":"qq"}' ?& ARRAY['c','a'];
1242 SELECT jsonb '{"a":null, "b":"qq"}' ?& ARRAY['c','d'];
1248 SELECT jsonb '{"a":null, "b":"qq"}' ?& ARRAY['a','a', 'b', 'b', 'b'];
1254 SELECT jsonb '{"a":null, "b":"qq"}' ?& '{}'::text[];
1261 SELECT jsonb_typeof('{}') AS object;
1267 SELECT jsonb_typeof('{"c":3,"p":"o"}') AS object;
1273 SELECT jsonb_typeof('[]') AS array;
1279 SELECT jsonb_typeof('["a", 1]') AS array;
1285 SELECT jsonb_typeof('null') AS "null";
1291 SELECT jsonb_typeof('1') AS number;
1297 SELECT jsonb_typeof('-1') AS number;
1303 SELECT jsonb_typeof('1.0') AS number;
1309 SELECT jsonb_typeof('1e2') AS number;
1315 SELECT jsonb_typeof('-1.0') AS number;
1321 SELECT jsonb_typeof('true') AS boolean;
1327 SELECT jsonb_typeof('false') AS boolean;
1333 SELECT jsonb_typeof('"hello"') AS string;
1339 SELECT jsonb_typeof('"true"') AS string;
1345 SELECT jsonb_typeof('"1.0"') AS string;
1351 -- jsonb_build_array, jsonb_build_object, jsonb_object_agg
1352 SELECT jsonb_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}');
1354 -------------------------------------------------------------------------
1355 ["a", 1, "b", 1.2, "c", true, "d", null, "e", {"x": 3, "y": [1, 2, 3]}]
1358 SELECT jsonb_build_object('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}');
1360 -------------------------------------------------------------------------
1361 {"a": 1, "b": 1.2, "c": true, "d": null, "e": {"x": 3, "y": [1, 2, 3]}}
1364 SELECT jsonb_build_object(
1365 'a', jsonb_build_object('b',false,'c',99),
1366 'd', jsonb_build_object('e',array[9,8,7]::int[],
1367 'f', (select row_to_json(r) from ( select relkind, oid::regclass as name from pg_class where relname = 'pg_class') r)));
1369 ------------------------------------------------------------------------------------------------
1370 {"a": {"b": false, "c": 99}, "d": {"e": [9, 8, 7], "f": {"name": "pg_class", "relkind": "r"}}}
1373 -- empty objects/arrays
1374 SELECT jsonb_build_array();
1380 SELECT jsonb_build_object();
1382 --------------------
1386 -- make sure keys are quoted
1387 SELECT jsonb_build_object(1,2);
1389 --------------------
1393 -- keys must be scalar and not null
1394 SELECT jsonb_build_object(null,2);
1395 ERROR: arg 1: key cannot be null
1396 SELECT jsonb_build_object(r,2) FROM (SELECT 1 AS a, 2 AS b) r;
1397 ERROR: key value must be scalar, not array, composite or json
1398 SELECT jsonb_build_object(json '{"a":1,"b":2}', 3);
1399 ERROR: key value must be scalar, not array, composite or json
1400 SELECT jsonb_build_object('{1,2,3}'::int[], 3);
1401 ERROR: key value must be scalar, not array, composite or json
1402 CREATE TEMP TABLE foo (serial_num int, name text, type text);
1403 INSERT INTO foo VALUES (847001,'t15','GE1043');
1404 INSERT INTO foo VALUES (847002,'t16','GE1043');
1405 INSERT INTO foo VALUES (847003,'sub-alpha','GESS90');
1406 SELECT jsonb_build_object('turbines',jsonb_object_agg(serial_num,jsonb_build_object('name',name,'type',type)))
1409 -------------------------------------------------------------------------------------------------------------------------------------------------------------
1410 {"turbines": {"847001": {"name": "t15", "type": "GE1043"}, "847002": {"name": "t16", "type": "GE1043"}, "847003": {"name": "sub-alpha", "type": "GESS90"}}}
1413 SELECT jsonb_object_agg(name, type) FROM foo;
1415 -----------------------------------------------------------
1416 {"t15": "GE1043", "t16": "GE1043", "sub-alpha": "GESS90"}
1419 INSERT INTO foo VALUES (999999, NULL, 'bar');
1420 SELECT jsonb_object_agg(name, type) FROM foo;
1421 ERROR: field name must not be null
1424 SELECT jsonb_object('{a,1,b,2,3,NULL,"d e f","a b c"}');
1426 ---------------------------------------------------
1427 {"3": null, "a": "1", "b": "2", "d e f": "a b c"}
1430 -- same but with two dimensions
1431 SELECT jsonb_object('{{a,1},{b,2},{3,NULL},{"d e f","a b c"}}');
1433 ---------------------------------------------------
1434 {"3": null, "a": "1", "b": "2", "d e f": "a b c"}
1438 SELECT jsonb_object('{a,b,c}');
1439 ERROR: array must have even number of elements
1441 SELECT jsonb_object('{{a},{b}}');
1442 ERROR: array must have two columns
1443 -- too many columns error
1444 SELECT jsonb_object('{{a,b,c},{b,c,d}}');
1445 ERROR: array must have two columns
1446 -- too many dimensions error
1447 SELECT jsonb_object('{{{a,b},{c,d}},{{b,c},{d,e}}}');
1448 ERROR: wrong number of array subscripts
1449 --two argument form of jsonb_object
1450 select jsonb_object('{a,b,c,"d e f"}','{1,2,3,"a b c"}');
1452 --------------------------------------------------
1453 {"a": "1", "b": "2", "c": "3", "d e f": "a b c"}
1456 -- too many dimensions
1457 SELECT jsonb_object('{{a,1},{b,2},{3,NULL},{"d e f","a b c"}}', '{{a,1},{b,2},{3,NULL},{"d e f","a b c"}}');
1458 ERROR: wrong number of array subscripts
1459 -- mismatched dimensions
1460 select jsonb_object('{a,b,c,"d e f",g}','{1,2,3,"a b c"}');
1461 ERROR: mismatched array dimensions
1462 select jsonb_object('{a,b,c,"d e f"}','{1,2,3,"a b c",g}');
1463 ERROR: mismatched array dimensions
1465 select jsonb_object('{a,b,NULL,"d e f"}','{1,2,3,"a b c"}');
1466 ERROR: null value not allowed for object key
1467 -- empty key is allowed
1468 select jsonb_object('{a,b,"","d e f"}','{1,2,3,"a b c"}');
1470 -------------------------------------------------
1471 {"": "3", "a": "1", "b": "2", "d e f": "a b c"}
1474 -- extract_path, extract_path_as_text
1475 SELECT jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6');
1477 --------------------
1481 SELECT jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2');
1483 --------------------
1487 SELECT jsonb_extract_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',0::text);
1489 --------------------
1493 SELECT jsonb_extract_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',1::text);
1495 --------------------
1499 SELECT jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6');
1500 jsonb_extract_path_text
1501 -------------------------
1505 SELECT jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2');
1506 jsonb_extract_path_text
1507 -------------------------
1511 SELECT jsonb_extract_path_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',0::text);
1512 jsonb_extract_path_text
1513 -------------------------
1517 SELECT jsonb_extract_path_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',1::text);
1518 jsonb_extract_path_text
1519 -------------------------
1523 -- extract_path nulls
1524 SELECT jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') IS NULL AS expect_false;
1530 SELECT jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') IS NULL AS expect_true;
1536 SELECT jsonb_extract_path('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') IS NULL AS expect_false;
1542 SELECT jsonb_extract_path_text('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') IS NULL AS expect_true;
1548 -- extract_path operators
1549 SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>array['f4','f6'];
1555 SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>array['f2'];
1561 SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>array['f2','0'];
1567 SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>array['f2','1'];
1573 SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f4','f6'];
1579 SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f2'];
1585 SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f2','0'];
1591 SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f2','1'];
1597 -- corner cases for same
1598 select '{"a": {"b":{"c": "foo"}}}'::jsonb #> '{}';
1600 ----------------------------
1601 {"a": {"b": {"c": "foo"}}}
1604 select '[1,2,3]'::jsonb #> '{}';
1610 select '"foo"'::jsonb #> '{}';
1616 select '42'::jsonb #> '{}';
1622 select 'null'::jsonb #> '{}';
1628 select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a'];
1630 ---------------------
1634 select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a', null];
1640 select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a', ''];
1646 select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','b'];
1652 select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','b','c'];
1658 select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','b','c','d'];
1664 select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','z','c'];
1670 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #> array['a','1','b'];
1676 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #> array['a','z','b'];
1682 select '[{"b": "c"}, {"b": "cc"}]'::jsonb #> array['1','b'];
1688 select '[{"b": "c"}, {"b": "cc"}]'::jsonb #> array['z','b'];
1694 select '[{"b": "c"}, {"b": null}]'::jsonb #> array['1','b'];
1700 select '"foo"'::jsonb #> array['z'];
1706 select '42'::jsonb #> array['f2'];
1712 select '42'::jsonb #> array['0'];
1718 select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> '{}';
1720 ----------------------------
1721 {"a": {"b": {"c": "foo"}}}
1724 select '[1,2,3]'::jsonb #>> '{}';
1730 select '"foo"'::jsonb #>> '{}';
1736 select '42'::jsonb #>> '{}';
1742 select 'null'::jsonb #>> '{}';
1748 select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a'];
1750 ---------------------
1754 select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a', null];
1760 select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a', ''];
1766 select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','b'];
1772 select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','b','c'];
1778 select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','b','c','d'];
1784 select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','z','c'];
1790 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #>> array['a','1','b'];
1796 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #>> array['a','z','b'];
1802 select '[{"b": "c"}, {"b": "cc"}]'::jsonb #>> array['1','b'];
1808 select '[{"b": "c"}, {"b": "cc"}]'::jsonb #>> array['z','b'];
1814 select '[{"b": "c"}, {"b": null}]'::jsonb #>> array['1','b'];
1820 select '"foo"'::jsonb #>> array['z'];
1826 select '42'::jsonb #>> array['f2'];
1832 select '42'::jsonb #>> array['0'];
1839 SELECT jsonb_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]');
1840 jsonb_array_elements
1841 ----------------------------
1846 {"f1": 1, "f2": [7, 8, 9]}
1850 SELECT * FROM jsonb_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]') q;
1852 ----------------------------
1857 {"f1": 1, "f2": [7, 8, 9]}
1861 SELECT jsonb_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]');
1862 jsonb_array_elements_text
1863 ----------------------------
1868 {"f1": 1, "f2": [7, 8, 9]}
1873 SELECT * FROM jsonb_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q;
1875 ----------------------------
1880 {"f1": 1, "f2": [7, 8, 9]}
1886 CREATE TYPE jbpop AS (a text, b int, c timestamp);
1887 SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q;
1893 SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}') q;
1895 --------+---+--------------------------
1896 blurfl | 3 | Mon Dec 31 15:30:56 2012
1899 SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q;
1905 SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}') q;
1907 --------+---+--------------------------
1908 blurfl | 3 | Mon Dec 31 15:30:56 2012
1911 SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":[100,200,false],"x":43.2}') q;
1913 -------------------+---+---
1914 [100, 200, false] | |
1917 SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":[100,200,false],"x":43.2}') q;
1919 -------------------+---+--------------------------
1920 [100, 200, false] | 3 | Mon Dec 31 15:30:56 2012
1923 SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"c":[100,200,false],"x":43.2}') q;
1924 ERROR: invalid input syntax for type timestamp: "[100, 200, false]"
1925 -- populate_recordset
1926 SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
1928 --------+---+--------------------------
1930 | 3 | Fri Jan 20 10:42:53 2012
1933 SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
1935 --------+----+--------------------------
1937 def | 3 | Fri Jan 20 10:42:53 2012
1940 SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
1942 --------+---+--------------------------
1944 | 3 | Fri Jan 20 10:42:53 2012
1947 SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
1949 --------+----+--------------------------
1951 def | 3 | Fri Jan 20 10:42:53 2012
1954 SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q;
1956 -----------------+----+--------------------------
1957 [100, 200, 300] | 99 |
1958 {"z": true} | 3 | Fri Jan 20 10:42:53 2012
1961 SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q;
1962 ERROR: invalid input syntax for type timestamp: "[100, 200, 300]"
1963 SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
1965 --------+---+--------------------------
1967 | 3 | Fri Jan 20 10:42:53 2012
1970 SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
1972 --------+----+--------------------------
1974 def | 3 | Fri Jan 20 10:42:53 2012
1977 SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q;
1979 -----------------+----+--------------------------
1980 [100, 200, 300] | 99 |
1981 {"z": true} | 3 | Fri Jan 20 10:42:53 2012
1984 -- handling of unicode surrogate pairs
1985 SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a')::text) AS correct_in_utf8;
1986 ERROR: unsupported Unicode escape sequence
1987 LINE 1: SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc3...
1989 DETAIL: Unicode escape values cannot be used for code point values above 007F when the server encoding is not UTF8.
1990 CONTEXT: JSON data, line 1: { "a":...
1991 SELECT jsonb '{ "a": "\ud83d\ud83d" }' -> 'a'; -- 2 high surrogates in a row
1992 ERROR: invalid input syntax for type json
1993 LINE 1: SELECT jsonb '{ "a": "\ud83d\ud83d" }' -> 'a';
1995 DETAIL: Unicode high surrogate must not follow a high surrogate.
1996 CONTEXT: JSON data, line 1: { "a":...
1997 SELECT jsonb '{ "a": "\ude04\ud83d" }' -> 'a'; -- surrogates in wrong order
1998 ERROR: invalid input syntax for type json
1999 LINE 1: SELECT jsonb '{ "a": "\ude04\ud83d" }' -> 'a';
2001 DETAIL: Unicode low surrogate must follow a high surrogate.
2002 CONTEXT: JSON data, line 1: { "a":...
2003 SELECT jsonb '{ "a": "\ud83dX" }' -> 'a'; -- orphan high surrogate
2004 ERROR: invalid input syntax for type json
2005 LINE 1: SELECT jsonb '{ "a": "\ud83dX" }' -> 'a';
2007 DETAIL: Unicode low surrogate must follow a high surrogate.
2008 CONTEXT: JSON data, line 1: { "a":...
2009 SELECT jsonb '{ "a": "\ude04X" }' -> 'a'; -- orphan low surrogate
2010 ERROR: invalid input syntax for type json
2011 LINE 1: SELECT jsonb '{ "a": "\ude04X" }' -> 'a';
2013 DETAIL: Unicode low surrogate must follow a high surrogate.
2014 CONTEXT: JSON data, line 1: { "a":...
2015 -- handling of simple unicode escapes
2016 SELECT jsonb '{ "a": "the Copyright \u00a9 sign" }' as correct_in_utf8;
2017 ERROR: unsupported Unicode escape sequence
2018 LINE 1: SELECT jsonb '{ "a": "the Copyright \u00a9 sign" }' as corr...
2020 DETAIL: Unicode escape values cannot be used for code point values above 007F when the server encoding is not UTF8.
2021 CONTEXT: JSON data, line 1: { "a":...
2022 SELECT jsonb '{ "a": "dollar \u0024 character" }' as correct_everywhere;
2024 -----------------------------
2025 {"a": "dollar $ character"}
2028 SELECT jsonb '{ "a": "dollar \\u0024 character" }' as not_an_escape;
2030 -----------------------------------
2031 {"a": "dollar \\u0024 character"}
2034 SELECT jsonb '{ "a": "null \u0000 escape" }' as fails;
2035 ERROR: unsupported Unicode escape sequence
2036 LINE 1: SELECT jsonb '{ "a": "null \u0000 escape" }' as fails;
2038 DETAIL: \u0000 cannot be converted to text.
2039 CONTEXT: JSON data, line 1: { "a":...
2040 SELECT jsonb '{ "a": "null \\u0000 escape" }' as not_an_escape;
2042 ------------------------------
2043 {"a": "null \\u0000 escape"}
2046 SELECT jsonb '{ "a": "the Copyright \u00a9 sign" }' ->> 'a' as correct_in_utf8;
2047 ERROR: unsupported Unicode escape sequence
2048 LINE 1: SELECT jsonb '{ "a": "the Copyright \u00a9 sign" }' ->> 'a'...
2050 DETAIL: Unicode escape values cannot be used for code point values above 007F when the server encoding is not UTF8.
2051 CONTEXT: JSON data, line 1: { "a":...
2052 SELECT jsonb '{ "a": "dollar \u0024 character" }' ->> 'a' as correct_everywhere;
2054 --------------------
2058 SELECT jsonb '{ "a": "dollar \\u0024 character" }' ->> 'a' as not_an_escape;
2060 -------------------------
2061 dollar \u0024 character
2064 SELECT jsonb '{ "a": "null \u0000 escape" }' ->> 'a' as fails;
2065 ERROR: unsupported Unicode escape sequence
2066 LINE 1: SELECT jsonb '{ "a": "null \u0000 escape" }' ->> 'a' as fai...
2068 DETAIL: \u0000 cannot be converted to text.
2069 CONTEXT: JSON data, line 1: { "a":...
2070 SELECT jsonb '{ "a": "null \\u0000 escape" }' ->> 'a' as not_an_escape;
2072 --------------------
2076 -- jsonb_to_record and jsonb_to_recordset
2077 select * from jsonb_to_record('{"a":1,"b":"foo","c":"bar"}')
2078 as x(a int, b text, d text);
2084 select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]')
2085 as x(a int, b text, c boolean);
2093 SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';
2099 SELECT count(*) FROM testjsonb WHERE j @> '{"wait":"CC"}';
2105 SELECT count(*) FROM testjsonb WHERE j @> '{"wait":"CC", "public":true}';
2111 SELECT count(*) FROM testjsonb WHERE j @> '{"age":25}';
2117 SELECT count(*) FROM testjsonb WHERE j @> '{"age":25.0}';
2123 SELECT count(*) FROM testjsonb WHERE j ? 'public';
2129 SELECT count(*) FROM testjsonb WHERE j ? 'bar';
2135 SELECT count(*) FROM testjsonb WHERE j ?| ARRAY['public','disabled'];
2141 SELECT count(*) FROM testjsonb WHERE j ?& ARRAY['public','disabled'];
2147 CREATE INDEX jidx ON testjsonb USING gin (j);
2148 SET enable_seqscan = off;
2149 SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';
2155 SELECT count(*) FROM testjsonb WHERE j @> '{"wait":"CC"}';
2161 SELECT count(*) FROM testjsonb WHERE j @> '{"wait":"CC", "public":true}';
2167 SELECT count(*) FROM testjsonb WHERE j @> '{"age":25}';
2173 SELECT count(*) FROM testjsonb WHERE j @> '{"age":25.0}';
2179 SELECT count(*) FROM testjsonb WHERE j @> '{"array":["foo"]}';
2185 SELECT count(*) FROM testjsonb WHERE j @> '{"array":["bar"]}';
2191 -- exercise GIN_SEARCH_MODE_ALL
2192 SELECT count(*) FROM testjsonb WHERE j @> '{}';
2198 SELECT count(*) FROM testjsonb WHERE j ? 'public';
2204 SELECT count(*) FROM testjsonb WHERE j ? 'bar';
2210 SELECT count(*) FROM testjsonb WHERE j ?| ARRAY['public','disabled'];
2216 SELECT count(*) FROM testjsonb WHERE j ?& ARRAY['public','disabled'];
2222 -- array exists - array elements should behave as keys (for GIN index scans too)
2223 CREATE INDEX jidx_array ON testjsonb USING gin((j->'array'));
2224 SELECT count(*) from testjsonb WHERE j->'array' ? 'bar';
2230 -- type sensitive array exists - should return no rows (since "exists" only
2231 -- matches strings that are either object keys or array elements)
2232 SELECT count(*) from testjsonb WHERE j->'array' ? '5'::text;
2238 -- However, a raw scalar is *contained* within the array
2239 SELECT count(*) from testjsonb WHERE j->'array' @> '5'::jsonb;
2245 RESET enable_seqscan;
2246 SELECT count(*) FROM (SELECT (jsonb_each(j)).key FROM testjsonb) AS wow;
2252 SELECT key, count(*) FROM (SELECT (jsonb_each(j)).key FROM testjsonb) AS wow GROUP BY key ORDER BY count DESC, key;
2284 SELECT count(distinct j) FROM testjsonb;
2290 SET enable_hashagg = off;
2291 SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT * FROM testjsonb) js GROUP BY j) js2;
2297 SET enable_hashagg = on;
2298 SET enable_sort = off;
2299 SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT * FROM testjsonb) js GROUP BY j) js2;
2305 SELECT distinct * FROM (values (jsonb '{}' || ''::text),('{}')) v(j);
2311 SET enable_sort = on;
2312 RESET enable_hashagg;
2315 DROP INDEX jidx_array;
2317 CREATE INDEX jidx ON testjsonb USING btree (j);
2318 SET enable_seqscan = off;
2319 SELECT count(*) FROM testjsonb WHERE j > '{"p":1}';
2325 SELECT count(*) FROM testjsonb WHERE j = '{"pos":98, "line":371, "node":"CBA", "indexed":true}';
2333 CREATE INDEX jidx ON testjsonb USING gin (j jsonb_path_ops);
2334 SET enable_seqscan = off;
2335 SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';
2341 SELECT count(*) FROM testjsonb WHERE j @> '{"wait":"CC"}';
2347 SELECT count(*) FROM testjsonb WHERE j @> '{"wait":"CC", "public":true}';
2353 SELECT count(*) FROM testjsonb WHERE j @> '{"age":25}';
2359 SELECT count(*) FROM testjsonb WHERE j @> '{"age":25.0}';
2365 -- exercise GIN_SEARCH_MODE_ALL
2366 SELECT count(*) FROM testjsonb WHERE j @> '{}';
2372 RESET enable_seqscan;
2375 SELECT '{"ff":{"a":12,"b":16}}'::jsonb;
2377 ----------------------------
2378 {"ff": {"a": 12, "b": 16}}
2381 SELECT '{"ff":{"a":12,"b":16},"qq":123}'::jsonb;
2383 ---------------------------------------
2384 {"ff": {"a": 12, "b": 16}, "qq": 123}
2387 SELECT '{"aa":["a","aaa"],"qq":{"a":12,"b":16,"c":["c1","c2"],"d":{"d1":"d1","d2":"d2","d1":"d3"}}}'::jsonb;
2389 --------------------------------------------------------------------------------------------------
2390 {"aa": ["a", "aaa"], "qq": {"a": 12, "b": 16, "c": ["c1", "c2"], "d": {"d1": "d3", "d2": "d2"}}}
2393 SELECT '{"aa":["a","aaa"],"qq":{"a":"12","b":"16","c":["c1","c2"],"d":{"d1":"d1","d2":"d2"}}}'::jsonb;
2395 ------------------------------------------------------------------------------------------------------
2396 {"aa": ["a", "aaa"], "qq": {"a": "12", "b": "16", "c": ["c1", "c2"], "d": {"d1": "d1", "d2": "d2"}}}
2399 SELECT '{"aa":["a","aaa"],"qq":{"a":"12","b":"16","c":["c1","c2",["c3"],{"c4":4}],"d":{"d1":"d1","d2":"d2"}}}'::jsonb;
2401 -------------------------------------------------------------------------------------------------------------------------
2402 {"aa": ["a", "aaa"], "qq": {"a": "12", "b": "16", "c": ["c1", "c2", ["c3"], {"c4": 4}], "d": {"d1": "d1", "d2": "d2"}}}
2405 SELECT '{"ff":["a","aaa"]}'::jsonb;
2407 ----------------------
2408 {"ff": ["a", "aaa"]}
2412 '{"ff":{"a":12,"b":16},"qq":123,"x":[1,2],"Y":null}'::jsonb -> 'ff',
2413 '{"ff":{"a":12,"b":16},"qq":123,"x":[1,2],"Y":null}'::jsonb -> 'qq',
2414 ('{"ff":{"a":12,"b":16},"qq":123,"x":[1,2],"Y":null}'::jsonb -> 'Y') IS NULL AS f,
2415 ('{"ff":{"a":12,"b":16},"qq":123,"x":[1,2],"Y":null}'::jsonb ->> 'Y') IS NULL AS t,
2416 '{"ff":{"a":12,"b":16},"qq":123,"x":[1,2],"Y":null}'::jsonb -> 'x';
2417 ?column? | ?column? | f | t | ?column?
2418 --------------------+----------+---+---+----------
2419 {"a": 12, "b": 16} | 123 | f | t | [1, 2]
2422 -- nested containment
2423 SELECT '{"a":[1,2],"c":"b"}'::jsonb @> '{"a":[1,2]}';
2429 SELECT '{"a":[2,1],"c":"b"}'::jsonb @> '{"a":[1,2]}';
2435 SELECT '{"a":{"1":2},"c":"b"}'::jsonb @> '{"a":[1,2]}';
2441 SELECT '{"a":{"2":1},"c":"b"}'::jsonb @> '{"a":[1,2]}';
2447 SELECT '{"a":{"1":2},"c":"b"}'::jsonb @> '{"a":{"1":2}}';
2453 SELECT '{"a":{"2":1},"c":"b"}'::jsonb @> '{"a":{"1":2}}';
2459 SELECT '["a","b"]'::jsonb @> '["a","b","c","b"]';
2465 SELECT '["a","b","c","b"]'::jsonb @> '["a","b"]';
2471 SELECT '["a","b","c",[1,2]]'::jsonb @> '["a",[1,2]]';
2477 SELECT '["a","b","c",[1,2]]'::jsonb @> '["b",[1,2]]';
2483 SELECT '{"a":[1,2],"c":"b"}'::jsonb @> '{"a":[1]}';
2489 SELECT '{"a":[1,2],"c":"b"}'::jsonb @> '{"a":[2]}';
2495 SELECT '{"a":[1,2],"c":"b"}'::jsonb @> '{"a":[3]}';
2501 SELECT '{"a":[1,2,{"c":3,"x":4}],"c":"b"}'::jsonb @> '{"a":[{"c":3}]}';
2507 SELECT '{"a":[1,2,{"c":3,"x":4}],"c":"b"}'::jsonb @> '{"a":[{"x":4}]}';
2513 SELECT '{"a":[1,2,{"c":3,"x":4}],"c":"b"}'::jsonb @> '{"a":[{"x":4},3]}';
2519 SELECT '{"a":[1,2,{"c":3,"x":4}],"c":"b"}'::jsonb @> '{"a":[{"x":4},1]}';
2525 -- nested object field / array index lookup
2526 SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'n';
2532 SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'a';
2538 SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'b';
2544 SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'c';
2550 SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'd';
2556 SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'd' -> '1';
2562 SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'e';
2568 SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 0; --expecting error
2574 SELECT '["a","b","c",[1,2],null]'::jsonb -> 0;
2580 SELECT '["a","b","c",[1,2],null]'::jsonb -> 1;
2586 SELECT '["a","b","c",[1,2],null]'::jsonb -> 2;
2592 SELECT '["a","b","c",[1,2],null]'::jsonb -> 3;
2598 SELECT '["a","b","c",[1,2],null]'::jsonb -> 3 -> 1;
2604 SELECT '["a","b","c",[1,2],null]'::jsonb -> 4;
2610 SELECT '["a","b","c",[1,2],null]'::jsonb -> 5;
2616 SELECT '["a","b","c",[1,2],null]'::jsonb -> -1;
2622 SELECT '["a","b","c",[1,2],null]'::jsonb -> -5;
2628 SELECT '["a","b","c",[1,2],null]'::jsonb -> -6;
2634 --nested path extraction
2635 SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{0}';
2641 SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{a}';
2647 SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c}';
2653 SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,0}';
2659 SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,1}';
2665 SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,2}';
2671 SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,3}';
2677 SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,-1}';
2683 SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,-3}';
2689 SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,-4}';
2695 SELECT '[0,1,2,[3,4],{"5":"five"}]'::jsonb #> '{0}';
2701 SELECT '[0,1,2,[3,4],{"5":"five"}]'::jsonb #> '{3}';
2707 SELECT '[0,1,2,[3,4],{"5":"five"}]'::jsonb #> '{4}';
2713 SELECT '[0,1,2,[3,4],{"5":"five"}]'::jsonb #> '{4,5}';
2720 SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'n';
2726 SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'a';
2732 SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'b';
2738 SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'c';
2744 SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'd';
2750 SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'e';
2756 -- jsonb_strip_nulls
2757 select jsonb_strip_nulls(null);
2763 select jsonb_strip_nulls('1');
2769 select jsonb_strip_nulls('"a string"');
2775 select jsonb_strip_nulls('null');
2781 select jsonb_strip_nulls('[1,2,null,3,4]');
2783 --------------------
2787 select jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}');
2789 --------------------------------------------
2790 {"a": 1, "c": [2, null, 3], "d": {"e": 4}}
2793 select jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');
2795 --------------------------
2796 [1, {"a": 1, "c": 2}, 3]
2799 -- an empty object is not null and should not be stripped
2800 select jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
2802 --------------------
2806 select jsonb_pretty('{"a": "test", "b": [1, 2, 3], "c": "test3", "d":{"dd": "test4", "dd2":{"ddd": "test5"}}}');
2808 ----------------------------
2826 select jsonb_pretty('[{"f1":1,"f2":null},2,null,[[{"x":true},6,7],8],3]');
2828 ---------------------------
2850 select jsonb_pretty('{"a":["b", "c"], "d": {"e":"f"}}');
2864 select jsonb_concat('{"d": "test", "a": [1, 2]}', '{"g": "test2", "c": {"c1":1, "c2":2}}');
2866 -------------------------------------------------------------------
2867 {"a": [1, 2], "c": {"c1": 1, "c2": 2}, "d": "test", "g": "test2"}
2870 select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"cq":"l", "b":"g", "fg":false}';
2872 ---------------------------------------------
2873 {"b": "g", "aa": 1, "cq": "l", "fg": false}
2876 select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"aq":"l"}';
2878 ---------------------------------------
2879 {"b": 2, "aa": 1, "aq": "l", "cq": 3}
2882 select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"aa":"l"}';
2884 ------------------------------
2885 {"b": 2, "aa": "l", "cq": 3}
2888 select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{}';
2890 ----------------------------
2891 {"b": 2, "aa": 1, "cq": 3}
2894 select '["a", "b"]'::jsonb || '["c"]';
2900 select '["a", "b"]'::jsonb || '["c", "d"]';
2902 ----------------------
2903 ["a", "b", "c", "d"]
2906 select '["c"]' || '["a", "b"]'::jsonb;
2912 select '["a", "b"]'::jsonb || '"c"';
2918 select '"c"' || '["a", "b"]'::jsonb;
2924 select '[]'::jsonb || '["a"]'::jsonb;
2930 select '[]'::jsonb || '"a"'::jsonb;
2936 select '"b"'::jsonb || '"a"'::jsonb;
2942 select '{}'::jsonb || '{"a":"b"}'::jsonb;
2948 select '[]'::jsonb || '{"a":"b"}'::jsonb;
2954 select '{"a":"b"}'::jsonb || '[]'::jsonb;
2960 select '"a"'::jsonb || '{"a":1}';
2961 ERROR: invalid concatenation of jsonb objects
2962 select '{"a":1}' || '"a"'::jsonb;
2963 ERROR: invalid concatenation of jsonb objects
2964 select '["a", "b"]'::jsonb || '{"c":1}';
2966 ----------------------
2967 ["a", "b", {"c": 1}]
2970 select '{"c": 1}'::jsonb || '["a", "b"]';
2972 ----------------------
2973 [{"c": 1}, "a", "b"]
2976 select '{}'::jsonb || '{"cq":"l", "b":"g", "fg":false}';
2978 ------------------------------------
2979 {"b": "g", "cq": "l", "fg": false}
2982 select pg_column_size('{}'::jsonb || '{}'::jsonb) = pg_column_size('{}'::jsonb);
2988 select pg_column_size('{"aa":1}'::jsonb || '{"b":2}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb);
2994 select pg_column_size('{"aa":1, "b":2}'::jsonb || '{}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb);
3000 select pg_column_size('{}'::jsonb || '{"aa":1, "b":2}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb);
3006 select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'a');
3012 select jsonb_delete('{"a":null , "b":2, "c":3}'::jsonb, 'a');
3018 select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'b');
3024 select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'c');
3030 select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'd');
3032 --------------------------
3033 {"a": 1, "b": 2, "c": 3}
3036 select '{"a":1 , "b":2, "c":3}'::jsonb - 'a';
3042 select '{"a":null , "b":2, "c":3}'::jsonb - 'a';
3048 select '{"a":1 , "b":2, "c":3}'::jsonb - 'b';
3054 select '{"a":1 , "b":2, "c":3}'::jsonb - 'c';
3060 select '{"a":1 , "b":2, "c":3}'::jsonb - 'd';
3062 --------------------------
3063 {"a": 1, "b": 2, "c": 3}
3066 select pg_column_size('{"a":1 , "b":2, "c":3}'::jsonb - 'b') = pg_column_size('{"a":1, "b":2}'::jsonb);
3072 select '["a","b","c"]'::jsonb - 3;
3078 select '["a","b","c"]'::jsonb - 2;
3084 select '["a","b","c"]'::jsonb - 1;
3090 select '["a","b","c"]'::jsonb - 0;
3096 select '["a","b","c"]'::jsonb - -1;
3102 select '["a","b","c"]'::jsonb - -2;
3108 select '["a","b","c"]'::jsonb - -3;
3114 select '["a","b","c"]'::jsonb - -4;
3120 select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]');
3122 --------------------------------------------------------------------------
3123 {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": [1, 2, 3]}
3126 select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '[1,2,3]');
3128 -----------------------------------------------------------------------------
3129 {"a": 1, "b": [1, [1, 2, 3]], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
3132 select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '[1,2,3]');
3134 -----------------------------------------------------------------------------
3135 {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [[1, 2, 3], 3]}, "n": null}
3138 select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '[1,2,3]');
3139 ERROR: path element at the position 2 is NULL
3140 select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '{"1": 2}');
3142 -------------------------------------------------------------------------
3143 {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": {"1": 2}}
3146 select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"1": 2}');
3148 ----------------------------------------------------------------------------
3149 {"a": 1, "b": [1, {"1": 2}], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
3152 select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '{"1": 2}');
3154 ----------------------------------------------------------------------------
3155 {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [{"1": 2}, 3]}, "n": null}
3158 select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '{"1": 2}');
3159 ERROR: path element at the position 2 is NULL
3160 select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '"test"');
3162 --------------------------------------------------------------------------
3163 {"a": 1, "b": [1, "test"], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
3166 select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"f": "test"}');
3168 ---------------------------------------------------------------------------------
3169 {"a": 1, "b": [1, {"f": "test"}], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
3172 select jsonb_delete_path('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{n}');
3174 ----------------------------------------------------------
3175 {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}}
3178 select jsonb_delete_path('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{b,-1}');
3180 ------------------------------------------------------------------
3181 {"a": 1, "b": [1], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
3184 select jsonb_delete_path('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{d,1,0}');
3186 ------------------------------------------------------------------
3187 {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [3]}, "n": null}
3190 select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb #- '{n}';
3192 ----------------------------------------------------------
3193 {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}}
3196 select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb #- '{b,-1}';
3198 ------------------------------------------------------------------
3199 {"a": 1, "b": [1], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
3202 select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb #- '{b,-1e}'; -- invalid array subscript
3203 ERROR: path element at the position 2 is not an integer
3204 select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb #- '{d,1,0}';
3206 ------------------------------------------------------------------
3207 {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [3]}, "n": null}
3210 -- empty structure and error conditions for delete and replace
3211 select '"a"'::jsonb - 'a'; -- error
3212 ERROR: cannot delete from scalar
3213 select '{}'::jsonb - 'a';
3219 select '[]'::jsonb - 'a';
3225 select '"a"'::jsonb - 1; -- error
3226 ERROR: cannot delete from scalar
3227 select '{}'::jsonb - 1; -- error
3228 ERROR: cannot delete from object using integer subscript
3229 select '[]'::jsonb - 1;
3235 select '"a"'::jsonb #- '{a}'; -- error
3236 ERROR: cannot delete path in scalar
3237 select '{}'::jsonb #- '{a}';
3243 select '[]'::jsonb #- '{a}';
3249 select jsonb_set('"a"','{a}','"b"'); --error
3250 ERROR: cannot set path in scalar
3251 select jsonb_set('{}','{a}','"b"', false);
3257 select jsonb_set('[]','{1}','"b"', false);
3263 -- jsonb_set adding instead of replacing
3265 select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{b,-33}','{"foo":123}');
3267 -------------------------------------------------------
3268 {"a": 1, "b": [{"foo": 123}, 0, 1, 2], "c": {"d": 4}}
3272 select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{b,33}','{"foo":123}');
3274 -------------------------------------------------------
3275 {"a": 1, "b": [0, 1, 2, {"foo": 123}], "c": {"d": 4}}
3278 -- check nesting levels addition
3279 select jsonb_set('{"a":1,"b":[4,5,[0,1,2],6,7],"c":{"d":4}}','{b,2,33}','{"foo":123}');
3281 ---------------------------------------------------------------------
3282 {"a": 1, "b": [4, 5, [0, 1, 2, {"foo": 123}], 6, 7], "c": {"d": 4}}
3286 select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{c,e}','{"foo":123}');
3288 ------------------------------------------------------------
3289 {"a": 1, "b": [0, 1, 2], "c": {"d": 4, "e": {"foo": 123}}}
3292 -- adding doesn't do anything if elements before last aren't present
3293 select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{x,-33}','{"foo":123}');
3295 -----------------------------------------
3296 {"a": 1, "b": [0, 1, 2], "c": {"d": 4}}
3299 select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{x,y}','{"foo":123}');
3301 -----------------------------------------
3302 {"a": 1, "b": [0, 1, 2], "c": {"d": 4}}
3305 -- add to empty object
3306 select jsonb_set('{}','{x}','{"foo":123}');
3308 ---------------------
3312 --add to empty array
3313 select jsonb_set('[]','{0}','{"foo":123}');
3319 select jsonb_set('[]','{99}','{"foo":123}');
3325 select jsonb_set('[]','{-99}','{"foo":123}');
3331 select jsonb_set('{"a": [1, 2, 3]}', '{a, non_integer}', '"new_value"');
3332 ERROR: path element at the position 2 is not an integer
3333 select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, non_integer}', '"new_value"');
3334 ERROR: path element at the position 3 is not an integer
3335 select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, NULL}', '"new_value"');
3336 ERROR: path element at the position 3 is NULL