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