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...
234 -- Miscellaneous stuff.
235 SELECT 'true'::json; -- OK
241 SELECT 'false'::json; -- OK
247 SELECT 'null'::json; -- OK
253 SELECT ' true '::json; -- OK, even with extra whitespace
259 SELECT 'true false'::json; -- ERROR, too many values
260 ERROR: invalid input syntax for type json
261 LINE 1: SELECT 'true false'::json;
263 DETAIL: Expected end of input, but found "false".
264 CONTEXT: JSON data, line 1: true false
265 SELECT 'true, false'::json; -- ERROR, too many values
266 ERROR: invalid input syntax for type json
267 LINE 1: SELECT 'true, false'::json;
269 DETAIL: Expected end of input, but found ",".
270 CONTEXT: JSON data, line 1: true,...
271 SELECT 'truf'::json; -- ERROR, not a keyword
272 ERROR: invalid input syntax for type json
273 LINE 1: SELECT 'truf'::json;
275 DETAIL: Token "truf" is invalid.
276 CONTEXT: JSON data, line 1: truf
277 SELECT 'trues'::json; -- ERROR, not a keyword
278 ERROR: invalid input syntax for type json
279 LINE 1: SELECT 'trues'::json;
281 DETAIL: Token "trues" is invalid.
282 CONTEXT: JSON data, line 1: trues
283 SELECT ''::json; -- ERROR, no value
284 ERROR: invalid input syntax for type json
285 LINE 1: SELECT ''::json;
287 DETAIL: The input string ended unexpectedly.
288 CONTEXT: JSON data, line 1:
289 SELECT ' '::json; -- ERROR, no value
290 ERROR: invalid input syntax for type json
291 LINE 1: SELECT ' '::json;
293 DETAIL: The input string ended unexpectedly.
294 CONTEXT: JSON data, line 1:
297 SELECT array_to_json(array(select 1 as a));
303 SELECT array_to_json(array_agg(q),false) from (select x as b, x * 2 as c from generate_series(1,3) x) q;
305 ---------------------------------------------
306 [{"b":1,"c":2},{"b":2,"c":4},{"b":3,"c":6}]
309 SELECT array_to_json(array_agg(q),true) from (select x as b, x * 2 as c from generate_series(1,3) x) q;
317 SELECT array_to_json(array_agg(q),false)
318 FROM ( SELECT $$a$$ || x AS b, y AS c,
319 ARRAY[ROW(x.*,ARRAY[1,2,3]),
320 ROW(y.*,ARRAY[4,5,6])] AS z
321 FROM generate_series(1,2) x,
322 generate_series(4,5) y) q;
324 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
325 [{"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]}]}]
328 SELECT array_to_json(array_agg(x),false) from generate_series(5,10) x;
334 SELECT array_to_json('{{1,5},{99,100}}'::int[]);
341 SELECT row_to_json(row(1,'foo'));
343 ---------------------
347 SELECT row_to_json(q)
348 FROM (SELECT $$a$$ || x AS b,
350 ARRAY[ROW(x.*,ARRAY[1,2,3]),
351 ROW(y.*,ARRAY[4,5,6])] AS z
352 FROM generate_series(1,2) x,
353 generate_series(4,5) y) q;
355 --------------------------------------------------------------------
356 {"b":"a1","c":4,"z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}
357 {"b":"a1","c":5,"z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}
358 {"b":"a2","c":4,"z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}
359 {"b":"a2","c":5,"z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}
362 SELECT row_to_json(q,true)
363 FROM (SELECT $$a$$ || x AS b,
365 ARRAY[ROW(x.*,ARRAY[1,2,3]),
366 ROW(y.*,ARRAY[4,5,6])] AS z
367 FROM generate_series(1,2) x,
368 generate_series(4,5) y) q;
370 -----------------------------------------------------
373 "z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}
376 "z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}
379 "z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}
382 "z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}
385 CREATE TEMP TABLE rows AS
386 SELECT x, 'txt' || x as y
387 FROM generate_series(1,3) AS x;
388 SELECT row_to_json(q,true)
400 SELECT row_to_json(row((select array_agg(x) as d from generate_series(5,10) x)),false);
402 -----------------------
403 {"f1":[5,6,7,8,9,10]}
408 FROM ( SELECT $$a$$ || x AS b, y AS c,
409 ARRAY[ROW(x.*,ARRAY[1,2,3]),
410 ROW(y.*,ARRAY[4,5,6])] AS z
411 FROM generate_series(1,2) x,
412 generate_series(4,5) y) q;
414 -----------------------------------------------------------------------
415 [{"b":"a1","c":4,"z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}, +
416 {"b":"a1","c":5,"z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}, +
417 {"b":"a2","c":4,"z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}, +
418 {"b":"a2","c":5,"z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}]
424 -----------------------
425 [{"x":1,"y":"txt1"}, +
426 {"x":2,"y":"txt2"}, +
430 -- non-numeric output
431 SELECT row_to_json(q)
432 FROM (SELECT 'NaN'::float8 AS "float8field") q;
434 -----------------------
435 {"float8field":"NaN"}
438 SELECT row_to_json(q)
439 FROM (SELECT 'Infinity'::float8 AS "float8field") q;
441 ----------------------------
442 {"float8field":"Infinity"}
445 SELECT row_to_json(q)
446 FROM (SELECT '-Infinity'::float8 AS "float8field") q;
448 -----------------------------
449 {"float8field":"-Infinity"}
453 SELECT row_to_json(q)
454 FROM (SELECT '{"a":1,"b": [2,3,4,"d","e","f"],"c":{"p":1,"q":2}}'::json AS "jsonfield") q;
456 ------------------------------------------------------------------
457 {"jsonfield":{"a":1,"b": [2,3,4,"d","e","f"],"c":{"p":1,"q":2}}}