1-- Strings. 2SELECT '""'::json; -- OK. 3 json 4------ 5 "" 6(1 row) 7 8SELECT $$''$$::json; -- ERROR, single quotes are not allowed 9ERROR: invalid input syntax for type json 10LINE 1: SELECT $$''$$::json; 11 ^ 12DETAIL: Token "'" is invalid. 13CONTEXT: JSON data, line 1: '... 14SELECT '"abc"'::json; -- OK 15 json 16------- 17 "abc" 18(1 row) 19 20SELECT '"abc'::json; -- ERROR, quotes not closed 21ERROR: invalid input syntax for type json 22LINE 1: SELECT '"abc'::json; 23 ^ 24DETAIL: Token ""abc" is invalid. 25CONTEXT: JSON data, line 1: "abc 26SELECT '"abc 27def"'::json; -- ERROR, unescaped newline in string constant 28ERROR: invalid input syntax for type json 29LINE 1: SELECT '"abc 30 ^ 31DETAIL: Character with value 0x0a must be escaped. 32CONTEXT: JSON data, line 1: "abc 33SELECT '"\n\"\\"'::json; -- OK, legal escapes 34 json 35---------- 36 "\n\"\\" 37(1 row) 38 39SELECT '"\v"'::json; -- ERROR, not a valid JSON escape 40ERROR: invalid input syntax for type json 41LINE 1: SELECT '"\v"'::json; 42 ^ 43DETAIL: Escape sequence "\v" is invalid. 44CONTEXT: JSON data, line 1: "\v... 45-- see json_encoding test for input with unicode escapes 46-- Numbers. 47SELECT '1'::json; -- OK 48 json 49------ 50 1 51(1 row) 52 53SELECT '0'::json; -- OK 54 json 55------ 56 0 57(1 row) 58 59SELECT '01'::json; -- ERROR, not valid according to JSON spec 60ERROR: invalid input syntax for type json 61LINE 1: SELECT '01'::json; 62 ^ 63DETAIL: Token "01" is invalid. 64CONTEXT: JSON data, line 1: 01 65SELECT '0.1'::json; -- OK 66 json 67------ 68 0.1 69(1 row) 70 71SELECT '9223372036854775808'::json; -- OK, even though it's too large for int8 72 json 73--------------------- 74 9223372036854775808 75(1 row) 76 77SELECT '1e100'::json; -- OK 78 json 79------- 80 1e100 81(1 row) 82 83SELECT '1.3e100'::json; -- OK 84 json 85--------- 86 1.3e100 87(1 row) 88 89SELECT '1f2'::json; -- ERROR 90ERROR: invalid input syntax for type json 91LINE 1: SELECT '1f2'::json; 92 ^ 93DETAIL: Token "1f2" is invalid. 94CONTEXT: JSON data, line 1: 1f2 95SELECT '0.x1'::json; -- ERROR 96ERROR: invalid input syntax for type json 97LINE 1: SELECT '0.x1'::json; 98 ^ 99DETAIL: Token "0.x1" is invalid. 100CONTEXT: JSON data, line 1: 0.x1 101SELECT '1.3ex100'::json; -- ERROR 102ERROR: invalid input syntax for type json 103LINE 1: SELECT '1.3ex100'::json; 104 ^ 105DETAIL: Token "1.3ex100" is invalid. 106CONTEXT: JSON data, line 1: 1.3ex100 107-- Arrays. 108SELECT '[]'::json; -- OK 109 json 110------ 111 [] 112(1 row) 113 114SELECT '[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]'::json; -- OK 115 json 116---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 117 [[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]] 118(1 row) 119 120SELECT '[1,2]'::json; -- OK 121 json 122------- 123 [1,2] 124(1 row) 125 126SELECT '[1,2,]'::json; -- ERROR, trailing comma 127ERROR: invalid input syntax for type json 128LINE 1: SELECT '[1,2,]'::json; 129 ^ 130DETAIL: Expected JSON value, but found "]". 131CONTEXT: JSON data, line 1: [1,2,] 132SELECT '[1,2'::json; -- ERROR, no closing bracket 133ERROR: invalid input syntax for type json 134LINE 1: SELECT '[1,2'::json; 135 ^ 136DETAIL: The input string ended unexpectedly. 137CONTEXT: JSON data, line 1: [1,2 138SELECT '[1,[2]'::json; -- ERROR, no closing bracket 139ERROR: invalid input syntax for type json 140LINE 1: SELECT '[1,[2]'::json; 141 ^ 142DETAIL: The input string ended unexpectedly. 143CONTEXT: JSON data, line 1: [1,[2] 144-- Objects. 145SELECT '{}'::json; -- OK 146 json 147------ 148 {} 149(1 row) 150 151SELECT '{"abc"}'::json; -- ERROR, no value 152ERROR: invalid input syntax for type json 153LINE 1: SELECT '{"abc"}'::json; 154 ^ 155DETAIL: Expected ":", but found "}". 156CONTEXT: JSON data, line 1: {"abc"} 157SELECT '{"abc":1}'::json; -- OK 158 json 159----------- 160 {"abc":1} 161(1 row) 162 163SELECT '{1:"abc"}'::json; -- ERROR, keys must be strings 164ERROR: invalid input syntax for type json 165LINE 1: SELECT '{1:"abc"}'::json; 166 ^ 167DETAIL: Expected string or "}", but found "1". 168CONTEXT: JSON data, line 1: {1... 169SELECT '{"abc",1}'::json; -- ERROR, wrong separator 170ERROR: invalid input syntax for type json 171LINE 1: SELECT '{"abc",1}'::json; 172 ^ 173DETAIL: Expected ":", but found ",". 174CONTEXT: JSON data, line 1: {"abc",... 175SELECT '{"abc"=1}'::json; -- ERROR, totally wrong separator 176ERROR: invalid input syntax for type json 177LINE 1: SELECT '{"abc"=1}'::json; 178 ^ 179DETAIL: Token "=" is invalid. 180CONTEXT: JSON data, line 1: {"abc"=... 181SELECT '{"abc"::1}'::json; -- ERROR, another wrong separator 182ERROR: invalid input syntax for type json 183LINE 1: SELECT '{"abc"::1}'::json; 184 ^ 185DETAIL: Expected JSON value, but found ":". 186CONTEXT: JSON data, line 1: {"abc"::... 187SELECT '{"abc":1,"def":2,"ghi":[3,4],"hij":{"klm":5,"nop":[6]}}'::json; -- OK 188 json 189--------------------------------------------------------- 190 {"abc":1,"def":2,"ghi":[3,4],"hij":{"klm":5,"nop":[6]}} 191(1 row) 192 193SELECT '{"abc":1:2}'::json; -- ERROR, colon in wrong spot 194ERROR: invalid input syntax for type json 195LINE 1: SELECT '{"abc":1:2}'::json; 196 ^ 197DETAIL: Expected "," or "}", but found ":". 198CONTEXT: JSON data, line 1: {"abc":1:... 199SELECT '{"abc":1,3}'::json; -- ERROR, no value 200ERROR: invalid input syntax for type json 201LINE 1: SELECT '{"abc":1,3}'::json; 202 ^ 203DETAIL: Expected string, but found "3". 204CONTEXT: JSON data, line 1: {"abc":1,3... 205-- Recursion. 206SET max_stack_depth = '100kB'; 207SELECT repeat('[', 10000)::json; 208ERROR: stack depth limit exceeded 209HINT: Increase the configuration parameter "max_stack_depth" (currently 100kB), after ensuring the platform's stack depth limit is adequate. 210SELECT repeat('{"a":', 10000)::json; 211ERROR: stack depth limit exceeded 212HINT: Increase the configuration parameter "max_stack_depth" (currently 100kB), after ensuring the platform's stack depth limit is adequate. 213RESET max_stack_depth; 214-- Miscellaneous stuff. 215SELECT 'true'::json; -- OK 216 json 217------ 218 true 219(1 row) 220 221SELECT 'false'::json; -- OK 222 json 223------- 224 false 225(1 row) 226 227SELECT 'null'::json; -- OK 228 json 229------ 230 null 231(1 row) 232 233SELECT ' true '::json; -- OK, even with extra whitespace 234 json 235-------- 236 true 237(1 row) 238 239SELECT 'true false'::json; -- ERROR, too many values 240ERROR: invalid input syntax for type json 241LINE 1: SELECT 'true false'::json; 242 ^ 243DETAIL: Expected end of input, but found "false". 244CONTEXT: JSON data, line 1: true false 245SELECT 'true, false'::json; -- ERROR, too many values 246ERROR: invalid input syntax for type json 247LINE 1: SELECT 'true, false'::json; 248 ^ 249DETAIL: Expected end of input, but found ",". 250CONTEXT: JSON data, line 1: true,... 251SELECT 'truf'::json; -- ERROR, not a keyword 252ERROR: invalid input syntax for type json 253LINE 1: SELECT 'truf'::json; 254 ^ 255DETAIL: Token "truf" is invalid. 256CONTEXT: JSON data, line 1: truf 257SELECT 'trues'::json; -- ERROR, not a keyword 258ERROR: invalid input syntax for type json 259LINE 1: SELECT 'trues'::json; 260 ^ 261DETAIL: Token "trues" is invalid. 262CONTEXT: JSON data, line 1: trues 263SELECT ''::json; -- ERROR, no value 264ERROR: invalid input syntax for type json 265LINE 1: SELECT ''::json; 266 ^ 267DETAIL: The input string ended unexpectedly. 268CONTEXT: JSON data, line 1: 269SELECT ' '::json; -- ERROR, no value 270ERROR: invalid input syntax for type json 271LINE 1: SELECT ' '::json; 272 ^ 273DETAIL: The input string ended unexpectedly. 274CONTEXT: JSON data, line 1: 275--constructors 276-- array_to_json 277SELECT array_to_json(array(select 1 as a)); 278 array_to_json 279--------------- 280 [1] 281(1 row) 282 283SELECT array_to_json(array_agg(q),false) from (select x as b, x * 2 as c from generate_series(1,3) x) q; 284 array_to_json 285--------------------------------------------- 286 [{"b":1,"c":2},{"b":2,"c":4},{"b":3,"c":6}] 287(1 row) 288 289SELECT array_to_json(array_agg(q),true) from (select x as b, x * 2 as c from generate_series(1,3) x) q; 290 array_to_json 291----------------- 292 [{"b":1,"c":2},+ 293 {"b":2,"c":4},+ 294 {"b":3,"c":6}] 295(1 row) 296 297SELECT array_to_json(array_agg(q),false) 298 FROM ( SELECT $$a$$ || x AS b, y AS c, 299 ARRAY[ROW(x.*,ARRAY[1,2,3]), 300 ROW(y.*,ARRAY[4,5,6])] AS z 301 FROM generate_series(1,2) x, 302 generate_series(4,5) y) q; 303 array_to_json 304------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 305 [{"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]}]}] 306(1 row) 307 308SELECT array_to_json(array_agg(x),false) from generate_series(5,10) x; 309 array_to_json 310---------------- 311 [5,6,7,8,9,10] 312(1 row) 313 314SELECT array_to_json('{{1,5},{99,100}}'::int[]); 315 array_to_json 316------------------ 317 [[1,5],[99,100]] 318(1 row) 319 320-- row_to_json 321SELECT row_to_json(row(1,'foo')); 322 row_to_json 323--------------------- 324 {"f1":1,"f2":"foo"} 325(1 row) 326 327SELECT row_to_json(q) 328FROM (SELECT $$a$$ || x AS b, 329 y AS c, 330 ARRAY[ROW(x.*,ARRAY[1,2,3]), 331 ROW(y.*,ARRAY[4,5,6])] AS z 332 FROM generate_series(1,2) x, 333 generate_series(4,5) y) q; 334 row_to_json 335-------------------------------------------------------------------- 336 {"b":"a1","c":4,"z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]} 337 {"b":"a1","c":5,"z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]} 338 {"b":"a2","c":4,"z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]} 339 {"b":"a2","c":5,"z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]} 340(4 rows) 341 342SELECT row_to_json(q,true) 343FROM (SELECT $$a$$ || x AS b, 344 y AS c, 345 ARRAY[ROW(x.*,ARRAY[1,2,3]), 346 ROW(y.*,ARRAY[4,5,6])] AS z 347 FROM generate_series(1,2) x, 348 generate_series(4,5) y) q; 349 row_to_json 350----------------------------------------------------- 351 {"b":"a1", + 352 "c":4, + 353 "z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]} 354 {"b":"a1", + 355 "c":5, + 356 "z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]} 357 {"b":"a2", + 358 "c":4, + 359 "z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]} 360 {"b":"a2", + 361 "c":5, + 362 "z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]} 363(4 rows) 364 365CREATE TEMP TABLE rows AS 366SELECT x, 'txt' || x as y 367FROM generate_series(1,3) AS x; 368SELECT row_to_json(q,true) 369FROM rows q; 370 row_to_json 371-------------- 372 {"x":1, + 373 "y":"txt1"} 374 {"x":2, + 375 "y":"txt2"} 376 {"x":3, + 377 "y":"txt3"} 378(3 rows) 379 380SELECT row_to_json(row((select array_agg(x) as d from generate_series(5,10) x)),false); 381 row_to_json 382----------------------- 383 {"f1":[5,6,7,8,9,10]} 384(1 row) 385 386-- anyarray column 387select to_json(histogram_bounds) histogram_bounds 388from pg_stats 389where attname = 'tmplname' and tablename = 'pg_pltemplate'; 390 histogram_bounds 391--------------------------------------------------------------------------------------- 392 ["plperl","plperlu","plpgsql","plpython2u","plpython3u","plpythonu","pltcl","pltclu"] 393(1 row) 394 395-- to_json, timestamps 396select to_json(timestamp '2014-05-28 12:22:35.614298'); 397 to_json 398------------------------------ 399 "2014-05-28T12:22:35.614298" 400(1 row) 401 402BEGIN; 403SET LOCAL TIME ZONE 10.5; 404select to_json(timestamptz '2014-05-28 12:22:35.614298-04'); 405 to_json 406------------------------------------ 407 "2014-05-29T02:52:35.614298+10:30" 408(1 row) 409 410SET LOCAL TIME ZONE -8; 411select to_json(timestamptz '2014-05-28 12:22:35.614298-04'); 412 to_json 413------------------------------------ 414 "2014-05-28T08:22:35.614298-08:00" 415(1 row) 416 417COMMIT; 418select to_json(date '2014-05-28'); 419 to_json 420-------------- 421 "2014-05-28" 422(1 row) 423 424select to_json(date 'Infinity'); 425 to_json 426------------ 427 "infinity" 428(1 row) 429 430select to_json(date '-Infinity'); 431 to_json 432------------- 433 "-infinity" 434(1 row) 435 436select to_json(timestamp 'Infinity'); 437 to_json 438------------ 439 "infinity" 440(1 row) 441 442select to_json(timestamp '-Infinity'); 443 to_json 444------------- 445 "-infinity" 446(1 row) 447 448select to_json(timestamptz 'Infinity'); 449 to_json 450------------ 451 "infinity" 452(1 row) 453 454select to_json(timestamptz '-Infinity'); 455 to_json 456------------- 457 "-infinity" 458(1 row) 459 460--json_agg 461SELECT json_agg(q) 462 FROM ( SELECT $$a$$ || x AS b, y AS c, 463 ARRAY[ROW(x.*,ARRAY[1,2,3]), 464 ROW(y.*,ARRAY[4,5,6])] AS z 465 FROM generate_series(1,2) x, 466 generate_series(4,5) y) q; 467 json_agg 468----------------------------------------------------------------------- 469 [{"b":"a1","c":4,"z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}, + 470 {"b":"a1","c":5,"z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}, + 471 {"b":"a2","c":4,"z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}, + 472 {"b":"a2","c":5,"z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}] 473(1 row) 474 475SELECT json_agg(q ORDER BY x, y) 476 FROM rows q; 477 json_agg 478----------------------- 479 [{"x":1,"y":"txt1"}, + 480 {"x":2,"y":"txt2"}, + 481 {"x":3,"y":"txt3"}] 482(1 row) 483 484UPDATE rows SET x = NULL WHERE x = 1; 485SELECT json_agg(q ORDER BY x NULLS FIRST, y) 486 FROM rows q; 487 json_agg 488-------------------------- 489 [{"x":null,"y":"txt1"}, + 490 {"x":2,"y":"txt2"}, + 491 {"x":3,"y":"txt3"}] 492(1 row) 493 494-- non-numeric output 495SELECT row_to_json(q) 496FROM (SELECT 'NaN'::float8 AS "float8field") q; 497 row_to_json 498----------------------- 499 {"float8field":"NaN"} 500(1 row) 501 502SELECT row_to_json(q) 503FROM (SELECT 'Infinity'::float8 AS "float8field") q; 504 row_to_json 505---------------------------- 506 {"float8field":"Infinity"} 507(1 row) 508 509SELECT row_to_json(q) 510FROM (SELECT '-Infinity'::float8 AS "float8field") q; 511 row_to_json 512----------------------------- 513 {"float8field":"-Infinity"} 514(1 row) 515 516-- json input 517SELECT row_to_json(q) 518FROM (SELECT '{"a":1,"b": [2,3,4,"d","e","f"],"c":{"p":1,"q":2}}'::json AS "jsonfield") q; 519 row_to_json 520------------------------------------------------------------------ 521 {"jsonfield":{"a":1,"b": [2,3,4,"d","e","f"],"c":{"p":1,"q":2}}} 522(1 row) 523 524-- json extraction functions 525CREATE TEMP TABLE test_json ( 526 json_type text, 527 test_json json 528); 529INSERT INTO test_json VALUES 530('scalar','"a scalar"'), 531('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'), 532('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}'); 533SELECT test_json -> 'x' 534FROM test_json 535WHERE json_type = 'scalar'; 536 ?column? 537---------- 538 539(1 row) 540 541SELECT test_json -> 'x' 542FROM test_json 543WHERE json_type = 'array'; 544 ?column? 545---------- 546 547(1 row) 548 549SELECT test_json -> 'x' 550FROM test_json 551WHERE json_type = 'object'; 552 ?column? 553---------- 554 555(1 row) 556 557SELECT test_json->'field2' 558FROM test_json 559WHERE json_type = 'object'; 560 ?column? 561---------- 562 "val2" 563(1 row) 564 565SELECT test_json->>'field2' 566FROM test_json 567WHERE json_type = 'object'; 568 ?column? 569---------- 570 val2 571(1 row) 572 573SELECT test_json -> 2 574FROM test_json 575WHERE json_type = 'scalar'; 576 ?column? 577---------- 578 579(1 row) 580 581SELECT test_json -> 2 582FROM test_json 583WHERE json_type = 'array'; 584 ?column? 585---------- 586 "two" 587(1 row) 588 589SELECT test_json -> -1 590FROM test_json 591WHERE json_type = 'array'; 592 ?column? 593---------- 594 {"f1":9} 595(1 row) 596 597SELECT test_json -> 2 598FROM test_json 599WHERE json_type = 'object'; 600 ?column? 601---------- 602 603(1 row) 604 605SELECT test_json->>2 606FROM test_json 607WHERE json_type = 'array'; 608 ?column? 609---------- 610 two 611(1 row) 612 613SELECT test_json ->> 6 FROM test_json WHERE json_type = 'array'; 614 ?column? 615---------- 616 [1,2,3] 617(1 row) 618 619SELECT test_json ->> 7 FROM test_json WHERE json_type = 'array'; 620 ?column? 621---------- 622 {"f1":9} 623(1 row) 624 625SELECT test_json ->> 'field4' FROM test_json WHERE json_type = 'object'; 626 ?column? 627---------- 628 4 629(1 row) 630 631SELECT test_json ->> 'field5' FROM test_json WHERE json_type = 'object'; 632 ?column? 633---------- 634 [1,2,3] 635(1 row) 636 637SELECT test_json ->> 'field6' FROM test_json WHERE json_type = 'object'; 638 ?column? 639---------- 640 {"f1":9} 641(1 row) 642 643SELECT json_object_keys(test_json) 644FROM test_json 645WHERE json_type = 'scalar'; 646ERROR: cannot call json_object_keys on a scalar 647SELECT json_object_keys(test_json) 648FROM test_json 649WHERE json_type = 'array'; 650ERROR: cannot call json_object_keys on an array 651SELECT json_object_keys(test_json) 652FROM test_json 653WHERE json_type = 'object'; 654 json_object_keys 655------------------ 656 field1 657 field2 658 field3 659 field4 660 field5 661 field6 662(6 rows) 663 664-- test extending object_keys resultset - initial resultset size is 256 665select count(*) from 666 (select json_object_keys(json_object(array_agg(g))) 667 from (select unnest(array['f'||n,n::text])as g 668 from generate_series(1,300) as n) x ) y; 669 count 670------- 671 300 672(1 row) 673 674-- nulls 675select (test_json->'field3') is null as expect_false 676from test_json 677where json_type = 'object'; 678 expect_false 679-------------- 680 f 681(1 row) 682 683select (test_json->>'field3') is null as expect_true 684from test_json 685where json_type = 'object'; 686 expect_true 687------------- 688 t 689(1 row) 690 691select (test_json->3) is null as expect_false 692from test_json 693where json_type = 'array'; 694 expect_false 695-------------- 696 f 697(1 row) 698 699select (test_json->>3) is null as expect_true 700from test_json 701where json_type = 'array'; 702 expect_true 703------------- 704 t 705(1 row) 706 707-- corner cases 708select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> null::text; 709 ?column? 710---------- 711 712(1 row) 713 714select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> null::int; 715 ?column? 716---------- 717 718(1 row) 719 720select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 1; 721 ?column? 722---------- 723 724(1 row) 725 726select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> -1; 727 ?column? 728---------- 729 730(1 row) 731 732select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 'z'; 733 ?column? 734---------- 735 736(1 row) 737 738select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> ''; 739 ?column? 740---------- 741 742(1 row) 743 744select '[{"b": "c"}, {"b": "cc"}]'::json -> 1; 745 ?column? 746------------- 747 {"b": "cc"} 748(1 row) 749 750select '[{"b": "c"}, {"b": "cc"}]'::json -> 3; 751 ?column? 752---------- 753 754(1 row) 755 756select '[{"b": "c"}, {"b": "cc"}]'::json -> 'z'; 757 ?column? 758---------- 759 760(1 row) 761 762select '{"a": "c", "b": null}'::json -> 'b'; 763 ?column? 764---------- 765 null 766(1 row) 767 768select '"foo"'::json -> 1; 769 ?column? 770---------- 771 772(1 row) 773 774select '"foo"'::json -> 'z'; 775 ?column? 776---------- 777 778(1 row) 779 780select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> null::text; 781 ?column? 782---------- 783 784(1 row) 785 786select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> null::int; 787 ?column? 788---------- 789 790(1 row) 791 792select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> 1; 793 ?column? 794---------- 795 796(1 row) 797 798select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> 'z'; 799 ?column? 800---------- 801 802(1 row) 803 804select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json ->> ''; 805 ?column? 806---------- 807 808(1 row) 809 810select '[{"b": "c"}, {"b": "cc"}]'::json ->> 1; 811 ?column? 812------------- 813 {"b": "cc"} 814(1 row) 815 816select '[{"b": "c"}, {"b": "cc"}]'::json ->> 3; 817 ?column? 818---------- 819 820(1 row) 821 822select '[{"b": "c"}, {"b": "cc"}]'::json ->> 'z'; 823 ?column? 824---------- 825 826(1 row) 827 828select '{"a": "c", "b": null}'::json ->> 'b'; 829 ?column? 830---------- 831 832(1 row) 833 834select '"foo"'::json ->> 1; 835 ?column? 836---------- 837 838(1 row) 839 840select '"foo"'::json ->> 'z'; 841 ?column? 842---------- 843 844(1 row) 845 846-- array length 847SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]'); 848 json_array_length 849------------------- 850 5 851(1 row) 852 853SELECT json_array_length('[]'); 854 json_array_length 855------------------- 856 0 857(1 row) 858 859SELECT json_array_length('{"f1":1,"f2":[5,6]}'); 860ERROR: cannot get array length of a non-array 861SELECT json_array_length('4'); 862ERROR: cannot get array length of a scalar 863-- each 864select json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}'); 865 json_each 866------------------- 867 (f1,"[1,2,3]") 868 (f2,"{""f3"":1}") 869 (f4,null) 870(3 rows) 871 872select * from json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q; 873 key | value 874-----+----------- 875 f1 | [1,2,3] 876 f2 | {"f3":1} 877 f4 | null 878 f5 | 99 879 f6 | "stringy" 880(5 rows) 881 882select json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":"null"}'); 883 json_each_text 884------------------- 885 (f1,"[1,2,3]") 886 (f2,"{""f3"":1}") 887 (f4,) 888 (f5,null) 889(4 rows) 890 891select * from json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q; 892 key | value 893-----+---------- 894 f1 | [1,2,3] 895 f2 | {"f3":1} 896 f4 | 897 f5 | 99 898 f6 | stringy 899(5 rows) 900 901-- extract_path, extract_path_as_text 902select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6'); 903 json_extract_path 904------------------- 905 "stringy" 906(1 row) 907 908select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2'); 909 json_extract_path 910------------------- 911 {"f3":1} 912(1 row) 913 914select json_extract_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',0::text); 915 json_extract_path 916------------------- 917 "f3" 918(1 row) 919 920select json_extract_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',1::text); 921 json_extract_path 922------------------- 923 1 924(1 row) 925 926select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6'); 927 json_extract_path_text 928------------------------ 929 stringy 930(1 row) 931 932select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2'); 933 json_extract_path_text 934------------------------ 935 {"f3":1} 936(1 row) 937 938select json_extract_path_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',0::text); 939 json_extract_path_text 940------------------------ 941 f3 942(1 row) 943 944select json_extract_path_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',1::text); 945 json_extract_path_text 946------------------------ 947 1 948(1 row) 949 950-- extract_path nulls 951select json_extract_path('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') is null as expect_false; 952 expect_false 953-------------- 954 f 955(1 row) 956 957select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') is null as expect_true; 958 expect_true 959------------- 960 t 961(1 row) 962 963select json_extract_path('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') is null as expect_false; 964 expect_false 965-------------- 966 f 967(1 row) 968 969select json_extract_path_text('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') is null as expect_true; 970 expect_true 971------------- 972 t 973(1 row) 974 975-- extract_path operators 976select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f4','f6']; 977 ?column? 978----------- 979 "stringy" 980(1 row) 981 982select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f2']; 983 ?column? 984---------- 985 {"f3":1} 986(1 row) 987 988select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f2','0']; 989 ?column? 990---------- 991 "f3" 992(1 row) 993 994select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f2','1']; 995 ?column? 996---------- 997 1 998(1 row) 999 1000select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f4','f6']; 1001 ?column? 1002---------- 1003 stringy 1004(1 row) 1005 1006select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2']; 1007 ?column? 1008---------- 1009 {"f3":1} 1010(1 row) 1011 1012select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2','0']; 1013 ?column? 1014---------- 1015 f3 1016(1 row) 1017 1018select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2','1']; 1019 ?column? 1020---------- 1021 1 1022(1 row) 1023 1024-- corner cases for same 1025select '{"a": {"b":{"c": "foo"}}}'::json #> '{}'; 1026 ?column? 1027--------------------------- 1028 {"a": {"b":{"c": "foo"}}} 1029(1 row) 1030 1031select '[1,2,3]'::json #> '{}'; 1032 ?column? 1033---------- 1034 [1,2,3] 1035(1 row) 1036 1037select '"foo"'::json #> '{}'; 1038 ?column? 1039---------- 1040 "foo" 1041(1 row) 1042 1043select '42'::json #> '{}'; 1044 ?column? 1045---------- 1046 42 1047(1 row) 1048 1049select 'null'::json #> '{}'; 1050 ?column? 1051---------- 1052 null 1053(1 row) 1054 1055select '{"a": {"b":{"c": "foo"}}}'::json #> array['a']; 1056 ?column? 1057-------------------- 1058 {"b":{"c": "foo"}} 1059(1 row) 1060 1061select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', null]; 1062 ?column? 1063---------- 1064 1065(1 row) 1066 1067select '{"a": {"b":{"c": "foo"}}}'::json #> array['a', '']; 1068 ?column? 1069---------- 1070 1071(1 row) 1072 1073select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b']; 1074 ?column? 1075-------------- 1076 {"c": "foo"} 1077(1 row) 1078 1079select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b','c']; 1080 ?column? 1081---------- 1082 "foo" 1083(1 row) 1084 1085select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b','c','d']; 1086 ?column? 1087---------- 1088 1089(1 row) 1090 1091select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','z','c']; 1092 ?column? 1093---------- 1094 1095(1 row) 1096 1097select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #> array['a','1','b']; 1098 ?column? 1099---------- 1100 "cc" 1101(1 row) 1102 1103select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #> array['a','z','b']; 1104 ?column? 1105---------- 1106 1107(1 row) 1108 1109select '[{"b": "c"}, {"b": "cc"}]'::json #> array['1','b']; 1110 ?column? 1111---------- 1112 "cc" 1113(1 row) 1114 1115select '[{"b": "c"}, {"b": "cc"}]'::json #> array['z','b']; 1116 ?column? 1117---------- 1118 1119(1 row) 1120 1121select '[{"b": "c"}, {"b": null}]'::json #> array['1','b']; 1122 ?column? 1123---------- 1124 null 1125(1 row) 1126 1127select '"foo"'::json #> array['z']; 1128 ?column? 1129---------- 1130 1131(1 row) 1132 1133select '42'::json #> array['f2']; 1134 ?column? 1135---------- 1136 1137(1 row) 1138 1139select '42'::json #> array['0']; 1140 ?column? 1141---------- 1142 1143(1 row) 1144 1145select '{"a": {"b":{"c": "foo"}}}'::json #>> '{}'; 1146 ?column? 1147--------------------------- 1148 {"a": {"b":{"c": "foo"}}} 1149(1 row) 1150 1151select '[1,2,3]'::json #>> '{}'; 1152 ?column? 1153---------- 1154 [1,2,3] 1155(1 row) 1156 1157select '"foo"'::json #>> '{}'; 1158 ?column? 1159---------- 1160 foo 1161(1 row) 1162 1163select '42'::json #>> '{}'; 1164 ?column? 1165---------- 1166 42 1167(1 row) 1168 1169select 'null'::json #>> '{}'; 1170 ?column? 1171---------- 1172 1173(1 row) 1174 1175select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a']; 1176 ?column? 1177-------------------- 1178 {"b":{"c": "foo"}} 1179(1 row) 1180 1181select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', null]; 1182 ?column? 1183---------- 1184 1185(1 row) 1186 1187select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a', '']; 1188 ?column? 1189---------- 1190 1191(1 row) 1192 1193select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b']; 1194 ?column? 1195-------------- 1196 {"c": "foo"} 1197(1 row) 1198 1199select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b','c']; 1200 ?column? 1201---------- 1202 foo 1203(1 row) 1204 1205select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b','c','d']; 1206 ?column? 1207---------- 1208 1209(1 row) 1210 1211select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','z','c']; 1212 ?column? 1213---------- 1214 1215(1 row) 1216 1217select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #>> array['a','1','b']; 1218 ?column? 1219---------- 1220 cc 1221(1 row) 1222 1223select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #>> array['a','z','b']; 1224 ?column? 1225---------- 1226 1227(1 row) 1228 1229select '[{"b": "c"}, {"b": "cc"}]'::json #>> array['1','b']; 1230 ?column? 1231---------- 1232 cc 1233(1 row) 1234 1235select '[{"b": "c"}, {"b": "cc"}]'::json #>> array['z','b']; 1236 ?column? 1237---------- 1238 1239(1 row) 1240 1241select '[{"b": "c"}, {"b": null}]'::json #>> array['1','b']; 1242 ?column? 1243---------- 1244 1245(1 row) 1246 1247select '"foo"'::json #>> array['z']; 1248 ?column? 1249---------- 1250 1251(1 row) 1252 1253select '42'::json #>> array['f2']; 1254 ?column? 1255---------- 1256 1257(1 row) 1258 1259select '42'::json #>> array['0']; 1260 ?column? 1261---------- 1262 1263(1 row) 1264 1265-- array_elements 1266select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]'); 1267 json_array_elements 1268----------------------- 1269 1 1270 true 1271 [1,[2,3]] 1272 null 1273 {"f1":1,"f2":[7,8,9]} 1274 false 1275 "stringy" 1276(7 rows) 1277 1278select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q; 1279 value 1280----------------------- 1281 1 1282 true 1283 [1,[2,3]] 1284 null 1285 {"f1":1,"f2":[7,8,9]} 1286 false 1287 "stringy" 1288(7 rows) 1289 1290select json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]'); 1291 json_array_elements_text 1292-------------------------- 1293 1 1294 true 1295 [1,[2,3]] 1296 1297 {"f1":1,"f2":[7,8,9]} 1298 false 1299 stringy 1300(7 rows) 1301 1302select * from json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q; 1303 value 1304----------------------- 1305 1 1306 true 1307 [1,[2,3]] 1308 1309 {"f1":1,"f2":[7,8,9]} 1310 false 1311 stringy 1312(7 rows) 1313 1314-- populate_record 1315create type jpop as (a text, b int, c timestamp); 1316CREATE DOMAIN js_int_not_null AS int NOT NULL; 1317CREATE DOMAIN js_int_array_1d AS int[] CHECK(array_length(VALUE, 1) = 3); 1318CREATE DOMAIN js_int_array_2d AS int[][] CHECK(array_length(VALUE, 2) = 3); 1319create type j_unordered_pair as (x int, y int); 1320create domain j_ordered_pair as j_unordered_pair check((value).x <= (value).y); 1321CREATE TYPE jsrec AS ( 1322 i int, 1323 ia _int4, 1324 ia1 int[], 1325 ia2 int[][], 1326 ia3 int[][][], 1327 ia1d js_int_array_1d, 1328 ia2d js_int_array_2d, 1329 t text, 1330 ta text[], 1331 c char(10), 1332 ca char(10)[], 1333 ts timestamp, 1334 js json, 1335 jsb jsonb, 1336 jsa json[], 1337 rec jpop, 1338 reca jpop[] 1339); 1340CREATE TYPE jsrec_i_not_null AS ( 1341 i js_int_not_null 1342); 1343select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q; 1344 a | b | c 1345--------+---+--- 1346 blurfl | | 1347(1 row) 1348 1349select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q; 1350 a | b | c 1351--------+---+-------------------------- 1352 blurfl | 3 | Mon Dec 31 15:30:56 2012 1353(1 row) 1354 1355select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q; 1356 a | b | c 1357--------+---+--- 1358 blurfl | | 1359(1 row) 1360 1361select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q; 1362 a | b | c 1363--------+---+-------------------------- 1364 blurfl | 3 | Mon Dec 31 15:30:56 2012 1365(1 row) 1366 1367select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}') q; 1368 a | b | c 1369-----------------+---+--- 1370 [100,200,false] | | 1371(1 row) 1372 1373select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}') q; 1374 a | b | c 1375-----------------+---+-------------------------- 1376 [100,200,false] | 3 | Mon Dec 31 15:30:56 2012 1377(1 row) 1378 1379select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}') q; 1380ERROR: invalid input syntax for type timestamp: "[100,200,false]" 1381select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{}') q; 1382 a | b | c 1383---+---+-------------------------- 1384 x | 3 | Mon Dec 31 15:30:56 2012 1385(1 row) 1386 1387SELECT i FROM json_populate_record(NULL::jsrec_i_not_null, '{"x": 43.2}') q; 1388ERROR: domain js_int_not_null does not allow null values 1389SELECT i FROM json_populate_record(NULL::jsrec_i_not_null, '{"i": null}') q; 1390ERROR: domain js_int_not_null does not allow null values 1391SELECT i FROM json_populate_record(NULL::jsrec_i_not_null, '{"i": 12345}') q; 1392 i 1393------- 1394 12345 1395(1 row) 1396 1397SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": null}') q; 1398 ia 1399---- 1400 1401(1 row) 1402 1403SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": 123}') q; 1404ERROR: expected JSON array 1405HINT: See the value of key "ia". 1406SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": [1, "2", null, 4]}') q; 1407 ia 1408-------------- 1409 {1,2,NULL,4} 1410(1 row) 1411 1412SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": [[1, 2], [3, 4]]}') q; 1413 ia 1414--------------- 1415 {{1,2},{3,4}} 1416(1 row) 1417 1418SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": [[1], 2]}') q; 1419ERROR: expected JSON array 1420HINT: See the array element [1] of key "ia". 1421SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": [[1], [2, 3]]}') q; 1422ERROR: malformed JSON array 1423DETAIL: Multidimensional arrays must have sub-arrays with matching dimensions. 1424SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": "{1,2,3}"}') q; 1425 ia 1426--------- 1427 {1,2,3} 1428(1 row) 1429 1430SELECT ia1 FROM json_populate_record(NULL::jsrec, '{"ia1": null}') q; 1431 ia1 1432----- 1433 1434(1 row) 1435 1436SELECT ia1 FROM json_populate_record(NULL::jsrec, '{"ia1": 123}') q; 1437ERROR: expected JSON array 1438HINT: See the value of key "ia1". 1439SELECT ia1 FROM json_populate_record(NULL::jsrec, '{"ia1": [1, "2", null, 4]}') q; 1440 ia1 1441-------------- 1442 {1,2,NULL,4} 1443(1 row) 1444 1445SELECT ia1 FROM json_populate_record(NULL::jsrec, '{"ia1": [[1, 2, 3]]}') q; 1446 ia1 1447----------- 1448 {{1,2,3}} 1449(1 row) 1450 1451SELECT ia1d FROM json_populate_record(NULL::jsrec, '{"ia1d": null}') q; 1452 ia1d 1453------ 1454 1455(1 row) 1456 1457SELECT ia1d FROM json_populate_record(NULL::jsrec, '{"ia1d": 123}') q; 1458ERROR: expected JSON array 1459HINT: See the value of key "ia1d". 1460SELECT ia1d FROM json_populate_record(NULL::jsrec, '{"ia1d": [1, "2", null, 4]}') q; 1461ERROR: value for domain js_int_array_1d violates check constraint "js_int_array_1d_check" 1462SELECT ia1d FROM json_populate_record(NULL::jsrec, '{"ia1d": [1, "2", null]}') q; 1463 ia1d 1464------------ 1465 {1,2,NULL} 1466(1 row) 1467 1468SELECT ia2 FROM json_populate_record(NULL::jsrec, '{"ia2": [1, "2", null, 4]}') q; 1469 ia2 1470-------------- 1471 {1,2,NULL,4} 1472(1 row) 1473 1474SELECT ia2 FROM json_populate_record(NULL::jsrec, '{"ia2": [[1, 2], [null, 4]]}') q; 1475 ia2 1476------------------ 1477 {{1,2},{NULL,4}} 1478(1 row) 1479 1480SELECT ia2 FROM json_populate_record(NULL::jsrec, '{"ia2": [[], []]}') q; 1481 ia2 1482----- 1483 {} 1484(1 row) 1485 1486SELECT ia2 FROM json_populate_record(NULL::jsrec, '{"ia2": [[1, 2], [3]]}') q; 1487ERROR: malformed JSON array 1488DETAIL: Multidimensional arrays must have sub-arrays with matching dimensions. 1489SELECT ia2 FROM json_populate_record(NULL::jsrec, '{"ia2": [[1, 2], 3, 4]}') q; 1490ERROR: expected JSON array 1491HINT: See the array element [1] of key "ia2". 1492SELECT ia2d FROM json_populate_record(NULL::jsrec, '{"ia2d": [[1, "2"], [null, 4]]}') q; 1493ERROR: value for domain js_int_array_2d violates check constraint "js_int_array_2d_check" 1494SELECT ia2d FROM json_populate_record(NULL::jsrec, '{"ia2d": [[1, "2", 3], [null, 5, 6]]}') q; 1495 ia2d 1496---------------------- 1497 {{1,2,3},{NULL,5,6}} 1498(1 row) 1499 1500SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [1, "2", null, 4]}') q; 1501 ia3 1502-------------- 1503 {1,2,NULL,4} 1504(1 row) 1505 1506SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [[1, 2], [null, 4]]}') q; 1507 ia3 1508------------------ 1509 {{1,2},{NULL,4}} 1510(1 row) 1511 1512SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [ [[], []], [[], []], [[], []] ]}') q; 1513 ia3 1514----- 1515 {} 1516(1 row) 1517 1518SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [ [[1, 2]], [[3, 4]] ]}') q; 1519 ia3 1520------------------- 1521 {{{1,2}},{{3,4}}} 1522(1 row) 1523 1524SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8]] ]}') q; 1525 ia3 1526------------------------------- 1527 {{{1,2},{3,4}},{{5,6},{7,8}}} 1528(1 row) 1529 1530SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8], [9, 10]] ]}') q; 1531ERROR: malformed JSON array 1532DETAIL: Multidimensional arrays must have sub-arrays with matching dimensions. 1533SELECT ta FROM json_populate_record(NULL::jsrec, '{"ta": null}') q; 1534 ta 1535---- 1536 1537(1 row) 1538 1539SELECT ta FROM json_populate_record(NULL::jsrec, '{"ta": 123}') q; 1540ERROR: expected JSON array 1541HINT: See the value of key "ta". 1542SELECT ta FROM json_populate_record(NULL::jsrec, '{"ta": [1, "2", null, 4]}') q; 1543 ta 1544-------------- 1545 {1,2,NULL,4} 1546(1 row) 1547 1548SELECT ta FROM json_populate_record(NULL::jsrec, '{"ta": [[1, 2, 3], {"k": "v"}]}') q; 1549ERROR: expected JSON array 1550HINT: See the array element [1] of key "ta". 1551SELECT c FROM json_populate_record(NULL::jsrec, '{"c": null}') q; 1552 c 1553--- 1554 1555(1 row) 1556 1557SELECT c FROM json_populate_record(NULL::jsrec, '{"c": "aaa"}') q; 1558 c 1559------------ 1560 aaa 1561(1 row) 1562 1563SELECT c FROM json_populate_record(NULL::jsrec, '{"c": "aaaaaaaaaa"}') q; 1564 c 1565------------ 1566 aaaaaaaaaa 1567(1 row) 1568 1569SELECT c FROM json_populate_record(NULL::jsrec, '{"c": "aaaaaaaaaaaaa"}') q; 1570ERROR: value too long for type character(10) 1571SELECT ca FROM json_populate_record(NULL::jsrec, '{"ca": null}') q; 1572 ca 1573---- 1574 1575(1 row) 1576 1577SELECT ca FROM json_populate_record(NULL::jsrec, '{"ca": 123}') q; 1578ERROR: expected JSON array 1579HINT: See the value of key "ca". 1580SELECT ca FROM json_populate_record(NULL::jsrec, '{"ca": [1, "2", null, 4]}') q; 1581 ca 1582----------------------------------------------- 1583 {"1 ","2 ",NULL,"4 "} 1584(1 row) 1585 1586SELECT ca FROM json_populate_record(NULL::jsrec, '{"ca": ["aaaaaaaaaaaaaaaa"]}') q; 1587ERROR: value too long for type character(10) 1588SELECT ca FROM json_populate_record(NULL::jsrec, '{"ca": [[1, 2, 3], {"k": "v"}]}') q; 1589ERROR: expected JSON array 1590HINT: See the array element [1] of key "ca". 1591SELECT js FROM json_populate_record(NULL::jsrec, '{"js": null}') q; 1592 js 1593---- 1594 1595(1 row) 1596 1597SELECT js FROM json_populate_record(NULL::jsrec, '{"js": true}') q; 1598 js 1599------ 1600 true 1601(1 row) 1602 1603SELECT js FROM json_populate_record(NULL::jsrec, '{"js": 123.45}') q; 1604 js 1605-------- 1606 123.45 1607(1 row) 1608 1609SELECT js FROM json_populate_record(NULL::jsrec, '{"js": "123.45"}') q; 1610 js 1611---------- 1612 "123.45" 1613(1 row) 1614 1615SELECT js FROM json_populate_record(NULL::jsrec, '{"js": "abc"}') q; 1616 js 1617------- 1618 "abc" 1619(1 row) 1620 1621SELECT js FROM json_populate_record(NULL::jsrec, '{"js": [123, "123", null, {"key": "value"}]}') q; 1622 js 1623-------------------------------------- 1624 [123, "123", null, {"key": "value"}] 1625(1 row) 1626 1627SELECT js FROM json_populate_record(NULL::jsrec, '{"js": {"a": "bbb", "b": null, "c": 123.45}}') q; 1628 js 1629-------------------------------------- 1630 {"a": "bbb", "b": null, "c": 123.45} 1631(1 row) 1632 1633SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": null}') q; 1634 jsb 1635----- 1636 1637(1 row) 1638 1639SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": true}') q; 1640 jsb 1641------ 1642 true 1643(1 row) 1644 1645SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": 123.45}') q; 1646 jsb 1647-------- 1648 123.45 1649(1 row) 1650 1651SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": "123.45"}') q; 1652 jsb 1653---------- 1654 "123.45" 1655(1 row) 1656 1657SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": "abc"}') q; 1658 jsb 1659------- 1660 "abc" 1661(1 row) 1662 1663SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": [123, "123", null, {"key": "value"}]}') q; 1664 jsb 1665-------------------------------------- 1666 [123, "123", null, {"key": "value"}] 1667(1 row) 1668 1669SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": {"a": "bbb", "b": null, "c": 123.45}}') q; 1670 jsb 1671-------------------------------------- 1672 {"a": "bbb", "b": null, "c": 123.45} 1673(1 row) 1674 1675SELECT jsa FROM json_populate_record(NULL::jsrec, '{"jsa": null}') q; 1676 jsa 1677----- 1678 1679(1 row) 1680 1681SELECT jsa FROM json_populate_record(NULL::jsrec, '{"jsa": 123}') q; 1682ERROR: expected JSON array 1683HINT: See the value of key "jsa". 1684SELECT jsa FROM json_populate_record(NULL::jsrec, '{"jsa": [1, "2", null, 4]}') q; 1685 jsa 1686-------------------- 1687 {1,"\"2\"",NULL,4} 1688(1 row) 1689 1690SELECT jsa FROM json_populate_record(NULL::jsrec, '{"jsa": ["aaa", null, [1, 2, "3", {}], { "k" : "v" }]}') q; 1691 jsa 1692---------------------------------------------------------- 1693 {"\"aaa\"",NULL,"[1, 2, \"3\", {}]","{ \"k\" : \"v\" }"} 1694(1 row) 1695 1696SELECT rec FROM json_populate_record(NULL::jsrec, '{"rec": 123}') q; 1697ERROR: cannot call populate_composite on a scalar 1698SELECT rec FROM json_populate_record(NULL::jsrec, '{"rec": [1, 2]}') q; 1699ERROR: cannot call populate_composite on an array 1700SELECT rec FROM json_populate_record(NULL::jsrec, '{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}') q; 1701 rec 1702----------------------------------- 1703 (abc,,"Thu Jan 02 00:00:00 2003") 1704(1 row) 1705 1706SELECT rec FROM json_populate_record(NULL::jsrec, '{"rec": "(abc,42,01.02.2003)"}') q; 1707 rec 1708------------------------------------- 1709 (abc,42,"Thu Jan 02 00:00:00 2003") 1710(1 row) 1711 1712SELECT reca FROM json_populate_record(NULL::jsrec, '{"reca": 123}') q; 1713ERROR: expected JSON array 1714HINT: See the value of key "reca". 1715SELECT reca FROM json_populate_record(NULL::jsrec, '{"reca": [1, 2]}') q; 1716ERROR: cannot call populate_composite on a scalar 1717SELECT reca FROM json_populate_record(NULL::jsrec, '{"reca": [{"a": "abc", "b": 456}, null, {"c": "01.02.2003", "x": 43.2}]}') q; 1718 reca 1719-------------------------------------------------------- 1720 {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"} 1721(1 row) 1722 1723SELECT reca FROM json_populate_record(NULL::jsrec, '{"reca": ["(abc,42,01.02.2003)"]}') q; 1724 reca 1725------------------------------------------- 1726 {"(abc,42,\"Thu Jan 02 00:00:00 2003\")"} 1727(1 row) 1728 1729SELECT reca FROM json_populate_record(NULL::jsrec, '{"reca": "{\"(abc,42,01.02.2003)\"}"}') q; 1730 reca 1731------------------------------------------- 1732 {"(abc,42,\"Thu Jan 02 00:00:00 2003\")"} 1733(1 row) 1734 1735SELECT rec FROM json_populate_record( 1736 row(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 1737 row('x',3,'2012-12-31 15:30:56')::jpop,NULL)::jsrec, 1738 '{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}' 1739) q; 1740 rec 1741------------------------------------ 1742 (abc,3,"Thu Jan 02 00:00:00 2003") 1743(1 row) 1744 1745-- anonymous record type 1746SELECT json_populate_record(null::record, '{"x": 0, "y": 1}'); 1747ERROR: could not determine row type for result of json_populate_record 1748HINT: Provide a non-null record argument, or call the function in the FROM clause using a column definition list. 1749SELECT json_populate_record(row(1,2), '{"f1": 0, "f2": 1}'); 1750 json_populate_record 1751---------------------- 1752 (0,1) 1753(1 row) 1754 1755SELECT * FROM 1756 json_populate_record(null::record, '{"x": 776}') AS (x int, y int); 1757 x | y 1758-----+--- 1759 776 | 1760(1 row) 1761 1762-- composite domain 1763SELECT json_populate_record(null::j_ordered_pair, '{"x": 0, "y": 1}'); 1764 json_populate_record 1765---------------------- 1766 (0,1) 1767(1 row) 1768 1769SELECT json_populate_record(row(1,2)::j_ordered_pair, '{"x": 0}'); 1770 json_populate_record 1771---------------------- 1772 (0,2) 1773(1 row) 1774 1775SELECT json_populate_record(row(1,2)::j_ordered_pair, '{"x": 1, "y": 0}'); 1776ERROR: value for domain j_ordered_pair violates check constraint "j_ordered_pair_check" 1777-- populate_recordset 1778select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 1779 a | b | c 1780--------+---+-------------------------- 1781 blurfl | | 1782 | 3 | Fri Jan 20 10:42:53 2012 1783(2 rows) 1784 1785select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 1786 a | b | c 1787--------+----+-------------------------- 1788 blurfl | 99 | 1789 def | 3 | Fri Jan 20 10:42:53 2012 1790(2 rows) 1791 1792select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 1793 a | b | c 1794--------+---+-------------------------- 1795 blurfl | | 1796 | 3 | Fri Jan 20 10:42:53 2012 1797(2 rows) 1798 1799select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 1800 a | b | c 1801--------+----+-------------------------- 1802 blurfl | 99 | 1803 def | 3 | Fri Jan 20 10:42:53 2012 1804(2 rows) 1805 1806select * 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; 1807 a | b | c 1808---------------+----+-------------------------- 1809 [100,200,300] | 99 | 1810 {"z":true} | 3 | Fri Jan 20 10:42:53 2012 1811(2 rows) 1812 1813select * 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; 1814ERROR: invalid input syntax for type timestamp: "[100,200,300]" 1815create type jpop2 as (a int, b json, c int, d int); 1816select * from json_populate_recordset(null::jpop2, '[{"a":2,"c":3,"b":{"z":4},"d":6}]') q; 1817 a | b | c | d 1818---+---------+---+--- 1819 2 | {"z":4} | 3 | 6 1820(1 row) 1821 1822select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 1823 a | b | c 1824--------+---+-------------------------- 1825 blurfl | | 1826 | 3 | Fri Jan 20 10:42:53 2012 1827(2 rows) 1828 1829select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 1830 a | b | c 1831--------+----+-------------------------- 1832 blurfl | 99 | 1833 def | 3 | Fri Jan 20 10:42:53 2012 1834(2 rows) 1835 1836select * 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; 1837 a | b | c 1838---------------+----+-------------------------- 1839 [100,200,300] | 99 | 1840 {"z":true} | 3 | Fri Jan 20 10:42:53 2012 1841(2 rows) 1842 1843-- anonymous record type 1844SELECT json_populate_recordset(null::record, '[{"x": 0, "y": 1}]'); 1845ERROR: could not determine row type for result of json_populate_recordset 1846HINT: Provide a non-null record argument, or call the function in the FROM clause using a column definition list. 1847SELECT json_populate_recordset(row(1,2), '[{"f1": 0, "f2": 1}]'); 1848 json_populate_recordset 1849------------------------- 1850 (0,1) 1851(1 row) 1852 1853SELECT i, json_populate_recordset(row(i,50), '[{"f1":"42"},{"f2":"43"}]') 1854FROM (VALUES (1),(2)) v(i); 1855 i | json_populate_recordset 1856---+------------------------- 1857 1 | (42,50) 1858 1 | (1,43) 1859 2 | (42,50) 1860 2 | (2,43) 1861(4 rows) 1862 1863SELECT * FROM 1864 json_populate_recordset(null::record, '[{"x": 776}]') AS (x int, y int); 1865 x | y 1866-----+--- 1867 776 | 1868(1 row) 1869 1870-- empty array is a corner case 1871SELECT json_populate_recordset(null::record, '[]'); 1872ERROR: could not determine row type for result of json_populate_recordset 1873HINT: Provide a non-null record argument, or call the function in the FROM clause using a column definition list. 1874SELECT json_populate_recordset(row(1,2), '[]'); 1875 json_populate_recordset 1876------------------------- 1877(0 rows) 1878 1879SELECT * FROM json_populate_recordset(NULL::jpop,'[]') q; 1880 a | b | c 1881---+---+--- 1882(0 rows) 1883 1884SELECT * FROM 1885 json_populate_recordset(null::record, '[]') AS (x int, y int); 1886 x | y 1887---+--- 1888(0 rows) 1889 1890-- composite domain 1891SELECT json_populate_recordset(null::j_ordered_pair, '[{"x": 0, "y": 1}]'); 1892 json_populate_recordset 1893------------------------- 1894 (0,1) 1895(1 row) 1896 1897SELECT json_populate_recordset(row(1,2)::j_ordered_pair, '[{"x": 0}, {"y": 3}]'); 1898 json_populate_recordset 1899------------------------- 1900 (0,2) 1901 (1,3) 1902(2 rows) 1903 1904SELECT json_populate_recordset(row(1,2)::j_ordered_pair, '[{"x": 1, "y": 0}]'); 1905ERROR: value for domain j_ordered_pair violates check constraint "j_ordered_pair_check" 1906-- negative cases where the wrong record type is supplied 1907select * from json_populate_recordset(row(0::int),'[{"a":"1","b":"2"},{"a":"3"}]') q (a text, b text); 1908ERROR: function return row and query-specified return row do not match 1909DETAIL: Returned row contains 1 attribute, but query expects 2. 1910select * from json_populate_recordset(row(0::int,0::int),'[{"a":"1","b":"2"},{"a":"3"}]') q (a text, b text); 1911ERROR: function return row and query-specified return row do not match 1912DETAIL: Returned type integer at ordinal position 1, but query expects text. 1913select * from json_populate_recordset(row(0::int,0::int,0::int),'[{"a":"1","b":"2"},{"a":"3"}]') q (a text, b text); 1914ERROR: function return row and query-specified return row do not match 1915DETAIL: Returned row contains 3 attributes, but query expects 2. 1916select * from json_populate_recordset(row(1000000000::int,50::int),'[{"b":"2"},{"a":"3"}]') q (a text, b text); 1917ERROR: function return row and query-specified return row do not match 1918DETAIL: Returned type integer at ordinal position 1, but query expects text. 1919-- test type info caching in json_populate_record() 1920CREATE TEMP TABLE jspoptest (js json); 1921INSERT INTO jspoptest 1922SELECT '{ 1923 "jsa": [1, "2", null, 4], 1924 "rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}, 1925 "reca": [{"a": "abc", "b": 456}, null, {"c": "01.02.2003", "x": 43.2}] 1926}'::json 1927FROM generate_series(1, 3); 1928SELECT (json_populate_record(NULL::jsrec, js)).* FROM jspoptest; 1929 i | ia | ia1 | ia2 | ia3 | ia1d | ia2d | t | ta | c | ca | ts | js | jsb | jsa | rec | reca 1930---+----+-----+-----+-----+------+------+---+----+---+----+----+----+-----+--------------------+-----------------------------------+-------------------------------------------------------- 1931 | | | | | | | | | | | | | | {1,"\"2\"",NULL,4} | (abc,,"Thu Jan 02 00:00:00 2003") | {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"} 1932 | | | | | | | | | | | | | | {1,"\"2\"",NULL,4} | (abc,,"Thu Jan 02 00:00:00 2003") | {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"} 1933 | | | | | | | | | | | | | | {1,"\"2\"",NULL,4} | (abc,,"Thu Jan 02 00:00:00 2003") | {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"} 1934(3 rows) 1935 1936DROP TYPE jsrec; 1937DROP TYPE jsrec_i_not_null; 1938DROP DOMAIN js_int_not_null; 1939DROP DOMAIN js_int_array_1d; 1940DROP DOMAIN js_int_array_2d; 1941DROP DOMAIN j_ordered_pair; 1942DROP TYPE j_unordered_pair; 1943--json_typeof() function 1944select value, json_typeof(value) 1945 from (values (json '123.4'), 1946 (json '-1'), 1947 (json '"foo"'), 1948 (json 'true'), 1949 (json 'false'), 1950 (json 'null'), 1951 (json '[1, 2, 3]'), 1952 (json '[]'), 1953 (json '{"x":"foo", "y":123}'), 1954 (json '{}'), 1955 (NULL::json)) 1956 as data(value); 1957 value | json_typeof 1958----------------------+------------- 1959 123.4 | number 1960 -1 | number 1961 "foo" | string 1962 true | boolean 1963 false | boolean 1964 null | null 1965 [1, 2, 3] | array 1966 [] | array 1967 {"x":"foo", "y":123} | object 1968 {} | object 1969 | 1970(11 rows) 1971 1972-- json_build_array, json_build_object, json_object_agg 1973SELECT json_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}'); 1974 json_build_array 1975----------------------------------------------------------------------- 1976 ["a", 1, "b", 1.2, "c", true, "d", null, "e", {"x": 3, "y": [1,2,3]}] 1977(1 row) 1978 1979SELECT json_build_array('a', NULL); -- ok 1980 json_build_array 1981------------------ 1982 ["a", null] 1983(1 row) 1984 1985SELECT json_build_array(VARIADIC NULL::text[]); -- ok 1986 json_build_array 1987------------------ 1988 1989(1 row) 1990 1991SELECT json_build_array(VARIADIC '{}'::text[]); -- ok 1992 json_build_array 1993------------------ 1994 [] 1995(1 row) 1996 1997SELECT json_build_array(VARIADIC '{a,b,c}'::text[]); -- ok 1998 json_build_array 1999------------------ 2000 ["a", "b", "c"] 2001(1 row) 2002 2003SELECT json_build_array(VARIADIC ARRAY['a', NULL]::text[]); -- ok 2004 json_build_array 2005------------------ 2006 ["a", null] 2007(1 row) 2008 2009SELECT json_build_array(VARIADIC '{1,2,3,4}'::text[]); -- ok 2010 json_build_array 2011---------------------- 2012 ["1", "2", "3", "4"] 2013(1 row) 2014 2015SELECT json_build_array(VARIADIC '{1,2,3,4}'::int[]); -- ok 2016 json_build_array 2017------------------ 2018 [1, 2, 3, 4] 2019(1 row) 2020 2021SELECT json_build_array(VARIADIC '{{1,4},{2,5},{3,6}}'::int[][]); -- ok 2022 json_build_array 2023-------------------- 2024 [1, 4, 2, 5, 3, 6] 2025(1 row) 2026 2027SELECT json_build_object('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}'); 2028 json_build_object 2029---------------------------------------------------------------------------- 2030 {"a" : 1, "b" : 1.2, "c" : true, "d" : null, "e" : {"x": 3, "y": [1,2,3]}} 2031(1 row) 2032 2033SELECT json_build_object( 2034 'a', json_build_object('b',false,'c',99), 2035 'd', json_build_object('e',array[9,8,7]::int[], 2036 'f', (select row_to_json(r) from ( select relkind, oid::regclass as name from pg_class where relname = 'pg_class') r))); 2037 json_build_object 2038------------------------------------------------------------------------------------------------- 2039 {"a" : {"b" : false, "c" : 99}, "d" : {"e" : [9,8,7], "f" : {"relkind":"r","name":"pg_class"}}} 2040(1 row) 2041 2042SELECT json_build_object('{a,b,c}'::text[]); -- error 2043ERROR: argument list must have even number of elements 2044HINT: The arguments of json_build_object() must consist of alternating keys and values. 2045SELECT json_build_object('{a,b,c}'::text[], '{d,e,f}'::text[]); -- error, key cannot be array 2046ERROR: key value must be scalar, not array, composite, or json 2047SELECT json_build_object('a', 'b', 'c'); -- error 2048ERROR: argument list must have even number of elements 2049HINT: The arguments of json_build_object() must consist of alternating keys and values. 2050SELECT json_build_object(NULL, 'a'); -- error, key cannot be NULL 2051ERROR: argument 1 cannot be null 2052HINT: Object keys should be text. 2053SELECT json_build_object('a', NULL); -- ok 2054 json_build_object 2055------------------- 2056 {"a" : null} 2057(1 row) 2058 2059SELECT json_build_object(VARIADIC NULL::text[]); -- ok 2060 json_build_object 2061------------------- 2062 2063(1 row) 2064 2065SELECT json_build_object(VARIADIC '{}'::text[]); -- ok 2066 json_build_object 2067------------------- 2068 {} 2069(1 row) 2070 2071SELECT json_build_object(VARIADIC '{a,b,c}'::text[]); -- error 2072ERROR: argument list must have even number of elements 2073HINT: The arguments of json_build_object() must consist of alternating keys and values. 2074SELECT json_build_object(VARIADIC ARRAY['a', NULL]::text[]); -- ok 2075 json_build_object 2076------------------- 2077 {"a" : null} 2078(1 row) 2079 2080SELECT json_build_object(VARIADIC ARRAY[NULL, 'a']::text[]); -- error, key cannot be NULL 2081ERROR: argument 1 cannot be null 2082HINT: Object keys should be text. 2083SELECT json_build_object(VARIADIC '{1,2,3,4}'::text[]); -- ok 2084 json_build_object 2085------------------------ 2086 {"1" : "2", "3" : "4"} 2087(1 row) 2088 2089SELECT json_build_object(VARIADIC '{1,2,3,4}'::int[]); -- ok 2090 json_build_object 2091-------------------- 2092 {"1" : 2, "3" : 4} 2093(1 row) 2094 2095SELECT json_build_object(VARIADIC '{{1,4},{2,5},{3,6}}'::int[][]); -- ok 2096 json_build_object 2097----------------------------- 2098 {"1" : 4, "2" : 5, "3" : 6} 2099(1 row) 2100 2101-- empty objects/arrays 2102SELECT json_build_array(); 2103 json_build_array 2104------------------ 2105 [] 2106(1 row) 2107 2108SELECT json_build_object(); 2109 json_build_object 2110------------------- 2111 {} 2112(1 row) 2113 2114-- make sure keys are quoted 2115SELECT json_build_object(1,2); 2116 json_build_object 2117------------------- 2118 {"1" : 2} 2119(1 row) 2120 2121-- keys must be scalar and not null 2122SELECT json_build_object(null,2); 2123ERROR: argument 1 cannot be null 2124HINT: Object keys should be text. 2125SELECT json_build_object(r,2) FROM (SELECT 1 AS a, 2 AS b) r; 2126ERROR: key value must be scalar, not array, composite, or json 2127SELECT json_build_object(json '{"a":1,"b":2}', 3); 2128ERROR: key value must be scalar, not array, composite, or json 2129SELECT json_build_object('{1,2,3}'::int[], 3); 2130ERROR: key value must be scalar, not array, composite, or json 2131CREATE TEMP TABLE foo (serial_num int, name text, type text); 2132INSERT INTO foo VALUES (847001,'t15','GE1043'); 2133INSERT INTO foo VALUES (847002,'t16','GE1043'); 2134INSERT INTO foo VALUES (847003,'sub-alpha','GESS90'); 2135SELECT json_build_object('turbines',json_object_agg(serial_num,json_build_object('name',name,'type',type))) 2136FROM foo; 2137 json_build_object 2138------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2139 {"turbines" : { "847001" : {"name" : "t15", "type" : "GE1043"}, "847002" : {"name" : "t16", "type" : "GE1043"}, "847003" : {"name" : "sub-alpha", "type" : "GESS90"} }} 2140(1 row) 2141 2142SELECT json_object_agg(name, type) FROM foo; 2143 json_object_agg 2144---------------------------------------------------------------- 2145 { "t15" : "GE1043", "t16" : "GE1043", "sub-alpha" : "GESS90" } 2146(1 row) 2147 2148INSERT INTO foo VALUES (999999, NULL, 'bar'); 2149SELECT json_object_agg(name, type) FROM foo; 2150ERROR: field name must not be null 2151-- json_object 2152-- empty object, one dimension 2153SELECT json_object('{}'); 2154 json_object 2155------------- 2156 {} 2157(1 row) 2158 2159-- empty object, two dimensions 2160SELECT json_object('{}', '{}'); 2161 json_object 2162------------- 2163 {} 2164(1 row) 2165 2166-- one dimension 2167SELECT json_object('{a,1,b,2,3,NULL,"d e f","a b c"}'); 2168 json_object 2169------------------------------------------------------- 2170 {"a" : "1", "b" : "2", "3" : null, "d e f" : "a b c"} 2171(1 row) 2172 2173-- same but with two dimensions 2174SELECT json_object('{{a,1},{b,2},{3,NULL},{"d e f","a b c"}}'); 2175 json_object 2176------------------------------------------------------- 2177 {"a" : "1", "b" : "2", "3" : null, "d e f" : "a b c"} 2178(1 row) 2179 2180-- odd number error 2181SELECT json_object('{a,b,c}'); 2182ERROR: array must have even number of elements 2183-- one column error 2184SELECT json_object('{{a},{b}}'); 2185ERROR: array must have two columns 2186-- too many columns error 2187SELECT json_object('{{a,b,c},{b,c,d}}'); 2188ERROR: array must have two columns 2189-- too many dimensions error 2190SELECT json_object('{{{a,b},{c,d}},{{b,c},{d,e}}}'); 2191ERROR: wrong number of array subscripts 2192--two argument form of json_object 2193select json_object('{a,b,c,"d e f"}','{1,2,3,"a b c"}'); 2194 json_object 2195------------------------------------------------------ 2196 {"a" : "1", "b" : "2", "c" : "3", "d e f" : "a b c"} 2197(1 row) 2198 2199-- too many dimensions 2200SELECT 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"}}'); 2201ERROR: wrong number of array subscripts 2202-- mismatched dimensions 2203select json_object('{a,b,c,"d e f",g}','{1,2,3,"a b c"}'); 2204ERROR: mismatched array dimensions 2205select json_object('{a,b,c,"d e f"}','{1,2,3,"a b c",g}'); 2206ERROR: mismatched array dimensions 2207-- null key error 2208select json_object('{a,b,NULL,"d e f"}','{1,2,3,"a b c"}'); 2209ERROR: null value not allowed for object key 2210-- empty key is allowed 2211select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}'); 2212 json_object 2213----------------------------------------------------- 2214 {"a" : "1", "b" : "2", "" : "3", "d e f" : "a b c"} 2215(1 row) 2216 2217-- json_to_record and json_to_recordset 2218select * from json_to_record('{"a":1,"b":"foo","c":"bar"}') 2219 as x(a int, b text, d text); 2220 a | b | d 2221---+-----+--- 2222 1 | foo | 2223(1 row) 2224 2225select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]') 2226 as x(a int, b text, c boolean); 2227 a | b | c 2228---+-----+--- 2229 1 | foo | 2230 2 | bar | t 2231(2 rows) 2232 2233select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]') 2234 as x(a int, b json, c boolean); 2235 a | b | c 2236---+-------------+--- 2237 1 | {"d":"foo"} | t 2238 2 | {"d":"bar"} | f 2239(2 rows) 2240 2241select *, c is null as c_is_null 2242from json_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8, "ca": ["1 2", 3], "ia": [[1,2],[3,4]], "r": {"a": "aaa", "b": 123}}'::json) 2243 as t(a int, b json, c text, x int, ca char(5)[], ia int[][], r jpop); 2244 a | b | c | x | ca | ia | r | c_is_null 2245---+-----------------+---+---+-------------------+---------------+------------+----------- 2246 1 | {"c":16, "d":2} | | 8 | {"1 2 ","3 "} | {{1,2},{3,4}} | (aaa,123,) | t 2247(1 row) 2248 2249select *, c is null as c_is_null 2250from json_to_recordset('[{"a":1, "b":{"c":16, "d":2}, "x":8}]'::json) 2251 as t(a int, b json, c text, x int); 2252 a | b | c | x | c_is_null 2253---+-----------------+---+---+----------- 2254 1 | {"c":16, "d":2} | | 8 | t 2255(1 row) 2256 2257select * from json_to_record('{"ia": null}') as x(ia _int4); 2258 ia 2259---- 2260 2261(1 row) 2262 2263select * from json_to_record('{"ia": 123}') as x(ia _int4); 2264ERROR: expected JSON array 2265HINT: See the value of key "ia". 2266select * from json_to_record('{"ia": [1, "2", null, 4]}') as x(ia _int4); 2267 ia 2268-------------- 2269 {1,2,NULL,4} 2270(1 row) 2271 2272select * from json_to_record('{"ia": [[1, 2], [3, 4]]}') as x(ia _int4); 2273 ia 2274--------------- 2275 {{1,2},{3,4}} 2276(1 row) 2277 2278select * from json_to_record('{"ia": [[1], 2]}') as x(ia _int4); 2279ERROR: expected JSON array 2280HINT: See the array element [1] of key "ia". 2281select * from json_to_record('{"ia": [[1], [2, 3]]}') as x(ia _int4); 2282ERROR: malformed JSON array 2283DETAIL: Multidimensional arrays must have sub-arrays with matching dimensions. 2284select * from json_to_record('{"ia2": [1, 2, 3]}') as x(ia2 int[][]); 2285 ia2 2286--------- 2287 {1,2,3} 2288(1 row) 2289 2290select * from json_to_record('{"ia2": [[1, 2], [3, 4]]}') as x(ia2 int4[][]); 2291 ia2 2292--------------- 2293 {{1,2},{3,4}} 2294(1 row) 2295 2296select * from json_to_record('{"ia2": [[[1], [2], [3]]]}') as x(ia2 int4[][]); 2297 ia2 2298----------------- 2299 {{{1},{2},{3}}} 2300(1 row) 2301 2302select * from json_to_record('{"out": {"key": 1}}') as x(out json); 2303 out 2304------------ 2305 {"key": 1} 2306(1 row) 2307 2308select * from json_to_record('{"out": [{"key": 1}]}') as x(out json); 2309 out 2310-------------- 2311 [{"key": 1}] 2312(1 row) 2313 2314select * from json_to_record('{"out": "{\"key\": 1}"}') as x(out json); 2315 out 2316---------------- 2317 "{\"key\": 1}" 2318(1 row) 2319 2320select * from json_to_record('{"out": {"key": 1}}') as x(out jsonb); 2321 out 2322------------ 2323 {"key": 1} 2324(1 row) 2325 2326select * from json_to_record('{"out": [{"key": 1}]}') as x(out jsonb); 2327 out 2328-------------- 2329 [{"key": 1}] 2330(1 row) 2331 2332select * from json_to_record('{"out": "{\"key\": 1}"}') as x(out jsonb); 2333 out 2334---------------- 2335 "{\"key\": 1}" 2336(1 row) 2337 2338-- json_strip_nulls 2339select json_strip_nulls(null); 2340 json_strip_nulls 2341------------------ 2342 2343(1 row) 2344 2345select json_strip_nulls('1'); 2346 json_strip_nulls 2347------------------ 2348 1 2349(1 row) 2350 2351select json_strip_nulls('"a string"'); 2352 json_strip_nulls 2353------------------ 2354 "a string" 2355(1 row) 2356 2357select json_strip_nulls('null'); 2358 json_strip_nulls 2359------------------ 2360 null 2361(1 row) 2362 2363select json_strip_nulls('[1,2,null,3,4]'); 2364 json_strip_nulls 2365------------------ 2366 [1,2,null,3,4] 2367(1 row) 2368 2369select json_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}'); 2370 json_strip_nulls 2371------------------------------------ 2372 {"a":1,"c":[2,null,3],"d":{"e":4}} 2373(1 row) 2374 2375select json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]'); 2376 json_strip_nulls 2377--------------------- 2378 [1,{"a":1,"c":2},3] 2379(1 row) 2380 2381-- an empty object is not null and should not be stripped 2382select json_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }'); 2383 json_strip_nulls 2384------------------ 2385 {"a":{},"d":{}} 2386(1 row) 2387 2388-- json to tsvector 2389select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::json); 2390 to_tsvector 2391--------------------------------------------------------------------------- 2392 'aaa':1 'bbb':2 'ccc':4 'ddd':3 'eee':6 'fff':7 'ggg':8 'hhh':10 'iii':11 2393(1 row) 2394 2395-- json to tsvector with config 2396select to_tsvector('simple', '{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::json); 2397 to_tsvector 2398--------------------------------------------------------------------------- 2399 'aaa':1 'bbb':2 'ccc':4 'ddd':3 'eee':6 'fff':7 'ggg':8 'hhh':10 'iii':11 2400(1 row) 2401 2402-- json to tsvector with stop words 2403select to_tsvector('english', '{"a": "aaa in bbb ddd ccc", "b": ["the eee fff ggg"], "c": {"d": "hhh. iii"}}'::json); 2404 to_tsvector 2405---------------------------------------------------------------------------- 2406 'aaa':1 'bbb':3 'ccc':5 'ddd':4 'eee':8 'fff':9 'ggg':10 'hhh':12 'iii':13 2407(1 row) 2408 2409-- json to tsvector with numeric values 2410select to_tsvector('english', '{"a": "aaa in bbb ddd ccc", "b": 123, "c": 456}'::json); 2411 to_tsvector 2412--------------------------------- 2413 'aaa':1 'bbb':3 'ccc':5 'ddd':4 2414(1 row) 2415 2416-- json_to_tsvector 2417select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '"all"'); 2418 json_to_tsvector 2419---------------------------------------------------------------------------------------- 2420 '123':8 '456':12 'aaa':2 'b':6 'bbb':4 'c':10 'd':14 'f':18 'fals':20 'g':22 'true':16 2421(1 row) 2422 2423select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '"key"'); 2424 json_to_tsvector 2425-------------------------------- 2426 'b':2 'c':4 'd':6 'f':8 'g':10 2427(1 row) 2428 2429select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '"string"'); 2430 json_to_tsvector 2431------------------ 2432 'aaa':1 'bbb':3 2433(1 row) 2434 2435select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '"numeric"'); 2436 json_to_tsvector 2437------------------ 2438 '123':1 '456':3 2439(1 row) 2440 2441select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '"boolean"'); 2442 json_to_tsvector 2443------------------- 2444 'fals':3 'true':1 2445(1 row) 2446 2447select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '["string", "numeric"]'); 2448 json_to_tsvector 2449--------------------------------- 2450 '123':5 '456':7 'aaa':1 'bbb':3 2451(1 row) 2452 2453select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '"all"'); 2454 json_to_tsvector 2455---------------------------------------------------------------------------------------- 2456 '123':8 '456':12 'aaa':2 'b':6 'bbb':4 'c':10 'd':14 'f':18 'fals':20 'g':22 'true':16 2457(1 row) 2458 2459select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '"key"'); 2460 json_to_tsvector 2461-------------------------------- 2462 'b':2 'c':4 'd':6 'f':8 'g':10 2463(1 row) 2464 2465select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '"string"'); 2466 json_to_tsvector 2467------------------ 2468 'aaa':1 'bbb':3 2469(1 row) 2470 2471select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '"numeric"'); 2472 json_to_tsvector 2473------------------ 2474 '123':1 '456':3 2475(1 row) 2476 2477select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '"boolean"'); 2478 json_to_tsvector 2479------------------- 2480 'fals':3 'true':1 2481(1 row) 2482 2483select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '["string", "numeric"]'); 2484 json_to_tsvector 2485--------------------------------- 2486 '123':5 '456':7 'aaa':1 'bbb':3 2487(1 row) 2488 2489-- ts_vector corner cases 2490select to_tsvector('""'::json); 2491 to_tsvector 2492------------- 2493 2494(1 row) 2495 2496select to_tsvector('{}'::json); 2497 to_tsvector 2498------------- 2499 2500(1 row) 2501 2502select to_tsvector('[]'::json); 2503 to_tsvector 2504------------- 2505 2506(1 row) 2507 2508select to_tsvector('null'::json); 2509 to_tsvector 2510------------- 2511 2512(1 row) 2513 2514-- json_to_tsvector corner cases 2515select json_to_tsvector('""'::json, '"all"'); 2516 json_to_tsvector 2517------------------ 2518 2519(1 row) 2520 2521select json_to_tsvector('{}'::json, '"all"'); 2522 json_to_tsvector 2523------------------ 2524 2525(1 row) 2526 2527select json_to_tsvector('[]'::json, '"all"'); 2528 json_to_tsvector 2529------------------ 2530 2531(1 row) 2532 2533select json_to_tsvector('null'::json, '"all"'); 2534 json_to_tsvector 2535------------------ 2536 2537(1 row) 2538 2539select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '""'); 2540ERROR: wrong flag in flag array: "" 2541HINT: Possible values are: "string", "numeric", "boolean", "key", and "all" 2542select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '{}'); 2543ERROR: wrong flag type, only arrays and scalars are allowed 2544select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '[]'); 2545 json_to_tsvector 2546------------------ 2547 2548(1 row) 2549 2550select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, 'null'); 2551ERROR: flag array element is not a string 2552HINT: Possible values are: "string", "numeric", "boolean", "key", and "all" 2553select json_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::json, '["all", null]'); 2554ERROR: flag array element is not a string 2555HINT: Possible values are: "string", "numeric", "boolean", "key", and "all" 2556-- ts_headline for json 2557select ts_headline('{"a": "aaa bbb", "b": {"c": "ccc ddd fff", "c1": "ccc1 ddd1"}, "d": ["ggg hhh", "iii jjj"]}'::json, tsquery('bbb & ddd & hhh')); 2558 ts_headline 2559--------------------------------------------------------------------------------------------------------- 2560 {"a":"aaa <b>bbb</b>","b":{"c":"ccc <b>ddd</b> fff","c1":"ccc1 ddd1"},"d":["ggg <b>hhh</b>","iii jjj"]} 2561(1 row) 2562 2563select ts_headline('english', '{"a": "aaa bbb", "b": {"c": "ccc ddd fff"}, "d": ["ggg hhh", "iii jjj"]}'::json, tsquery('bbb & ddd & hhh')); 2564 ts_headline 2565---------------------------------------------------------------------------------------- 2566 {"a":"aaa <b>bbb</b>","b":{"c":"ccc <b>ddd</b> fff"},"d":["ggg <b>hhh</b>","iii jjj"]} 2567(1 row) 2568 2569select ts_headline('{"a": "aaa bbb", "b": {"c": "ccc ddd fff", "c1": "ccc1 ddd1"}, "d": ["ggg hhh", "iii jjj"]}'::json, tsquery('bbb & ddd & hhh'), 'StartSel = <, StopSel = >'); 2570 ts_headline 2571------------------------------------------------------------------------------------------ 2572 {"a":"aaa <bbb>","b":{"c":"ccc <ddd> fff","c1":"ccc1 ddd1"},"d":["ggg <hhh>","iii jjj"]} 2573(1 row) 2574 2575select ts_headline('english', '{"a": "aaa bbb", "b": {"c": "ccc ddd fff", "c1": "ccc1 ddd1"}, "d": ["ggg hhh", "iii jjj"]}'::json, tsquery('bbb & ddd & hhh'), 'StartSel = <, StopSel = >'); 2576 ts_headline 2577------------------------------------------------------------------------------------------ 2578 {"a":"aaa <bbb>","b":{"c":"ccc <ddd> fff","c1":"ccc1 ddd1"},"d":["ggg <hhh>","iii jjj"]} 2579(1 row) 2580 2581-- corner cases for ts_headline with json 2582select ts_headline('null'::json, tsquery('aaa & bbb')); 2583 ts_headline 2584------------- 2585 null 2586(1 row) 2587 2588select ts_headline('{}'::json, tsquery('aaa & bbb')); 2589 ts_headline 2590------------- 2591 {} 2592(1 row) 2593 2594select ts_headline('[]'::json, tsquery('aaa & bbb')); 2595 ts_headline 2596------------- 2597 [] 2598(1 row) 2599 2600