1-- Strings. 2SELECT '""'::json; -- OK. 3SELECT $$''$$::json; -- ERROR, single quotes are not allowed 4SELECT '"abc"'::json; -- OK 5SELECT '"abc'::json; -- ERROR, quotes not closed 6SELECT '"abc 7def"'::json; -- ERROR, unescaped newline in string constant 8SELECT '"\n\"\\"'::json; -- OK, legal escapes 9SELECT '"\v"'::json; -- ERROR, not a valid JSON escape 10-- see json_encoding test for input with unicode escapes 11 12-- Numbers. 13SELECT '1'::json; -- OK 14SELECT '0'::json; -- OK 15SELECT '01'::json; -- ERROR, not valid according to JSON spec 16SELECT '0.1'::json; -- OK 17SELECT '9223372036854775808'::json; -- OK, even though it's too large for int8 18SELECT '1e100'::json; -- OK 19SELECT '1.3e100'::json; -- OK 20SELECT '1f2'::json; -- ERROR 21SELECT '0.x1'::json; -- ERROR 22SELECT '1.3ex100'::json; -- ERROR 23 24-- Arrays. 25SELECT '[]'::json; -- OK 26SELECT '[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]'::json; -- OK 27SELECT '[1,2]'::json; -- OK 28SELECT '[1,2,]'::json; -- ERROR, trailing comma 29SELECT '[1,2'::json; -- ERROR, no closing bracket 30SELECT '[1,[2]'::json; -- ERROR, no closing bracket 31 32-- Objects. 33SELECT '{}'::json; -- OK 34SELECT '{"abc"}'::json; -- ERROR, no value 35SELECT '{"abc":1}'::json; -- OK 36SELECT '{1:"abc"}'::json; -- ERROR, keys must be strings 37SELECT '{"abc",1}'::json; -- ERROR, wrong separator 38SELECT '{"abc"=1}'::json; -- ERROR, totally wrong separator 39SELECT '{"abc"::1}'::json; -- ERROR, another wrong separator 40SELECT '{"abc":1,"def":2,"ghi":[3,4],"hij":{"klm":5,"nop":[6]}}'::json; -- OK 41SELECT '{"abc":1:2}'::json; -- ERROR, colon in wrong spot 42SELECT '{"abc":1,3}'::json; -- ERROR, no value 43 44-- Recursion. 45SET max_stack_depth = '100kB'; 46SELECT repeat('[', 10000)::json; 47SELECT repeat('{"a":', 10000)::json; 48RESET max_stack_depth; 49 50-- Miscellaneous stuff. 51SELECT 'true'::json; -- OK 52SELECT 'false'::json; -- OK 53SELECT 'null'::json; -- OK 54SELECT ' true '::json; -- OK, even with extra whitespace 55SELECT 'true false'::json; -- ERROR, too many values 56SELECT 'true, false'::json; -- ERROR, too many values 57SELECT 'truf'::json; -- ERROR, not a keyword 58SELECT 'trues'::json; -- ERROR, not a keyword 59SELECT ''::json; -- ERROR, no value 60SELECT ' '::json; -- ERROR, no value 61 62--constructors 63-- array_to_json 64 65SELECT array_to_json(array(select 1 as a)); 66SELECT array_to_json(array_agg(q),false) from (select x as b, x * 2 as c from generate_series(1,3) x) q; 67SELECT array_to_json(array_agg(q),true) from (select x as b, x * 2 as c from generate_series(1,3) x) q; 68SELECT array_to_json(array_agg(q),false) 69 FROM ( SELECT $$a$$ || x AS b, y AS c, 70 ARRAY[ROW(x.*,ARRAY[1,2,3]), 71 ROW(y.*,ARRAY[4,5,6])] AS z 72 FROM generate_series(1,2) x, 73 generate_series(4,5) y) q; 74SELECT array_to_json(array_agg(x),false) from generate_series(5,10) x; 75SELECT array_to_json('{{1,5},{99,100}}'::int[]); 76 77-- row_to_json 78SELECT row_to_json(row(1,'foo')); 79 80SELECT row_to_json(q) 81FROM (SELECT $$a$$ || x AS b, 82 y AS c, 83 ARRAY[ROW(x.*,ARRAY[1,2,3]), 84 ROW(y.*,ARRAY[4,5,6])] AS z 85 FROM generate_series(1,2) x, 86 generate_series(4,5) y) q; 87 88SELECT row_to_json(q,true) 89FROM (SELECT $$a$$ || x AS b, 90 y AS c, 91 ARRAY[ROW(x.*,ARRAY[1,2,3]), 92 ROW(y.*,ARRAY[4,5,6])] AS z 93 FROM generate_series(1,2) x, 94 generate_series(4,5) y) q; 95 96CREATE TEMP TABLE rows AS 97SELECT x, 'txt' || x as y 98FROM generate_series(1,3) AS x; 99 100SELECT row_to_json(q,true) 101FROM rows q; 102 103SELECT row_to_json(row((select array_agg(x) as d from generate_series(5,10) x)),false); 104 105-- to_json, timestamps 106 107select to_json(timestamp '2014-05-28 12:22:35.614298'); 108 109BEGIN; 110SET LOCAL TIME ZONE 10.5; 111select to_json(timestamptz '2014-05-28 12:22:35.614298-04'); 112SET LOCAL TIME ZONE -8; 113select to_json(timestamptz '2014-05-28 12:22:35.614298-04'); 114COMMIT; 115 116select to_json(date '2014-05-28'); 117 118select to_json(date 'Infinity'); 119select to_json(date '-Infinity'); 120select to_json(timestamp 'Infinity'); 121select to_json(timestamp '-Infinity'); 122select to_json(timestamptz 'Infinity'); 123select to_json(timestamptz '-Infinity'); 124 125--json_agg 126 127SELECT json_agg(q) 128 FROM ( SELECT $$a$$ || x AS b, y AS c, 129 ARRAY[ROW(x.*,ARRAY[1,2,3]), 130 ROW(y.*,ARRAY[4,5,6])] AS z 131 FROM generate_series(1,2) x, 132 generate_series(4,5) y) q; 133 134SELECT json_agg(q ORDER BY x, y) 135 FROM rows q; 136 137UPDATE rows SET x = NULL WHERE x = 1; 138 139SELECT json_agg(q ORDER BY x NULLS FIRST, y) 140 FROM rows q; 141 142-- non-numeric output 143SELECT row_to_json(q) 144FROM (SELECT 'NaN'::float8 AS "float8field") q; 145 146SELECT row_to_json(q) 147FROM (SELECT 'Infinity'::float8 AS "float8field") q; 148 149SELECT row_to_json(q) 150FROM (SELECT '-Infinity'::float8 AS "float8field") q; 151 152-- json input 153SELECT row_to_json(q) 154FROM (SELECT '{"a":1,"b": [2,3,4,"d","e","f"],"c":{"p":1,"q":2}}'::json AS "jsonfield") q; 155 156 157-- json extraction functions 158 159CREATE TEMP TABLE test_json ( 160 json_type text, 161 test_json json 162); 163 164INSERT INTO test_json VALUES 165('scalar','"a scalar"'), 166('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'), 167('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}'); 168 169SELECT test_json -> 'x' 170FROM test_json 171WHERE json_type = 'scalar'; 172 173SELECT test_json -> 'x' 174FROM test_json 175WHERE json_type = 'array'; 176 177SELECT test_json -> 'x' 178FROM test_json 179WHERE json_type = 'object'; 180 181SELECT test_json->'field2' 182FROM test_json 183WHERE json_type = 'object'; 184 185SELECT test_json->>'field2' 186FROM test_json 187WHERE json_type = 'object'; 188 189SELECT test_json -> 2 190FROM test_json 191WHERE json_type = 'scalar'; 192 193SELECT test_json -> 2 194FROM test_json 195WHERE json_type = 'array'; 196 197SELECT test_json -> -1 198FROM test_json 199WHERE json_type = 'array'; 200 201SELECT test_json -> 2 202FROM test_json 203WHERE json_type = 'object'; 204 205SELECT test_json->>2 206FROM test_json 207WHERE json_type = 'array'; 208 209SELECT test_json ->> 6 FROM test_json WHERE json_type = 'array'; 210SELECT test_json ->> 7 FROM test_json WHERE json_type = 'array'; 211 212SELECT test_json ->> 'field4' FROM test_json WHERE json_type = 'object'; 213SELECT test_json ->> 'field5' FROM test_json WHERE json_type = 'object'; 214SELECT test_json ->> 'field6' FROM test_json WHERE json_type = 'object'; 215 216SELECT json_object_keys(test_json) 217FROM test_json 218WHERE json_type = 'scalar'; 219 220SELECT json_object_keys(test_json) 221FROM test_json 222WHERE json_type = 'array'; 223 224SELECT json_object_keys(test_json) 225FROM test_json 226WHERE json_type = 'object'; 227 228-- test extending object_keys resultset - initial resultset size is 256 229 230select count(*) from 231 (select json_object_keys(json_object(array_agg(g))) 232 from (select unnest(array['f'||n,n::text])as g 233 from generate_series(1,300) as n) x ) y; 234 235-- nulls 236 237select (test_json->'field3') is null as expect_false 238from test_json 239where json_type = 'object'; 240 241select (test_json->>'field3') is null as expect_true 242from test_json 243where json_type = 'object'; 244 245select (test_json->3) is null as expect_false 246from test_json 247where json_type = 'array'; 248 249select (test_json->>3) is null as expect_true 250from test_json 251where json_type = 'array'; 252 253-- corner cases 254 255select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> null::text; 256select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> null::int; 257select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 1; 258select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> -1; 259select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 'z'; 260select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> ''; 261select '[{"b": "c"}, {"b": "cc"}]'::json -> 1; 262select '[{"b": "c"}, {"b": "cc"}]'::json -> 3; 263select '[{"b": "c"}, {"b": "cc"}]'::json -> 'z'; 264select '{"a": "c", "b": null}'::json -> 'b'; 265select '"foo"'::json -> 1; 266select '"foo"'::json -> 'z'; 267 268select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> null::text; 269select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> null::int; 270select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> 1; 271select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> 'z'; 272select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> ''; 273select '[{"b": "c"}, {"b": "cc"}]'::json ->> 1; 274select '[{"b": "c"}, {"b": "cc"}]'::json ->> 3; 275select '[{"b": "c"}, {"b": "cc"}]'::json ->> 'z'; 276select '{"a": "c", "b": null}'::json ->> 'b'; 277select '"foo"'::json ->> 1; 278select '"foo"'::json ->> 'z'; 279 280-- array length 281 282SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]'); 283 284SELECT json_array_length('[]'); 285 286SELECT json_array_length('{"f1":1,"f2":[5,6]}'); 287 288SELECT json_array_length('4'); 289 290-- each 291 292select json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}'); 293select * from json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q; 294 295select json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":"null"}'); 296select * from json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q; 297 298-- extract_path, extract_path_as_text 299 300select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6'); 301select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2'); 302select json_extract_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',0::text); 303select json_extract_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',1::text); 304select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6'); 305select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2'); 306select json_extract_path_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',0::text); 307select json_extract_path_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',1::text); 308 309-- extract_path nulls 310 311select json_extract_path('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') is null as expect_false; 312select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') is null as expect_true; 313select json_extract_path('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') is null as expect_false; 314select json_extract_path_text('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') is null as expect_true; 315 316-- extract_path operators 317 318select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f4','f6']; 319select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f2']; 320select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f2','0']; 321select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f2','1']; 322 323select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f4','f6']; 324select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2']; 325select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2','0']; 326select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2','1']; 327 328-- corner cases for same 329select '{"a": {"b":{"c": "foo"}}}'::json #> '{}'; 330select '[1,2,3]'::json #> '{}'; 331select '"foo"'::json #> '{}'; 332select '42'::json #> '{}'; 333select 'null'::json #> '{}'; 334select '{"a": {"b":{"c": "foo"}}}'::json #> array['a']; 335select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', null]; 336select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', '']; 337select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b']; 338select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b','c']; 339select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b','c','d']; 340select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','z','c']; 341select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #> array['a','1','b']; 342select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #> array['a','z','b']; 343select '[{"b": "c"}, {"b": "cc"}]'::json #> array['1','b']; 344select '[{"b": "c"}, {"b": "cc"}]'::json #> array['z','b']; 345select '[{"b": "c"}, {"b": null}]'::json #> array['1','b']; 346select '"foo"'::json #> array['z']; 347select '42'::json #> array['f2']; 348select '42'::json #> array['0']; 349 350select '{"a": {"b":{"c": "foo"}}}'::json #>> '{}'; 351select '[1,2,3]'::json #>> '{}'; 352select '"foo"'::json #>> '{}'; 353select '42'::json #>> '{}'; 354select 'null'::json #>> '{}'; 355select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a']; 356select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', null]; 357select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', '']; 358select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b']; 359select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b','c']; 360select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b','c','d']; 361select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','z','c']; 362select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #>> array['a','1','b']; 363select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #>> array['a','z','b']; 364select '[{"b": "c"}, {"b": "cc"}]'::json #>> array['1','b']; 365select '[{"b": "c"}, {"b": "cc"}]'::json #>> array['z','b']; 366select '[{"b": "c"}, {"b": null}]'::json #>> array['1','b']; 367select '"foo"'::json #>> array['z']; 368select '42'::json #>> array['f2']; 369select '42'::json #>> array['0']; 370 371-- array_elements 372 373select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]'); 374select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q; 375select json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]'); 376select * from json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q; 377 378-- populate_record 379create type jpop as (a text, b int, c timestamp); 380 381select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q; 382select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q; 383 384select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q; 385select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q; 386 387select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}') q; 388select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}') q; 389select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}') q; 390 391-- populate_recordset 392 393select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 394select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 395select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 396select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 397select * 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; 398select * 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; 399 400create type jpop2 as (a int, b json, c int, d int); 401select * from json_populate_recordset(null::jpop2, '[{"a":2,"c":3,"b":{"z":4},"d":6}]') q; 402 403select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 404select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 405select * 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; 406 407-- negative cases where the wrong record type is supplied 408select * from json_populate_recordset(row(0::int),'[{"a":"1","b":"2"},{"a":"3"}]') q (a text, b text); 409select * from json_populate_recordset(row(0::int,0::int),'[{"a":"1","b":"2"},{"a":"3"}]') q (a text, b text); 410select * from json_populate_recordset(row(0::int,0::int,0::int),'[{"a":"1","b":"2"},{"a":"3"}]') q (a text, b text); 411select * from json_populate_recordset(row(1000000000::int,50::int),'[{"b":"2"},{"a":"3"}]') q (a text, b text); 412 413--json_typeof() function 414select value, json_typeof(value) 415 from (values (json '123.4'), 416 (json '-1'), 417 (json '"foo"'), 418 (json 'true'), 419 (json 'false'), 420 (json 'null'), 421 (json '[1, 2, 3]'), 422 (json '[]'), 423 (json '{"x":"foo", "y":123}'), 424 (json '{}'), 425 (NULL::json)) 426 as data(value); 427 428-- json_build_array, json_build_object, json_object_agg 429 430SELECT json_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}'); 431SELECT json_build_array('a', NULL); -- ok 432SELECT json_build_array(VARIADIC NULL::text[]); -- ok 433SELECT json_build_array(VARIADIC '{}'::text[]); -- ok 434SELECT json_build_array(VARIADIC '{a,b,c}'::text[]); -- ok 435SELECT json_build_array(VARIADIC ARRAY['a', NULL]::text[]); -- ok 436SELECT json_build_array(VARIADIC '{1,2,3,4}'::text[]); -- ok 437SELECT json_build_array(VARIADIC '{1,2,3,4}'::int[]); -- ok 438SELECT json_build_array(VARIADIC '{{1,4},{2,5},{3,6}}'::int[][]); -- ok 439 440SELECT json_build_object('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}'); 441 442SELECT json_build_object( 443 'a', json_build_object('b',false,'c',99), 444 'd', json_build_object('e',array[9,8,7]::int[], 445 'f', (select row_to_json(r) from ( select relkind, oid::regclass as name from pg_class where relname = 'pg_class') r))); 446SELECT json_build_object('{a,b,c}'::text[]); -- error 447SELECT json_build_object('{a,b,c}'::text[], '{d,e,f}'::text[]); -- error, key cannot be array 448SELECT json_build_object('a', 'b', 'c'); -- error 449SELECT json_build_object(NULL, 'a'); -- error, key cannot be NULL 450SELECT json_build_object('a', NULL); -- ok 451SELECT json_build_object(VARIADIC NULL::text[]); -- ok 452SELECT json_build_object(VARIADIC '{}'::text[]); -- ok 453SELECT json_build_object(VARIADIC '{a,b,c}'::text[]); -- error 454SELECT json_build_object(VARIADIC ARRAY['a', NULL]::text[]); -- ok 455SELECT json_build_object(VARIADIC ARRAY[NULL, 'a']::text[]); -- error, key cannot be NULL 456SELECT json_build_object(VARIADIC '{1,2,3,4}'::text[]); -- ok 457SELECT json_build_object(VARIADIC '{1,2,3,4}'::int[]); -- ok 458SELECT json_build_object(VARIADIC '{{1,4},{2,5},{3,6}}'::int[][]); -- ok 459 460-- empty objects/arrays 461SELECT json_build_array(); 462 463SELECT json_build_object(); 464 465-- make sure keys are quoted 466SELECT json_build_object(1,2); 467 468-- keys must be scalar and not null 469SELECT json_build_object(null,2); 470 471SELECT json_build_object(r,2) FROM (SELECT 1 AS a, 2 AS b) r; 472 473SELECT json_build_object(json '{"a":1,"b":2}', 3); 474 475SELECT json_build_object('{1,2,3}'::int[], 3); 476 477CREATE TEMP TABLE foo (serial_num int, name text, type text); 478INSERT INTO foo VALUES (847001,'t15','GE1043'); 479INSERT INTO foo VALUES (847002,'t16','GE1043'); 480INSERT INTO foo VALUES (847003,'sub-alpha','GESS90'); 481 482SELECT json_build_object('turbines',json_object_agg(serial_num,json_build_object('name',name,'type',type))) 483FROM foo; 484 485SELECT json_object_agg(name, type) FROM foo; 486 487INSERT INTO foo VALUES (999999, NULL, 'bar'); 488SELECT json_object_agg(name, type) FROM foo; 489 490-- json_object 491 492-- empty object, one dimension 493SELECT json_object('{}'); 494 495-- empty object, two dimensions 496SELECT json_object('{}', '{}'); 497 498-- one dimension 499SELECT json_object('{a,1,b,2,3,NULL,"d e f","a b c"}'); 500 501-- same but with two dimensions 502SELECT json_object('{{a,1},{b,2},{3,NULL},{"d e f","a b c"}}'); 503 504-- odd number error 505SELECT json_object('{a,b,c}'); 506 507-- one column error 508SELECT json_object('{{a},{b}}'); 509 510-- too many columns error 511SELECT json_object('{{a,b,c},{b,c,d}}'); 512 513-- too many dimensions error 514SELECT json_object('{{{a,b},{c,d}},{{b,c},{d,e}}}'); 515 516--two argument form of json_object 517 518select json_object('{a,b,c,"d e f"}','{1,2,3,"a b c"}'); 519 520-- too many dimensions 521SELECT 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"}}'); 522 523-- mismatched dimensions 524 525select json_object('{a,b,c,"d e f",g}','{1,2,3,"a b c"}'); 526 527select json_object('{a,b,c,"d e f"}','{1,2,3,"a b c",g}'); 528 529-- null key error 530 531select json_object('{a,b,NULL,"d e f"}','{1,2,3,"a b c"}'); 532 533-- empty key is allowed 534 535select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}'); 536 537 538-- json_to_record and json_to_recordset 539 540select * from json_to_record('{"a":1,"b":"foo","c":"bar"}') 541 as x(a int, b text, d text); 542 543select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]') 544 as x(a int, b text, c boolean); 545 546select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]') 547 as x(a int, b json, c boolean); 548 549select *, c is null as c_is_null 550from json_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8}'::json) 551 as t(a int, b json, c text, x int); 552 553select *, c is null as c_is_null 554from json_to_recordset('[{"a":1, "b":{"c":16, "d":2}, "x":8}]'::json) 555 as t(a int, b json, c text, x int); 556 557-- json_strip_nulls 558 559select json_strip_nulls(null); 560 561select json_strip_nulls('1'); 562 563select json_strip_nulls('"a string"'); 564 565select json_strip_nulls('null'); 566 567select json_strip_nulls('[1,2,null,3,4]'); 568 569select json_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}'); 570 571select json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]'); 572 573-- an empty object is not null and should not be stripped 574select json_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }'); 575