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