2 SELECT '""'::json; -- OK.
8 SELECT $$''$$::json; -- ERROR, single quotes are not allowed
9 ERROR: invalid input syntax for type json
10 LINE 1: SELECT $$''$$::json;
12 DETAIL: Token "'" is invalid.
13 CONTEXT: JSON data, line 1: '...
14 SELECT '"abc"'::json; -- OK
20 SELECT '"abc'::json; -- ERROR, quotes not closed
21 ERROR: invalid input syntax for type json
22 LINE 1: SELECT '"abc'::json;
24 DETAIL: Token ""abc" is invalid.
25 CONTEXT: JSON data, line 1: "abc
27 def"'::json; -- 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\"\\"'::json; -- OK, legal escapes
39 SELECT '"\v"'::json; -- ERROR, not a valid JSON escape
40 ERROR: invalid input syntax for type json
41 LINE 1: SELECT '"\v"'::json;
43 DETAIL: Escape sequence "\v" is invalid.
44 CONTEXT: JSON data, line 1: "\v...
45 SELECT '"\u"'::json; -- ERROR, incomplete escape
46 ERROR: invalid input syntax for type json
47 LINE 1: SELECT '"\u"'::json;
49 DETAIL: "\u" must be followed by four hexadecimal digits.
50 CONTEXT: JSON data, line 1: "\u"
51 SELECT '"\u00"'::json; -- ERROR, incomplete escape
52 ERROR: invalid input syntax for type json
53 LINE 1: SELECT '"\u00"'::json;
55 DETAIL: "\u" must be followed by four hexadecimal digits.
56 CONTEXT: JSON data, line 1: "\u00"
57 SELECT '"\u000g"'::json; -- ERROR, g is not a hex digit
58 ERROR: invalid input syntax for type json
59 LINE 1: SELECT '"\u000g"'::json;
61 DETAIL: "\u" must be followed by four hexadecimal digits.
62 CONTEXT: JSON data, line 1: "\u000g...
63 SELECT '"\u0000"'::json; -- OK, legal escape
69 SELECT '"\uaBcD"'::json; -- OK, uppercase and lower case both OK
76 SELECT '1'::json; -- OK
82 SELECT '0'::json; -- OK
88 SELECT '01'::json; -- ERROR, not valid according to JSON spec
89 ERROR: invalid input syntax for type json
90 LINE 1: SELECT '01'::json;
92 DETAIL: Token "01" is invalid.
93 CONTEXT: JSON data, line 1: 01
94 SELECT '0.1'::json; -- OK
100 SELECT '9223372036854775808'::json; -- OK, even though it's too large for int8
102 ---------------------
106 SELECT '1e100'::json; -- OK
112 SELECT '1.3e100'::json; -- OK
118 SELECT '1f2'::json; -- ERROR
119 ERROR: invalid input syntax for type json
120 LINE 1: SELECT '1f2'::json;
122 DETAIL: Token "1f2" is invalid.
123 CONTEXT: JSON data, line 1: 1f2
124 SELECT '0.x1'::json; -- ERROR
125 ERROR: invalid input syntax for type json
126 LINE 1: SELECT '0.x1'::json;
128 DETAIL: Token "0.x1" is invalid.
129 CONTEXT: JSON data, line 1: 0.x1
130 SELECT '1.3ex100'::json; -- ERROR
131 ERROR: invalid input syntax for type json
132 LINE 1: SELECT '1.3ex100'::json;
134 DETAIL: Token "1.3ex100" is invalid.
135 CONTEXT: JSON data, line 1: 1.3ex100
137 SELECT '[]'::json; -- OK
143 SELECT '[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]'::json; -- OK
145 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
146 [[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
149 SELECT '[1,2]'::json; -- OK
155 SELECT '[1,2,]'::json; -- ERROR, trailing comma
156 ERROR: invalid input syntax for type json
157 LINE 1: SELECT '[1,2,]'::json;
159 DETAIL: Expected JSON value, but found "]".
160 CONTEXT: JSON data, line 1: [1,2,]
161 SELECT '[1,2'::json; -- ERROR, no closing bracket
162 ERROR: invalid input syntax for type json
163 LINE 1: SELECT '[1,2'::json;
165 DETAIL: The input string ended unexpectedly.
166 CONTEXT: JSON data, line 1: [1,2
167 SELECT '[1,[2]'::json; -- ERROR, no closing bracket
168 ERROR: invalid input syntax for type json
169 LINE 1: SELECT '[1,[2]'::json;
171 DETAIL: The input string ended unexpectedly.
172 CONTEXT: JSON data, line 1: [1,[2]
174 SELECT '{}'::json; -- OK
180 SELECT '{"abc"}'::json; -- ERROR, no value
181 ERROR: invalid input syntax for type json
182 LINE 1: SELECT '{"abc"}'::json;
184 DETAIL: Expected ":", but found "}".
185 CONTEXT: JSON data, line 1: {"abc"}
186 SELECT '{"abc":1}'::json; -- OK
192 SELECT '{1:"abc"}'::json; -- ERROR, keys must be strings
193 ERROR: invalid input syntax for type json
194 LINE 1: SELECT '{1:"abc"}'::json;
196 DETAIL: Expected string or "}", but found "1".
197 CONTEXT: JSON data, line 1: {1...
198 SELECT '{"abc",1}'::json; -- ERROR, wrong separator
199 ERROR: invalid input syntax for type json
200 LINE 1: SELECT '{"abc",1}'::json;
202 DETAIL: Expected ":", but found ",".
203 CONTEXT: JSON data, line 1: {"abc",...
204 SELECT '{"abc"=1}'::json; -- ERROR, totally wrong separator
205 ERROR: invalid input syntax for type json
206 LINE 1: SELECT '{"abc"=1}'::json;
208 DETAIL: Token "=" is invalid.
209 CONTEXT: JSON data, line 1: {"abc"=...
210 SELECT '{"abc"::1}'::json; -- ERROR, another wrong separator
211 ERROR: invalid input syntax for type json
212 LINE 1: SELECT '{"abc"::1}'::json;
214 DETAIL: Expected JSON value, but found ":".
215 CONTEXT: JSON data, line 1: {"abc"::...
216 SELECT '{"abc":1,"def":2,"ghi":[3,4],"hij":{"klm":5,"nop":[6]}}'::json; -- OK
218 ---------------------------------------------------------
219 {"abc":1,"def":2,"ghi":[3,4],"hij":{"klm":5,"nop":[6]}}
222 SELECT '{"abc":1:2}'::json; -- ERROR, colon in wrong spot
223 ERROR: invalid input syntax for type json
224 LINE 1: SELECT '{"abc":1:2}'::json;
226 DETAIL: Expected "," or "}", but found ":".
227 CONTEXT: JSON data, line 1: {"abc":1:...
228 SELECT '{"abc":1,3}'::json; -- ERROR, no value
229 ERROR: invalid input syntax for type json
230 LINE 1: SELECT '{"abc":1,3}'::json;
232 DETAIL: Expected string, but found "3".
233 CONTEXT: JSON data, line 1: {"abc":1,3...
235 SET max_stack_depth = '100kB';
236 SELECT repeat('[', 10000)::json;
237 ERROR: stack depth limit exceeded
238 HINT: Increase the configuration parameter "max_stack_depth" (currently 100kB), after ensuring the platform's stack depth limit is adequate.
239 SELECT repeat('{"a":', 10000)::json;
240 ERROR: stack depth limit exceeded
241 HINT: Increase the configuration parameter "max_stack_depth" (currently 100kB), after ensuring the platform's stack depth limit is adequate.
242 RESET max_stack_depth;
243 -- Miscellaneous stuff.
244 SELECT 'true'::json; -- OK
250 SELECT 'false'::json; -- OK
256 SELECT 'null'::json; -- OK
262 SELECT ' true '::json; -- OK, even with extra whitespace
268 SELECT 'true false'::json; -- ERROR, too many values
269 ERROR: invalid input syntax for type json
270 LINE 1: SELECT 'true false'::json;
272 DETAIL: Expected end of input, but found "false".
273 CONTEXT: JSON data, line 1: true false
274 SELECT 'true, false'::json; -- ERROR, too many values
275 ERROR: invalid input syntax for type json
276 LINE 1: SELECT 'true, false'::json;
278 DETAIL: Expected end of input, but found ",".
279 CONTEXT: JSON data, line 1: true,...
280 SELECT 'truf'::json; -- ERROR, not a keyword
281 ERROR: invalid input syntax for type json
282 LINE 1: SELECT 'truf'::json;
284 DETAIL: Token "truf" is invalid.
285 CONTEXT: JSON data, line 1: truf
286 SELECT 'trues'::json; -- ERROR, not a keyword
287 ERROR: invalid input syntax for type json
288 LINE 1: SELECT 'trues'::json;
290 DETAIL: Token "trues" is invalid.
291 CONTEXT: JSON data, line 1: trues
292 SELECT ''::json; -- ERROR, no value
293 ERROR: invalid input syntax for type json
294 LINE 1: SELECT ''::json;
296 DETAIL: The input string ended unexpectedly.
297 CONTEXT: JSON data, line 1:
298 SELECT ' '::json; -- ERROR, no value
299 ERROR: invalid input syntax for type json
300 LINE 1: SELECT ' '::json;
302 DETAIL: The input string ended unexpectedly.
303 CONTEXT: JSON data, line 1:
306 SELECT array_to_json(array(select 1 as a));
312 SELECT array_to_json(array_agg(q),false) from (select x as b, x * 2 as c from generate_series(1,3) x) q;
314 ---------------------------------------------
315 [{"b":1,"c":2},{"b":2,"c":4},{"b":3,"c":6}]
318 SELECT array_to_json(array_agg(q),true) from (select x as b, x * 2 as c from generate_series(1,3) x) q;
326 SELECT array_to_json(array_agg(q),false)
327 FROM ( SELECT $$a$$ || x AS b, y AS c,
328 ARRAY[ROW(x.*,ARRAY[1,2,3]),
329 ROW(y.*,ARRAY[4,5,6])] AS z
330 FROM generate_series(1,2) x,
331 generate_series(4,5) y) q;
333 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
334 [{"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]}]}]
337 SELECT array_to_json(array_agg(x),false) from generate_series(5,10) x;
343 SELECT array_to_json('{{1,5},{99,100}}'::int[]);
350 SELECT row_to_json(row(1,'foo'));
352 ---------------------
356 SELECT row_to_json(q)
357 FROM (SELECT $$a$$ || x AS b,
359 ARRAY[ROW(x.*,ARRAY[1,2,3]),
360 ROW(y.*,ARRAY[4,5,6])] AS z
361 FROM generate_series(1,2) x,
362 generate_series(4,5) y) q;
364 --------------------------------------------------------------------
365 {"b":"a1","c":4,"z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}
366 {"b":"a1","c":5,"z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}
367 {"b":"a2","c":4,"z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}
368 {"b":"a2","c":5,"z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}
371 SELECT row_to_json(q,true)
372 FROM (SELECT $$a$$ || x AS b,
374 ARRAY[ROW(x.*,ARRAY[1,2,3]),
375 ROW(y.*,ARRAY[4,5,6])] AS z
376 FROM generate_series(1,2) x,
377 generate_series(4,5) y) q;
379 -----------------------------------------------------
382 "z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}
385 "z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}
388 "z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}
391 "z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}
394 CREATE TEMP TABLE rows AS
395 SELECT x, 'txt' || x as y
396 FROM generate_series(1,3) AS x;
397 SELECT row_to_json(q,true)
409 SELECT row_to_json(row((select array_agg(x) as d from generate_series(5,10) x)),false);
411 -----------------------
412 {"f1":[5,6,7,8,9,10]}
415 -- to_json, timestamps
416 select to_json(timestamp '2014-05-28 12:22:35.614298');
418 ------------------------------
419 "2014-05-28T12:22:35.614298"
423 SET LOCAL TIME ZONE 10.5;
424 select to_json(timestamptz '2014-05-28 12:22:35.614298-04');
426 ------------------------------------
427 "2014-05-29T02:52:35.614298+10:30"
430 SET LOCAL TIME ZONE -8;
431 select to_json(timestamptz '2014-05-28 12:22:35.614298-04');
433 ------------------------------------
434 "2014-05-28T08:22:35.614298-08:00"
438 select to_json(date '2014-05-28');
444 select to_json(date 'Infinity');
450 select to_json(timestamp 'Infinity');
456 select to_json(timestamptz 'Infinity');
464 FROM ( SELECT $$a$$ || x AS b, y AS c,
465 ARRAY[ROW(x.*,ARRAY[1,2,3]),
466 ROW(y.*,ARRAY[4,5,6])] AS z
467 FROM generate_series(1,2) x,
468 generate_series(4,5) y) q;
470 -----------------------------------------------------------------------
471 [{"b":"a1","c":4,"z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}, +
472 {"b":"a1","c":5,"z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}, +
473 {"b":"a2","c":4,"z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}, +
474 {"b":"a2","c":5,"z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}]
477 SELECT json_agg(q ORDER BY x, y)
480 -----------------------
481 [{"x":1,"y":"txt1"}, +
482 {"x":2,"y":"txt2"}, +
486 UPDATE rows SET x = NULL WHERE x = 1;
487 SELECT json_agg(q ORDER BY x NULLS FIRST, y)
490 --------------------------
491 [{"x":null,"y":"txt1"}, +
492 {"x":2,"y":"txt2"}, +
496 -- non-numeric output
497 SELECT row_to_json(q)
498 FROM (SELECT 'NaN'::float8 AS "float8field") q;
500 -----------------------
501 {"float8field":"NaN"}
504 SELECT row_to_json(q)
505 FROM (SELECT 'Infinity'::float8 AS "float8field") q;
507 ----------------------------
508 {"float8field":"Infinity"}
511 SELECT row_to_json(q)
512 FROM (SELECT '-Infinity'::float8 AS "float8field") q;
514 -----------------------------
515 {"float8field":"-Infinity"}
519 SELECT row_to_json(q)
520 FROM (SELECT '{"a":1,"b": [2,3,4,"d","e","f"],"c":{"p":1,"q":2}}'::json AS "jsonfield") q;
522 ------------------------------------------------------------------
523 {"jsonfield":{"a":1,"b": [2,3,4,"d","e","f"],"c":{"p":1,"q":2}}}
526 -- json extraction functions
527 CREATE TEMP TABLE test_json (
531 INSERT INTO test_json VALUES
532 ('scalar','"a scalar"'),
533 ('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'),
534 ('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}');
535 SELECT test_json -> 'x'
537 WHERE json_type = 'scalar';
543 SELECT test_json -> 'x'
545 WHERE json_type = 'array';
551 SELECT test_json -> 'x'
553 WHERE json_type = 'object';
559 SELECT test_json->'field2'
561 WHERE json_type = 'object';
567 SELECT test_json->>'field2'
569 WHERE json_type = 'object';
575 SELECT test_json -> 2
577 WHERE json_type = 'scalar';
583 SELECT test_json -> 2
585 WHERE json_type = 'array';
591 SELECT test_json -> -1
593 WHERE json_type = 'array';
599 SELECT test_json -> 2
601 WHERE json_type = 'object';
609 WHERE json_type = 'array';
615 SELECT test_json ->> 6 FROM test_json WHERE json_type = 'array';
621 SELECT test_json ->> 7 FROM test_json WHERE json_type = 'array';
627 SELECT test_json ->> 'field4' FROM test_json WHERE json_type = 'object';
633 SELECT test_json ->> 'field5' FROM test_json WHERE json_type = 'object';
639 SELECT test_json ->> 'field6' FROM test_json WHERE json_type = 'object';
645 SELECT json_object_keys(test_json)
647 WHERE json_type = 'scalar';
648 ERROR: cannot call json_object_keys on a scalar
649 SELECT json_object_keys(test_json)
651 WHERE json_type = 'array';
652 ERROR: cannot call json_object_keys on an array
653 SELECT json_object_keys(test_json)
655 WHERE json_type = 'object';
666 -- test extending object_keys resultset - initial resultset size is 256
668 (select json_object_keys(json_object(array_agg(g)))
669 from (select unnest(array['f'||n,n::text])as g
670 from generate_series(1,300) as n) x ) y;
677 select (test_json->'field3') is null as expect_false
679 where json_type = 'object';
685 select (test_json->>'field3') is null as expect_true
687 where json_type = 'object';
693 select (test_json->3) is null as expect_false
695 where json_type = 'array';
701 select (test_json->>3) is null as expect_true
703 where json_type = 'array';
710 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> null::text;
716 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> null::int;
722 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 1;
728 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> -1;
734 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 'z';
740 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> '';
746 select '[{"b": "c"}, {"b": "cc"}]'::json -> 1;
752 select '[{"b": "c"}, {"b": "cc"}]'::json -> 3;
758 select '[{"b": "c"}, {"b": "cc"}]'::json -> 'z';
764 select '{"a": "c", "b": null}'::json -> 'b';
770 select '"foo"'::json -> 1;
776 select '"foo"'::json -> 'z';
782 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> null::text;
788 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> null::int;
794 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> 1;
800 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> 'z';
806 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> '';
812 select '[{"b": "c"}, {"b": "cc"}]'::json ->> 1;
818 select '[{"b": "c"}, {"b": "cc"}]'::json ->> 3;
824 select '[{"b": "c"}, {"b": "cc"}]'::json ->> 'z';
830 select '{"a": "c", "b": null}'::json ->> 'b';
836 select '"foo"'::json ->> 1;
842 select '"foo"'::json ->> 'z';
849 SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
855 SELECT json_array_length('[]');
861 SELECT json_array_length('{"f1":1,"f2":[5,6]}');
862 ERROR: cannot get array length of a non-array
863 SELECT json_array_length('4');
864 ERROR: cannot get array length of a scalar
866 select json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}');
874 select * from json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q;
884 select json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":"null"}');
893 select * from json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q;
903 -- extract_path, extract_path_as_text
904 select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6');
910 select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2');
916 select json_extract_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',0::text);
922 select json_extract_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',1::text);
928 select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6');
929 json_extract_path_text
930 ------------------------
934 select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2');
935 json_extract_path_text
936 ------------------------
940 select json_extract_path_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',0::text);
941 json_extract_path_text
942 ------------------------
946 select json_extract_path_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',1::text);
947 json_extract_path_text
948 ------------------------
952 -- extract_path nulls
953 select json_extract_path('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') is null as expect_false;
959 select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') is null as expect_true;
965 select json_extract_path('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') is null as expect_false;
971 select json_extract_path_text('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') is null as expect_true;
977 -- extract_path operators
978 select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f4','f6'];
984 select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f2'];
990 select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f2','0'];
996 select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f2','1'];
1002 select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f4','f6'];
1008 select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2'];
1014 select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2','0'];
1020 select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2','1'];
1026 -- corner cases for same
1027 select '{"a": {"b":{"c": "foo"}}}'::json #> '{}';
1029 ---------------------------
1030 {"a": {"b":{"c": "foo"}}}
1033 select '[1,2,3]'::json #> '{}';
1039 select '"foo"'::json #> '{}';
1045 select '42'::json #> '{}';
1051 select 'null'::json #> '{}';
1057 select '{"a": {"b":{"c": "foo"}}}'::json #> array['a'];
1059 --------------------
1063 select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', null];
1069 select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', ''];
1075 select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b'];
1081 select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b','c'];
1087 select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b','c','d'];
1093 select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','z','c'];
1099 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #> array['a','1','b'];
1105 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #> array['a','z','b'];
1111 select '[{"b": "c"}, {"b": "cc"}]'::json #> array['1','b'];
1117 select '[{"b": "c"}, {"b": "cc"}]'::json #> array['z','b'];
1123 select '[{"b": "c"}, {"b": null}]'::json #> array['1','b'];
1129 select '"foo"'::json #> array['z'];
1135 select '42'::json #> array['f2'];
1141 select '42'::json #> array['0'];
1147 select '{"a": {"b":{"c": "foo"}}}'::json #>> '{}';
1149 ---------------------------
1150 {"a": {"b":{"c": "foo"}}}
1153 select '[1,2,3]'::json #>> '{}';
1159 select '"foo"'::json #>> '{}';
1165 select '42'::json #>> '{}';
1171 select 'null'::json #>> '{}';
1177 select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a'];
1179 --------------------
1183 select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', null];
1189 select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', ''];
1195 select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b'];
1201 select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b','c'];
1207 select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b','c','d'];
1213 select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','z','c'];
1219 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #>> array['a','1','b'];
1225 select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #>> array['a','z','b'];
1231 select '[{"b": "c"}, {"b": "cc"}]'::json #>> array['1','b'];
1237 select '[{"b": "c"}, {"b": "cc"}]'::json #>> array['z','b'];
1243 select '[{"b": "c"}, {"b": null}]'::json #>> array['1','b'];
1249 select '"foo"'::json #>> array['z'];
1255 select '42'::json #>> array['f2'];
1261 select '42'::json #>> array['0'];
1268 select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]');
1270 -----------------------
1275 {"f1":1,"f2":[7,8,9]}
1280 select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q;
1282 -----------------------
1287 {"f1":1,"f2":[7,8,9]}
1292 select json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]');
1293 json_array_elements_text
1294 --------------------------
1299 {"f1":1,"f2":[7,8,9]}
1304 select * from json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q;
1306 -----------------------
1311 {"f1":1,"f2":[7,8,9]}
1317 create type jpop as (a text, b int, c timestamp);
1318 select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q;
1324 select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q;
1326 --------+---+--------------------------
1327 blurfl | 3 | Mon Dec 31 15:30:56 2012
1330 select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q;
1336 select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q;
1338 --------+---+--------------------------
1339 blurfl | 3 | Mon Dec 31 15:30:56 2012
1342 select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}') q;
1344 -----------------+---+---
1348 select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}') q;
1350 -----------------+---+--------------------------
1351 [100,200,false] | 3 | Mon Dec 31 15:30:56 2012
1354 select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}') q;
1355 ERROR: invalid input syntax for type timestamp: "[100,200,false]"
1356 -- populate_recordset
1357 select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
1359 --------+---+--------------------------
1361 | 3 | Fri Jan 20 10:42:53 2012
1364 select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
1366 --------+----+--------------------------
1368 def | 3 | Fri Jan 20 10:42:53 2012
1371 select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
1373 --------+---+--------------------------
1375 | 3 | Fri Jan 20 10:42:53 2012
1378 select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
1380 --------+----+--------------------------
1382 def | 3 | Fri Jan 20 10:42:53 2012
1385 select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q;
1387 ---------------+----+--------------------------
1388 [100,200,300] | 99 |
1389 {"z":true} | 3 | Fri Jan 20 10:42:53 2012
1392 select * from json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q;
1393 ERROR: invalid input syntax for type timestamp: "[100,200,300]"
1394 create type jpop2 as (a int, b json, c int, d int);
1395 select * from json_populate_recordset(null::jpop2, '[{"a":2,"c":3,"b":{"z":4},"d":6}]') q;
1397 ---+---------+---+---
1401 select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
1403 --------+---+--------------------------
1405 | 3 | Fri Jan 20 10:42:53 2012
1408 select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
1410 --------+----+--------------------------
1412 def | 3 | Fri Jan 20 10:42:53 2012
1415 select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q;
1417 ---------------+----+--------------------------
1418 [100,200,300] | 99 |
1419 {"z":true} | 3 | Fri Jan 20 10:42:53 2012
1422 -- handling of unicode surrogate pairs
1423 select json '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a' as correct_in_utf8;
1425 ----------------------------
1426 "\ud83d\ude04\ud83d\udc36"
1429 select json '{ "a": "\ud83d\ud83d" }' -> 'a'; -- 2 high surrogates in a row
1430 ERROR: invalid input syntax for type json
1431 DETAIL: Unicode high surrogate must not follow a high surrogate.
1432 CONTEXT: JSON data, line 1: { "a":...
1433 select json '{ "a": "\ude04\ud83d" }' -> 'a'; -- surrogates in wrong order
1434 ERROR: invalid input syntax for type json
1435 DETAIL: Unicode low surrogate must follow a high surrogate.
1436 CONTEXT: JSON data, line 1: { "a":...
1437 select json '{ "a": "\ud83dX" }' -> 'a'; -- orphan high surrogate
1438 ERROR: invalid input syntax for type json
1439 DETAIL: Unicode low surrogate must follow a high surrogate.
1440 CONTEXT: JSON data, line 1: { "a":...
1441 select json '{ "a": "\ude04X" }' -> 'a'; -- orphan low surrogate
1442 ERROR: invalid input syntax for type json
1443 DETAIL: Unicode low surrogate must follow a high surrogate.
1444 CONTEXT: JSON data, line 1: { "a":...
1445 --handling of simple unicode escapes
1446 select json '{ "a": "the Copyright \u00a9 sign" }' as correct_in_utf8;
1448 ---------------------------------------
1449 { "a": "the Copyright \u00a9 sign" }
1452 select json '{ "a": "dollar \u0024 character" }' as correct_everywhere;
1454 -------------------------------------
1455 { "a": "dollar \u0024 character" }
1458 select json '{ "a": "dollar \\u0024 character" }' as not_an_escape;
1460 --------------------------------------
1461 { "a": "dollar \\u0024 character" }
1464 select json '{ "a": "null \u0000 escape" }' as not_unescaped;
1466 --------------------------------
1467 { "a": "null \u0000 escape" }
1470 select json '{ "a": "null \\u0000 escape" }' as not_an_escape;
1472 ---------------------------------
1473 { "a": "null \\u0000 escape" }
1476 select json '{ "a": "the Copyright \u00a9 sign" }' ->> 'a' as correct_in_utf8;
1478 ----------------------
1479 the Copyright © sign
1482 select json '{ "a": "dollar \u0024 character" }' ->> 'a' as correct_everywhere;
1484 --------------------
1488 select json '{ "a": "dollar \\u0024 character" }' ->> 'a' as not_an_escape;
1490 -------------------------
1491 dollar \u0024 character
1494 select json '{ "a": "null \u0000 escape" }' ->> 'a' as fails;
1495 ERROR: unsupported Unicode escape sequence
1496 DETAIL: \u0000 cannot be converted to text.
1497 CONTEXT: JSON data, line 1: { "a":...
1498 select json '{ "a": "null \\u0000 escape" }' ->> 'a' as not_an_escape;
1500 --------------------
1504 --json_typeof() function
1505 select value, json_typeof(value)
1506 from (values (json '123.4'),
1514 (json '{"x":"foo", "y":123}'),
1519 ----------------------+-------------
1528 {"x":"foo", "y":123} | object
1533 -- json_build_array, json_build_object, json_object_agg
1534 SELECT json_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}');
1536 -----------------------------------------------------------------------
1537 ["a", 1, "b", 1.2, "c", true, "d", null, "e", {"x": 3, "y": [1,2,3]}]
1540 SELECT json_build_object('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}');
1542 ----------------------------------------------------------------------------
1543 {"a" : 1, "b" : 1.2, "c" : true, "d" : null, "e" : {"x": 3, "y": [1,2,3]}}
1546 SELECT json_build_object(
1547 'a', json_build_object('b',false,'c',99),
1548 'd', json_build_object('e',array[9,8,7]::int[],
1549 'f', (select row_to_json(r) from ( select relkind, oid::regclass as name from pg_class where relname = 'pg_class') r)));
1551 -------------------------------------------------------------------------------------------------
1552 {"a" : {"b" : false, "c" : 99}, "d" : {"e" : [9,8,7], "f" : {"relkind":"r","name":"pg_class"}}}
1555 -- empty objects/arrays
1556 SELECT json_build_array();
1562 SELECT json_build_object();
1568 -- make sure keys are quoted
1569 SELECT json_build_object(1,2);
1575 -- keys must be scalar and not null
1576 SELECT json_build_object(null,2);
1577 ERROR: argument 1 cannot be null
1578 HINT: Object keys should be text.
1579 SELECT json_build_object(r,2) FROM (SELECT 1 AS a, 2 AS b) r;
1580 ERROR: key value must be scalar, not array, composite, or json
1581 SELECT json_build_object(json '{"a":1,"b":2}', 3);
1582 ERROR: key value must be scalar, not array, composite, or json
1583 SELECT json_build_object('{1,2,3}'::int[], 3);
1584 ERROR: key value must be scalar, not array, composite, or json
1585 CREATE TEMP TABLE foo (serial_num int, name text, type text);
1586 INSERT INTO foo VALUES (847001,'t15','GE1043');
1587 INSERT INTO foo VALUES (847002,'t16','GE1043');
1588 INSERT INTO foo VALUES (847003,'sub-alpha','GESS90');
1589 SELECT json_build_object('turbines',json_object_agg(serial_num,json_build_object('name',name,'type',type)))
1592 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1593 {"turbines" : { "847001" : {"name" : "t15", "type" : "GE1043"}, "847002" : {"name" : "t16", "type" : "GE1043"}, "847003" : {"name" : "sub-alpha", "type" : "GESS90"} }}
1596 SELECT json_object_agg(name, type) FROM foo;
1598 ----------------------------------------------------------------
1599 { "t15" : "GE1043", "t16" : "GE1043", "sub-alpha" : "GESS90" }
1602 INSERT INTO foo VALUES (999999, NULL, 'bar');
1603 SELECT json_object_agg(name, type) FROM foo;
1604 ERROR: field name must not be null
1607 SELECT json_object('{a,1,b,2,3,NULL,"d e f","a b c"}');
1609 -------------------------------------------------------
1610 {"a" : "1", "b" : "2", "3" : null, "d e f" : "a b c"}
1613 -- same but with two dimensions
1614 SELECT json_object('{{a,1},{b,2},{3,NULL},{"d e f","a b c"}}');
1616 -------------------------------------------------------
1617 {"a" : "1", "b" : "2", "3" : null, "d e f" : "a b c"}
1621 SELECT json_object('{a,b,c}');
1622 ERROR: array must have even number of elements
1624 SELECT json_object('{{a},{b}}');
1625 ERROR: array must have two columns
1626 -- too many columns error
1627 SELECT json_object('{{a,b,c},{b,c,d}}');
1628 ERROR: array must have two columns
1629 -- too many dimensions error
1630 SELECT json_object('{{{a,b},{c,d}},{{b,c},{d,e}}}');
1631 ERROR: wrong number of array subscripts
1632 --two argument form of json_object
1633 select json_object('{a,b,c,"d e f"}','{1,2,3,"a b c"}');
1635 ------------------------------------------------------
1636 {"a" : "1", "b" : "2", "c" : "3", "d e f" : "a b c"}
1639 -- too many dimensions
1640 SELECT json_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"}}');
1641 ERROR: wrong number of array subscripts
1642 -- mismatched dimensions
1643 select json_object('{a,b,c,"d e f",g}','{1,2,3,"a b c"}');
1644 ERROR: mismatched array dimensions
1645 select json_object('{a,b,c,"d e f"}','{1,2,3,"a b c",g}');
1646 ERROR: mismatched array dimensions
1648 select json_object('{a,b,NULL,"d e f"}','{1,2,3,"a b c"}');
1649 ERROR: null value not allowed for object key
1650 -- empty key is allowed
1651 select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}');
1653 -----------------------------------------------------
1654 {"a" : "1", "b" : "2", "" : "3", "d e f" : "a b c"}
1657 -- json_to_record and json_to_recordset
1658 select * from json_to_record('{"a":1,"b":"foo","c":"bar"}')
1659 as x(a int, b text, d text);
1665 select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]')
1666 as x(a int, b text, c boolean);
1673 select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]')
1674 as x(a int, b json, c boolean);
1676 ---+-------------+---
1682 select json_strip_nulls(null);
1688 select json_strip_nulls('1');
1694 select json_strip_nulls('"a string"');
1700 select json_strip_nulls('null');
1706 select json_strip_nulls('[1,2,null,3,4]');
1712 select json_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}');
1714 ------------------------------------
1715 {"a":1,"c":[2,null,3],"d":{"e":4}}
1718 select json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');
1720 ---------------------
1724 -- an empty object is not null and should not be stripped
1725 select json_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');