1-- Strings. 2SELECT 3 '""'::jsonb; 4 5-- OK. 6SELECT 7 $$ ''$$::jsonb; 8 9-- ERROR, single quotes are not allowed 10SELECT 11 '"abc"'::jsonb; 12 13-- OK 14SELECT 15 '"abc'::jsonb; 16 17-- ERROR, quotes not closed 18SELECT 19 '"abc 20def"'::jsonb; 21 22-- ERROR, unescaped newline in string constant 23SELECT 24 '"\n\"\\"'::jsonb; 25 26-- OK, legal escapes 27SELECT 28 '"\v"'::jsonb; 29 30-- ERROR, not a valid JSON escape 31-- see json_encoding test for input with unicode escapes 32-- Numbers. 33SELECT 34 '1'::jsonb; 35 36-- OK 37SELECT 38 '0'::jsonb; 39 40-- OK 41SELECT 42 '01'::jsonb; 43 44-- ERROR, not valid according to JSON spec 45SELECT 46 '0.1'::jsonb; 47 48-- OK 49SELECT 50 '9223372036854775808'::jsonb; 51 52-- OK, even though it's too large for int8 53SELECT 54 '1e100'::jsonb; 55 56-- OK 57SELECT 58 '1.3e100'::jsonb; 59 60-- OK 61SELECT 62 '1f2'::jsonb; 63 64-- ERROR 65SELECT 66 '0.x1'::jsonb; 67 68-- ERROR 69SELECT 70 '1.3ex100'::jsonb; 71 72-- ERROR 73-- Arrays. 74SELECT 75 '[]'::jsonb; 76 77-- OK 78SELECT 79 '[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]'::jsonb; 80 81-- OK 82SELECT 83 '[1,2]'::jsonb; 84 85-- OK 86SELECT 87 '[1,2,]'::jsonb; 88 89-- ERROR, trailing comma 90SELECT 91 '[1,2'::jsonb; 92 93-- ERROR, no closing bracket 94SELECT 95 '[1,[2]'::jsonb; 96 97-- ERROR, no closing bracket 98-- Objects. 99SELECT 100 '{}'::jsonb; 101 102-- OK 103SELECT 104 '{"abc"}'::jsonb; 105 106-- ERROR, no value 107SELECT 108 '{"abc":1}'::jsonb; 109 110-- OK 111SELECT 112 '{1:"abc"}'::jsonb; 113 114-- ERROR, keys must be strings 115SELECT 116 '{"abc",1}'::jsonb; 117 118-- ERROR, wrong separator 119SELECT 120 '{"abc"=1}'::jsonb; 121 122-- ERROR, totally wrong separator 123SELECT 124 '{"abc"::1}'::jsonb; 125 126-- ERROR, another wrong separator 127SELECT 128 '{"abc":1,"def":2,"ghi":[3,4],"hij":{"klm":5,"nop":[6]}}'::jsonb; 129 130-- OK 131SELECT 132 '{"abc":1:2}'::jsonb; 133 134-- ERROR, colon in wrong spot 135SELECT 136 '{"abc":1,3}'::jsonb; 137 138-- ERROR, no value 139-- Recursion. 140SET max_stack_depth = '100kB'; 141 142SELECT 143 repeat('[', 10000)::jsonb; 144 145SELECT 146 repeat('{"a":', 10000)::jsonb; 147 148RESET max_stack_depth; 149 150-- Miscellaneous stuff. 151SELECT 152 'true'::jsonb; 153 154-- OK 155SELECT 156 'false'::jsonb; 157 158-- OK 159SELECT 160 'null'::jsonb; 161 162-- OK 163SELECT 164 ' true '::jsonb; 165 166-- OK, even with extra whitespace 167SELECT 168 'true false'::jsonb; 169 170-- ERROR, too many values 171SELECT 172 'true, false'::jsonb; 173 174-- ERROR, too many values 175SELECT 176 'truf'::jsonb; 177 178-- ERROR, not a keyword 179SELECT 180 'trues'::jsonb; 181 182-- ERROR, not a keyword 183SELECT 184 ''::jsonb; 185 186-- ERROR, no value 187SELECT 188 ' '::jsonb; 189 190-- ERROR, no value 191-- make sure jsonb is passed through json generators without being escaped 192SELECT 193 array_to_json(ARRAY[jsonb '{"a":1}', jsonb '{"b":[2,3]}']); 194 195-- anyarray column 196SELECT 197 to_jsonb (histogram_bounds) histogram_bounds 198FROM 199 pg_stats 200WHERE 201 attname = 'tmplname' 202 AND tablename = 'pg_pltemplate'; 203 204-- to_jsonb, timestamps 205SELECT 206 to_jsonb (timestamp '2014-05-28 12:22:35.614298'); 207 208BEGIN; 209SET LOCAL TIME ZONE 10.5; 210SELECT 211 to_jsonb (timestamptz '2014-05-28 12:22:35.614298-04'); 212SET LOCAL TIME ZONE - 8; 213SELECT 214 to_jsonb (timestamptz '2014-05-28 12:22:35.614298-04'); 215COMMIT; 216 217SELECT 218 to_jsonb (date '2014-05-28'); 219 220SELECT 221 to_jsonb (date 'Infinity'); 222 223SELECT 224 to_jsonb (date '-Infinity'); 225 226SELECT 227 to_jsonb (timestamp 'Infinity'); 228 229SELECT 230 to_jsonb (timestamp '-Infinity'); 231 232SELECT 233 to_jsonb (timestamptz 'Infinity'); 234 235SELECT 236 to_jsonb (timestamptz '-Infinity'); 237 238--jsonb_agg 239CREATE TEMP TABLE ROWS AS 240SELECT 241 x, 242 'txt' || x AS y 243FROM 244 generate_series(1, 3) AS x; 245 246SELECT 247 jsonb_agg(q) 248FROM ( 249 SELECT 250 $$ a$$ || x AS b, 251 y AS c, 252 ARRAY[ROW (x.*, ARRAY[1, 2, 3]), ROW (y.*, ARRAY[4, 5, 6])] AS z 253 FROM 254 generate_series(1, 2) x, 255 generate_series(4, 5) y) q; 256 257SELECT 258 jsonb_agg(q ORDER BY x, y) 259FROM 260 ROWS q; 261 262UPDATE 263 ROWS 264SET 265 x = NULL 266WHERE 267 x = 1; 268 269SELECT 270 jsonb_agg(q ORDER BY x NULLS FIRST, y) 271FROM 272 ROWS q; 273 274-- jsonb extraction functions 275CREATE TEMP TABLE test_jsonb ( 276 json_type text, 277 test_json jsonb 278); 279 280INSERT INTO test_jsonb 281 VALUES ('scalar', '"a scalar"'), ('array', '["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'), ('object', '{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}'); 282 283SELECT 284 test_json -> 'x' 285FROM 286 test_jsonb 287WHERE 288 json_type = 'scalar'; 289 290SELECT 291 test_json -> 'x' 292FROM 293 test_jsonb 294WHERE 295 json_type = 'array'; 296 297SELECT 298 test_json -> 'x' 299FROM 300 test_jsonb 301WHERE 302 json_type = 'object'; 303 304SELECT 305 test_json -> 'field2' 306FROM 307 test_jsonb 308WHERE 309 json_type = 'object'; 310 311SELECT 312 test_json ->> 'field2' 313FROM 314 test_jsonb 315WHERE 316 json_type = 'scalar'; 317 318SELECT 319 test_json ->> 'field2' 320FROM 321 test_jsonb 322WHERE 323 json_type = 'array'; 324 325SELECT 326 test_json ->> 'field2' 327FROM 328 test_jsonb 329WHERE 330 json_type = 'object'; 331 332SELECT 333 test_json -> 2 334FROM 335 test_jsonb 336WHERE 337 json_type = 'scalar'; 338 339SELECT 340 test_json -> 2 341FROM 342 test_jsonb 343WHERE 344 json_type = 'array'; 345 346SELECT 347 test_json -> 9 348FROM 349 test_jsonb 350WHERE 351 json_type = 'array'; 352 353SELECT 354 test_json -> 2 355FROM 356 test_jsonb 357WHERE 358 json_type = 'object'; 359 360SELECT 361 test_json ->> 6 362FROM 363 test_jsonb 364WHERE 365 json_type = 'array'; 366 367SELECT 368 test_json ->> 7 369FROM 370 test_jsonb 371WHERE 372 json_type = 'array'; 373 374SELECT 375 test_json ->> 'field4' 376FROM 377 test_jsonb 378WHERE 379 json_type = 'object'; 380 381SELECT 382 test_json ->> 'field5' 383FROM 384 test_jsonb 385WHERE 386 json_type = 'object'; 387 388SELECT 389 test_json ->> 'field6' 390FROM 391 test_jsonb 392WHERE 393 json_type = 'object'; 394 395SELECT 396 test_json ->> 2 397FROM 398 test_jsonb 399WHERE 400 json_type = 'scalar'; 401 402SELECT 403 test_json ->> 2 404FROM 405 test_jsonb 406WHERE 407 json_type = 'array'; 408 409SELECT 410 test_json ->> 2 411FROM 412 test_jsonb 413WHERE 414 json_type = 'object'; 415 416SELECT 417 jsonb_object_keys(test_json) 418FROM 419 test_jsonb 420WHERE 421 json_type = 'scalar'; 422 423SELECT 424 jsonb_object_keys(test_json) 425FROM 426 test_jsonb 427WHERE 428 json_type = 'array'; 429 430SELECT 431 jsonb_object_keys(test_json) 432FROM 433 test_jsonb 434WHERE 435 json_type = 'object'; 436 437-- nulls 438SELECT 439 (test_json -> 'field3') IS NULL AS expect_false 440FROM 441 test_jsonb 442WHERE 443 json_type = 'object'; 444 445SELECT 446 (test_json ->> 'field3') IS NULL AS expect_true 447FROM 448 test_jsonb 449WHERE 450 json_type = 'object'; 451 452SELECT 453 (test_json -> 3) IS NULL AS expect_false 454FROM 455 test_jsonb 456WHERE 457 json_type = 'array'; 458 459SELECT 460 (test_json ->> 3) IS NULL AS expect_true 461FROM 462 test_jsonb 463WHERE 464 json_type = 'array'; 465 466-- corner cases 467SELECT 468 '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> NULL::text; 469 470SELECT 471 '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> NULL::int; 472 473SELECT 474 '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 1; 475 476SELECT 477 '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 'z'; 478 479SELECT 480 '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> ''; 481 482SELECT 483 '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 1; 484 485SELECT 486 '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 3; 487 488SELECT 489 '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 'z'; 490 491SELECT 492 '{"a": "c", "b": null}'::jsonb -> 'b'; 493 494SELECT 495 '"foo"'::jsonb -> 1; 496 497SELECT 498 '"foo"'::jsonb -> 'z'; 499 500SELECT 501 '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> NULL::text; 502 503SELECT 504 '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> NULL::int; 505 506SELECT 507 '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 1; 508 509SELECT 510 '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 'z'; 511 512SELECT 513 '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> ''; 514 515SELECT 516 '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 1; 517 518SELECT 519 '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 3; 520 521SELECT 522 '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 'z'; 523 524SELECT 525 '{"a": "c", "b": null}'::jsonb ->> 'b'; 526 527SELECT 528 '"foo"'::jsonb ->> 1; 529 530SELECT 531 '"foo"'::jsonb ->> 'z'; 532 533-- equality and inequality 534SELECT 535 '{"x":"y"}'::jsonb = '{"x":"y"}'::jsonb; 536 537SELECT 538 '{"x":"y"}'::jsonb = '{"x":"z"}'::jsonb; 539 540SELECT 541 '{"x":"y"}'::jsonb <> '{"x":"y"}'::jsonb; 542 543SELECT 544 '{"x":"y"}'::jsonb <> '{"x":"z"}'::jsonb; 545 546-- containment 547SELECT 548 jsonb_contains ('{"a":"b", "b":1, "c":null}', '{"a":"b"}'); 549 550SELECT 551 jsonb_contains ('{"a":"b", "b":1, "c":null}', '{"a":"b", "c":null}'); 552 553SELECT 554 jsonb_contains ('{"a":"b", "b":1, "c":null}', '{"a":"b", "g":null}'); 555 556SELECT 557 jsonb_contains ('{"a":"b", "b":1, "c":null}', '{"g":null}'); 558 559SELECT 560 jsonb_contains ('{"a":"b", "b":1, "c":null}', '{"a":"c"}'); 561 562SELECT 563 jsonb_contains ('{"a":"b", "b":1, "c":null}', '{"a":"b"}'); 564 565SELECT 566 jsonb_contains ('{"a":"b", "b":1, "c":null}', '{"a":"b", "c":"q"}'); 567 568SELECT 569 '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"b"}'; 570 571SELECT 572 '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"b", "c":null}'; 573 574SELECT 575 '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"b", "g":null}'; 576 577SELECT 578 '{"a":"b", "b":1, "c":null}'::jsonb @> '{"g":null}'; 579 580SELECT 581 '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"c"}'; 582 583SELECT 584 '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"b"}'; 585 586SELECT 587 '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"b", "c":"q"}'; 588 589SELECT 590 '[1,2]'::jsonb @> '[1,2,2]'::jsonb; 591 592SELECT 593 '[1,1,2]'::jsonb @> '[1,2,2]'::jsonb; 594 595SELECT 596 '[[1,2]]'::jsonb @> '[[1,2,2]]'::jsonb; 597 598SELECT 599 '[1,2,2]'::jsonb <@ '[1,2]'::jsonb; 600 601SELECT 602 '[1,2,2]'::jsonb <@ '[1,1,2]'::jsonb; 603 604SELECT 605 '[[1,2,2]]'::jsonb <@ '[[1,2]]'::jsonb; 606 607SELECT 608 jsonb_contained ('{"a":"b"}', '{"a":"b", "b":1, "c":null}'); 609 610SELECT 611 jsonb_contained ('{"a":"b", "c":null}', '{"a":"b", "b":1, "c":null}'); 612 613SELECT 614 jsonb_contained ('{"a":"b", "g":null}', '{"a":"b", "b":1, "c":null}'); 615 616SELECT 617 jsonb_contained ('{"g":null}', '{"a":"b", "b":1, "c":null}'); 618 619SELECT 620 jsonb_contained ('{"a":"c"}', '{"a":"b", "b":1, "c":null}'); 621 622SELECT 623 jsonb_contained ('{"a":"b"}', '{"a":"b", "b":1, "c":null}'); 624 625SELECT 626 jsonb_contained ('{"a":"b", "c":"q"}', '{"a":"b", "b":1, "c":null}'); 627 628SELECT 629 '{"a":"b"}'::jsonb <@ '{"a":"b", "b":1, "c":null}'; 630 631SELECT 632 '{"a":"b", "c":null}'::jsonb <@ '{"a":"b", "b":1, "c":null}'; 633 634SELECT 635 '{"a":"b", "g":null}'::jsonb <@ '{"a":"b", "b":1, "c":null}'; 636 637SELECT 638 '{"g":null}'::jsonb <@ '{"a":"b", "b":1, "c":null}'; 639 640SELECT 641 '{"a":"c"}'::jsonb <@ '{"a":"b", "b":1, "c":null}'; 642 643SELECT 644 '{"a":"b"}'::jsonb <@ '{"a":"b", "b":1, "c":null}'; 645 646SELECT 647 '{"a":"b", "c":"q"}'::jsonb <@ '{"a":"b", "b":1, "c":null}'; 648 649-- Raw scalar may contain another raw scalar, array may contain a raw scalar 650SELECT 651 '[5]'::jsonb @> '[5]'; 652 653SELECT 654 '5'::jsonb @> '5'; 655 656SELECT 657 '[5]'::jsonb @> '5'; 658 659-- But a raw scalar cannot contain an array 660SELECT 661 '5'::jsonb @> '[5]'; 662 663-- In general, one thing should always contain itself. Test array containment: 664SELECT 665 '["9", ["7", "3"], 1]'::jsonb @> '["9", ["7", "3"], 1]'::jsonb; 666 667SELECT 668 '["9", ["7", "3"], ["1"]]'::jsonb @> '["9", ["7", "3"], ["1"]]'::jsonb; 669 670-- array containment string matching confusion bug 671SELECT 672 '{ "name": "Bob", "tags": [ "enim", "qui"]}'::jsonb @> '{"tags":["qu"]}'; 673 674-- array length 675SELECT 676 jsonb_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]'); 677 678SELECT 679 jsonb_array_length('[]'); 680 681SELECT 682 jsonb_array_length('{"f1":1,"f2":[5,6]}'); 683 684SELECT 685 jsonb_array_length('4'); 686 687-- each 688SELECT 689 jsonb_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}'); 690 691SELECT 692 jsonb_each('{"a":{"b":"c","c":"b","1":"first"},"b":[1,2],"c":"cc","1":"first","n":null}'::jsonb) AS q; 693 694SELECT 695 * 696FROM 697 jsonb_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q; 698 699SELECT 700 * 701FROM 702 jsonb_each('{"a":{"b":"c","c":"b","1":"first"},"b":[1,2],"c":"cc","1":"first","n":null}'::jsonb) AS q; 703 704SELECT 705 jsonb_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":"null"}'); 706 707SELECT 708 jsonb_each_text('{"a":{"b":"c","c":"b","1":"first"},"b":[1,2],"c":"cc","1":"first","n":null}'::jsonb) AS q; 709 710SELECT 711 * 712FROM 713 jsonb_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q; 714 715SELECT 716 * 717FROM 718 jsonb_each_text('{"a":{"b":"c","c":"b","1":"first"},"b":[1,2],"c":"cc","1":"first","n":null}'::jsonb) AS q; 719 720-- exists 721SELECT 722 jsonb_exists ('{"a":null, "b":"qq"}', 'a'); 723 724SELECT 725 jsonb_exists ('{"a":null, "b":"qq"}', 'b'); 726 727SELECT 728 jsonb_exists ('{"a":null, "b":"qq"}', 'c'); 729 730SELECT 731 jsonb_exists ('{"a":"null", "b":"qq"}', 'a'); 732 733SELECT 734 jsonb '{"a":null, "b":"qq"}' ? 'a'; 735 736SELECT 737 jsonb '{"a":null, "b":"qq"}' ? 'b'; 738 739SELECT 740 jsonb '{"a":null, "b":"qq"}' ? 'c'; 741 742SELECT 743 jsonb '{"a":"null", "b":"qq"}' ? 'a'; 744 745-- array exists - array elements should behave as keys 746SELECT 747 count(*) 748FROM 749 testjsonb 750WHERE 751 j -> 'array' ? 'bar'; 752 753-- type sensitive array exists - should return no rows (since "exists" only 754-- matches strings that are either object keys or array elements) 755SELECT 756 count(*) 757FROM 758 testjsonb 759WHERE 760 j -> 'array' ? '5'::text; 761 762-- However, a raw scalar is *contained* within the array 763SELECT 764 count(*) 765FROM 766 testjsonb 767WHERE 768 j -> 'array' @> '5'::jsonb; 769 770SELECT 771 jsonb_exists_any ('{"a":null, "b":"qq"}', ARRAY['a', 'b']); 772 773SELECT 774 jsonb_exists_any ('{"a":null, "b":"qq"}', ARRAY['b', 'a']); 775 776SELECT 777 jsonb_exists_any ('{"a":null, "b":"qq"}', ARRAY['c', 'a']); 778 779SELECT 780 jsonb_exists_any ('{"a":null, "b":"qq"}', ARRAY['c', 'd']); 781 782SELECT 783 jsonb_exists_any ('{"a":null, "b":"qq"}', '{}'::text[]); 784 785SELECT 786 jsonb '{"a":null, "b":"qq"}' ? | ARRAY['a', 'b']; 787 788SELECT 789 jsonb '{"a":null, "b":"qq"}' ? | ARRAY['b', 'a']; 790 791SELECT 792 jsonb '{"a":null, "b":"qq"}' ? | ARRAY['c', 'a']; 793 794SELECT 795 jsonb '{"a":null, "b":"qq"}' ? | ARRAY['c', 'd']; 796 797SELECT 798 jsonb '{"a":null, "b":"qq"}' ? | '{}'::text[]; 799 800SELECT 801 jsonb_exists_all ('{"a":null, "b":"qq"}', ARRAY['a', 'b']); 802 803SELECT 804 jsonb_exists_all ('{"a":null, "b":"qq"}', ARRAY['b', 'a']); 805 806SELECT 807 jsonb_exists_all ('{"a":null, "b":"qq"}', ARRAY['c', 'a']); 808 809SELECT 810 jsonb_exists_all ('{"a":null, "b":"qq"}', ARRAY['c', 'd']); 811 812SELECT 813 jsonb_exists_all ('{"a":null, "b":"qq"}', '{}'::text[]); 814 815SELECT 816 jsonb '{"a":null, "b":"qq"}' ?& ARRAY['a', 'b']; 817 818SELECT 819 jsonb '{"a":null, "b":"qq"}' ?& ARRAY['b', 'a']; 820 821SELECT 822 jsonb '{"a":null, "b":"qq"}' ?& ARRAY['c', 'a']; 823 824SELECT 825 jsonb '{"a":null, "b":"qq"}' ?& ARRAY['c', 'd']; 826 827SELECT 828 jsonb '{"a":null, "b":"qq"}' ?& ARRAY['a', 'a', 'b', 'b', 'b']; 829 830SELECT 831 jsonb '{"a":null, "b":"qq"}' ?& '{}'::text[]; 832 833-- typeof 834SELECT 835 jsonb_typeof('{}') AS object; 836 837SELECT 838 jsonb_typeof('{"c":3,"p":"o"}') AS object; 839 840SELECT 841 jsonb_typeof('[]') AS array; 842 843SELECT 844 jsonb_typeof('["a", 1]') AS array; 845 846SELECT 847 jsonb_typeof('null') AS "null"; 848 849SELECT 850 jsonb_typeof('1') AS number; 851 852SELECT 853 jsonb_typeof('-1') AS number; 854 855SELECT 856 jsonb_typeof('1.0') AS number; 857 858SELECT 859 jsonb_typeof('1e2') AS number; 860 861SELECT 862 jsonb_typeof('-1.0') AS number; 863 864SELECT 865 jsonb_typeof('true') AS boolean; 866 867SELECT 868 jsonb_typeof('false') AS boolean; 869 870SELECT 871 jsonb_typeof('"hello"') AS string; 872 873SELECT 874 jsonb_typeof('"true"') AS string; 875 876SELECT 877 jsonb_typeof('"1.0"') AS string; 878 879-- jsonb_build_array, jsonb_build_object, jsonb_object_agg 880SELECT 881 jsonb_build_array('a', 1, 'b', 1.2, 'c', TRUE, 'd', NULL, 'e', json '{"x": 3, "y": [1,2,3]}'); 882 883SELECT 884 jsonb_build_array('a', NULL); 885 886-- ok 887SELECT 888 jsonb_build_array(VARIADIC NULL::text[]); 889 890-- ok 891SELECT 892 jsonb_build_array(VARIADIC '{}'::text[]); 893 894-- ok 895SELECT 896 jsonb_build_array(VARIADIC '{a,b,c}'::text[]); 897 898-- ok 899SELECT 900 jsonb_build_array(VARIADIC ARRAY['a', NULL]::text[]); 901 902-- ok 903SELECT 904 jsonb_build_array(VARIADIC '{1,2,3,4}'::text[]); 905 906-- ok 907SELECT 908 jsonb_build_array(VARIADIC '{1,2,3,4}'::int[]); 909 910-- ok 911SELECT 912 jsonb_build_array(VARIADIC '{{1,4},{2,5},{3,6}}'::int[][]); 913 914-- ok 915SELECT 916 jsonb_build_object('a', 1, 'b', 1.2, 'c', TRUE, 'd', NULL, 'e', json '{"x": 3, "y": [1,2,3]}'); 917 918SELECT 919 jsonb_build_object('a', jsonb_build_object('b', FALSE, 'c', 99), 'd', jsonb_build_object('e', ARRAY[9, 8, 7]::int[], 'f', ( 920 SELECT 921 row_to_json(r) 922 FROM ( 923 SELECT 924 relkind, oid::regclass AS name 925 FROM pg_class 926 WHERE 927 relname = 'pg_class') r))); 928 929SELECT 930 jsonb_build_object('{a,b,c}'::text[]); 931 932-- error 933SELECT 934 jsonb_build_object('{a,b,c}'::text[], '{d,e,f}'::text[]); 935 936-- error, key cannot be array 937SELECT 938 jsonb_build_object('a', 'b', 'c'); 939 940-- error 941SELECT 942 jsonb_build_object(NULL, 'a'); 943 944-- error, key cannot be NULL 945SELECT 946 jsonb_build_object('a', NULL); 947 948-- ok 949SELECT 950 jsonb_build_object(VARIADIC NULL::text[]); 951 952-- ok 953SELECT 954 jsonb_build_object(VARIADIC '{}'::text[]); 955 956-- ok 957SELECT 958 jsonb_build_object(VARIADIC '{a,b,c}'::text[]); 959 960-- error 961SELECT 962 jsonb_build_object(VARIADIC ARRAY['a', NULL]::text[]); 963 964-- ok 965SELECT 966 jsonb_build_object(VARIADIC ARRAY[NULL, 'a']::text[]); 967 968-- error, key cannot be NULL 969SELECT 970 jsonb_build_object(VARIADIC '{1,2,3,4}'::text[]); 971 972-- ok 973SELECT 974 jsonb_build_object(VARIADIC '{1,2,3,4}'::int[]); 975 976-- ok 977SELECT 978 jsonb_build_object(VARIADIC '{{1,4},{2,5},{3,6}}'::int[][]); 979 980-- ok 981-- empty objects/arrays 982SELECT 983 jsonb_build_array(); 984 985SELECT 986 jsonb_build_object(); 987 988-- make sure keys are quoted 989SELECT 990 jsonb_build_object(1, 2); 991 992-- keys must be scalar and not null 993SELECT 994 jsonb_build_object(NULL, 2); 995 996SELECT 997 jsonb_build_object(r, 2) 998FROM ( 999 SELECT 1000 1 AS a, 1001 2 AS b) r; 1002 1003SELECT 1004 jsonb_build_object(json '{"a":1,"b":2}', 3); 1005 1006SELECT 1007 jsonb_build_object('{1,2,3}'::int[], 3); 1008 1009-- handling of NULL values 1010SELECT 1011 jsonb_object_agg(1, NULL::jsonb); 1012 1013SELECT 1014 jsonb_object_agg(NULL, '{"a":1}'); 1015 1016CREATE TEMP TABLE foo ( 1017 serial_num int, 1018 name text, 1019 type text 1020); 1021 1022INSERT INTO foo 1023 VALUES (847001, 't15', 'GE1043'); 1024 1025INSERT INTO foo 1026 VALUES (847002, 't16', 'GE1043'); 1027 1028INSERT INTO foo 1029 VALUES (847003, 'sub-alpha', 'GESS90'); 1030 1031SELECT 1032 jsonb_build_object('turbines', jsonb_object_agg(serial_num, jsonb_build_object('name', name, 'type', type))) 1033FROM 1034 foo; 1035 1036SELECT 1037 jsonb_object_agg(name, type) 1038FROM 1039 foo; 1040 1041INSERT INTO foo 1042 VALUES (999999, NULL, 'bar'); 1043 1044SELECT 1045 jsonb_object_agg(name, type) 1046FROM 1047 foo; 1048 1049-- jsonb_object 1050-- empty object, one dimension 1051SELECT 1052 jsonb_object('{}'); 1053 1054-- empty object, two dimensions 1055SELECT 1056 jsonb_object('{}', '{}'); 1057 1058-- one dimension 1059SELECT 1060 jsonb_object('{a,1,b,2,3,NULL,"d e f","a b c"}'); 1061 1062-- same but with two dimensions 1063SELECT 1064 jsonb_object('{{a,1},{b,2},{3,NULL},{"d e f","a b c"}}'); 1065 1066-- odd number error 1067SELECT 1068 jsonb_object('{a,b,c}'); 1069 1070-- one column error 1071SELECT 1072 jsonb_object('{{a},{b}}'); 1073 1074-- too many columns error 1075SELECT 1076 jsonb_object('{{a,b,c},{b,c,d}}'); 1077 1078-- too many dimensions error 1079SELECT 1080 jsonb_object('{{{a,b},{c,d}},{{b,c},{d,e}}}'); 1081 1082--two argument form of jsonb_object 1083SELECT 1084 jsonb_object('{a,b,c,"d e f"}', '{1,2,3,"a b c"}'); 1085 1086-- too many dimensions 1087SELECT 1088 jsonb_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"}}'); 1089 1090-- mismatched dimensions 1091SELECT 1092 jsonb_object('{a,b,c,"d e f",g}', '{1,2,3,"a b c"}'); 1093 1094SELECT 1095 jsonb_object('{a,b,c,"d e f"}', '{1,2,3,"a b c",g}'); 1096 1097-- null key error 1098SELECT 1099 jsonb_object('{a,b,NULL,"d e f"}', '{1,2,3,"a b c"}'); 1100 1101-- empty key is allowed 1102SELECT 1103 jsonb_object('{a,b,"","d e f"}', '{1,2,3,"a b c"}'); 1104 1105-- extract_path, extract_path_as_text 1106SELECT 1107 jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4', 'f6'); 1108 1109SELECT 1110 jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f2'); 1111 1112SELECT 1113 jsonb_extract_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}', 'f2', 0::text); 1114 1115SELECT 1116 jsonb_extract_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}', 'f2', 1::text); 1117 1118SELECT 1119 jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4', 'f6'); 1120 1121SELECT 1122 jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f2'); 1123 1124SELECT 1125 jsonb_extract_path_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}', 'f2', 0::text); 1126 1127SELECT 1128 jsonb_extract_path_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}', 'f2', 1::text); 1129 1130-- extract_path nulls 1131SELECT 1132 jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}', 'f4', 'f5') IS NULL AS expect_false; 1133 1134SELECT 1135 jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}', 'f4', 'f5') IS NULL AS expect_true; 1136 1137SELECT 1138 jsonb_extract_path('{"f2":{"f3":1},"f4":[0,1,2,null]}', 'f4', '3') IS NULL AS expect_false; 1139 1140SELECT 1141 jsonb_extract_path_text('{"f2":{"f3":1},"f4":[0,1,2,null]}', 'f4', '3') IS NULL AS expect_true; 1142 1143-- extract_path operators 1144SELECT 1145 '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb #> ARRAY['f4', 'f6']; 1146 1147SELECT 1148 '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb #> ARRAY['f2']; 1149 1150SELECT 1151 '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb #> ARRAY['f2', '0']; 1152 1153SELECT 1154 '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb #> ARRAY['f2', '1']; 1155 1156SELECT 1157 '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb #>> ARRAY['f4', 'f6']; 1158 1159SELECT 1160 '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb #>> ARRAY['f2']; 1161 1162SELECT 1163 '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb #>> ARRAY['f2', '0']; 1164 1165SELECT 1166 '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb #>> ARRAY['f2', '1']; 1167 1168-- corner cases for same 1169SELECT 1170 '{"a": {"b":{"c": "foo"}}}'::jsonb #> '{}'; 1171 1172SELECT 1173 '[1,2,3]'::jsonb #> '{}'; 1174 1175SELECT 1176 '"foo"'::jsonb #> '{}'; 1177 1178SELECT 1179 '42'::jsonb #> '{}'; 1180 1181SELECT 1182 'null'::jsonb #> '{}'; 1183 1184SELECT 1185 '{"a": {"b":{"c": "foo"}}}'::jsonb #> ARRAY['a']; 1186 1187SELECT 1188 '{"a": {"b":{"c": "foo"}}}'::jsonb #> ARRAY['a', NULL]; 1189 1190SELECT 1191 '{"a": {"b":{"c": "foo"}}}'::jsonb #> ARRAY['a', '']; 1192 1193SELECT 1194 '{"a": {"b":{"c": "foo"}}}'::jsonb #> ARRAY['a', 'b']; 1195 1196SELECT 1197 '{"a": {"b":{"c": "foo"}}}'::jsonb #> ARRAY['a', 'b', 'c']; 1198 1199SELECT 1200 '{"a": {"b":{"c": "foo"}}}'::jsonb #> ARRAY['a', 'b', 'c', 'd']; 1201 1202SELECT 1203 '{"a": {"b":{"c": "foo"}}}'::jsonb #> ARRAY['a', 'z', 'c']; 1204 1205SELECT 1206 '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #> ARRAY['a', '1', 'b']; 1207 1208SELECT 1209 '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #> ARRAY['a', 'z', 'b']; 1210 1211SELECT 1212 '[{"b": "c"}, {"b": "cc"}]'::jsonb #> ARRAY['1', 'b']; 1213 1214SELECT 1215 '[{"b": "c"}, {"b": "cc"}]'::jsonb #> ARRAY['z', 'b']; 1216 1217SELECT 1218 '[{"b": "c"}, {"b": null}]'::jsonb #> ARRAY['1', 'b']; 1219 1220SELECT 1221 '"foo"'::jsonb #> ARRAY['z']; 1222 1223SELECT 1224 '42'::jsonb #> ARRAY['f2']; 1225 1226SELECT 1227 '42'::jsonb #> ARRAY['0']; 1228 1229SELECT 1230 '{"a": {"b":{"c": "foo"}}}'::jsonb #>> '{}'; 1231 1232SELECT 1233 '[1,2,3]'::jsonb #>> '{}'; 1234 1235SELECT 1236 '"foo"'::jsonb #>> '{}'; 1237 1238SELECT 1239 '42'::jsonb #>> '{}'; 1240 1241SELECT 1242 'null'::jsonb #>> '{}'; 1243 1244SELECT 1245 '{"a": {"b":{"c": "foo"}}}'::jsonb #>> ARRAY['a']; 1246 1247SELECT 1248 '{"a": {"b":{"c": "foo"}}}'::jsonb #>> ARRAY['a', NULL]; 1249 1250SELECT 1251 '{"a": {"b":{"c": "foo"}}}'::jsonb #>> ARRAY['a', '']; 1252 1253SELECT 1254 '{"a": {"b":{"c": "foo"}}}'::jsonb #>> ARRAY['a', 'b']; 1255 1256SELECT 1257 '{"a": {"b":{"c": "foo"}}}'::jsonb #>> ARRAY['a', 'b', 'c']; 1258 1259SELECT 1260 '{"a": {"b":{"c": "foo"}}}'::jsonb #>> ARRAY['a', 'b', 'c', 'd']; 1261 1262SELECT 1263 '{"a": {"b":{"c": "foo"}}}'::jsonb #>> ARRAY['a', 'z', 'c']; 1264 1265SELECT 1266 '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #>> ARRAY['a', '1', 'b']; 1267 1268SELECT 1269 '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #>> ARRAY['a', 'z', 'b']; 1270 1271SELECT 1272 '[{"b": "c"}, {"b": "cc"}]'::jsonb #>> ARRAY['1', 'b']; 1273 1274SELECT 1275 '[{"b": "c"}, {"b": "cc"}]'::jsonb #>> ARRAY['z', 'b']; 1276 1277SELECT 1278 '[{"b": "c"}, {"b": null}]'::jsonb #>> ARRAY['1', 'b']; 1279 1280SELECT 1281 '"foo"'::jsonb #>> ARRAY['z']; 1282 1283SELECT 1284 '42'::jsonb #>> ARRAY['f2']; 1285 1286SELECT 1287 '42'::jsonb #>> ARRAY['0']; 1288 1289-- array_elements 1290SELECT 1291 jsonb_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]'); 1292 1293SELECT 1294 * 1295FROM 1296 jsonb_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]') q; 1297 1298SELECT 1299 jsonb_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]'); 1300 1301SELECT 1302 * 1303FROM 1304 jsonb_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q; 1305 1306-- populate_record 1307CREATE TYPE jbpop AS ( 1308 a text, 1309 b int, 1310 c timestamp 1311); 1312 1313CREATE DOMAIN jsb_int_not_null AS int NOT NULL; 1314 1315CREATE DOMAIN jsb_int_array_1d AS int[] CHECK (array_length(VALUE, 1) = 3); 1316 1317CREATE DOMAIN jsb_int_array_2d AS int[][] CHECK (array_length(VALUE, 2) = 3); 1318 1319CREATE TYPE jb_unordered_pair AS ( 1320 x int, 1321 y int 1322); 1323 1324CREATE DOMAIN jb_ordered_pair AS jb_unordered_pair CHECK ((value).x <= (value).y); 1325 1326CREATE TYPE jsbrec AS ( 1327 i int, 1328 ia _int4, 1329 ia1 int[], 1330 ia2 int[][], 1331 ia3 int[][][], 1332 ia1d jsb_int_array_1d, 1333 ia2d jsb_int_array_2d, 1334 t text, 1335 ta text[], 1336 c char ( 10), 1337 ca char(10)[], 1338 ts timestamp, 1339 js json, 1340 jsb jsonb, 1341 jsa json[], 1342 rec jbpop, 1343 reca jbpop[]); 1344 1345CREATE TYPE jsbrec_i_not_null AS ( 1346 i jsb_int_not_null 1347); 1348 1349SELECT 1350 * 1351FROM 1352 jsonb_populate_record(NULL::jbpop, '{"a":"blurfl","x":43.2}') q; 1353 1354SELECT 1355 * 1356FROM 1357 jsonb_populate_record(ROW ('x', 3, '2012-12-31 15:30:56')::jbpop, '{"a":"blurfl","x":43.2}') q; 1358 1359SELECT 1360 * 1361FROM 1362 jsonb_populate_record(NULL::jbpop, '{"a":"blurfl","x":43.2}') q; 1363 1364SELECT 1365 * 1366FROM 1367 jsonb_populate_record(ROW ('x', 3, '2012-12-31 15:30:56')::jbpop, '{"a":"blurfl","x":43.2}') q; 1368 1369SELECT 1370 * 1371FROM 1372 jsonb_populate_record(NULL::jbpop, '{"a":[100,200,false],"x":43.2}') q; 1373 1374SELECT 1375 * 1376FROM 1377 jsonb_populate_record(ROW ('x', 3, '2012-12-31 15:30:56')::jbpop, '{"a":[100,200,false],"x":43.2}') q; 1378 1379SELECT 1380 * 1381FROM 1382 jsonb_populate_record(ROW ('x', 3, '2012-12-31 15:30:56')::jbpop, '{"c":[100,200,false],"x":43.2}') q; 1383 1384SELECT 1385 * 1386FROM 1387 jsonb_populate_record(ROW ('x', 3, '2012-12-31 15:30:56')::jbpop, '{}') q; 1388 1389SELECT 1390 i 1391FROM 1392 jsonb_populate_record(NULL::jsbrec_i_not_null, '{"x": 43.2}') q; 1393 1394SELECT 1395 i 1396FROM 1397 jsonb_populate_record(NULL::jsbrec_i_not_null, '{"i": null}') q; 1398 1399SELECT 1400 i 1401FROM 1402 jsonb_populate_record(NULL::jsbrec_i_not_null, '{"i": 12345}') q; 1403 1404SELECT 1405 ia 1406FROM 1407 jsonb_populate_record(NULL::jsbrec, '{"ia": null}') q; 1408 1409SELECT 1410 ia 1411FROM 1412 jsonb_populate_record(NULL::jsbrec, '{"ia": 123}') q; 1413 1414SELECT 1415 ia 1416FROM 1417 jsonb_populate_record(NULL::jsbrec, '{"ia": [1, "2", null, 4]}') q; 1418 1419SELECT 1420 ia 1421FROM 1422 jsonb_populate_record(NULL::jsbrec, '{"ia": [[1, 2], [3, 4]]}') q; 1423 1424SELECT 1425 ia 1426FROM 1427 jsonb_populate_record(NULL::jsbrec, '{"ia": [[1], 2]}') q; 1428 1429SELECT 1430 ia 1431FROM 1432 jsonb_populate_record(NULL::jsbrec, '{"ia": [[1], [2, 3]]}') q; 1433 1434SELECT 1435 ia 1436FROM 1437 jsonb_populate_record(NULL::jsbrec, '{"ia": "{1,2,3}"}') q; 1438 1439SELECT 1440 ia1 1441FROM 1442 jsonb_populate_record(NULL::jsbrec, '{"ia1": null}') q; 1443 1444SELECT 1445 ia1 1446FROM 1447 jsonb_populate_record(NULL::jsbrec, '{"ia1": 123}') q; 1448 1449SELECT 1450 ia1 1451FROM 1452 jsonb_populate_record(NULL::jsbrec, '{"ia1": [1, "2", null, 4]}') q; 1453 1454SELECT 1455 ia1 1456FROM 1457 jsonb_populate_record(NULL::jsbrec, '{"ia1": [[1, 2, 3]]}') q; 1458 1459SELECT 1460 ia1d 1461FROM 1462 jsonb_populate_record(NULL::jsbrec, '{"ia1d": null}') q; 1463 1464SELECT 1465 ia1d 1466FROM 1467 jsonb_populate_record(NULL::jsbrec, '{"ia1d": 123}') q; 1468 1469SELECT 1470 ia1d 1471FROM 1472 jsonb_populate_record(NULL::jsbrec, '{"ia1d": [1, "2", null, 4]}') q; 1473 1474SELECT 1475 ia1d 1476FROM 1477 jsonb_populate_record(NULL::jsbrec, '{"ia1d": [1, "2", null]}') q; 1478 1479SELECT 1480 ia2 1481FROM 1482 jsonb_populate_record(NULL::jsbrec, '{"ia2": [1, "2", null, 4]}') q; 1483 1484SELECT 1485 ia2 1486FROM 1487 jsonb_populate_record(NULL::jsbrec, '{"ia2": [[1, 2], [null, 4]]}') q; 1488 1489SELECT 1490 ia2 1491FROM 1492 jsonb_populate_record(NULL::jsbrec, '{"ia2": [[], []]}') q; 1493 1494SELECT 1495 ia2 1496FROM 1497 jsonb_populate_record(NULL::jsbrec, '{"ia2": [[1, 2], [3]]}') q; 1498 1499SELECT 1500 ia2 1501FROM 1502 jsonb_populate_record(NULL::jsbrec, '{"ia2": [[1, 2], 3, 4]}') q; 1503 1504SELECT 1505 ia2d 1506FROM 1507 jsonb_populate_record(NULL::jsbrec, '{"ia2d": [[1, "2"], [null, 4]]}') q; 1508 1509SELECT 1510 ia2d 1511FROM 1512 jsonb_populate_record(NULL::jsbrec, '{"ia2d": [[1, "2", 3], [null, 5, 6]]}') q; 1513 1514SELECT 1515 ia3 1516FROM 1517 jsonb_populate_record(NULL::jsbrec, '{"ia3": [1, "2", null, 4]}') q; 1518 1519SELECT 1520 ia3 1521FROM 1522 jsonb_populate_record(NULL::jsbrec, '{"ia3": [[1, 2], [null, 4]]}') q; 1523 1524SELECT 1525 ia3 1526FROM 1527 jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[], []], [[], []], [[], []] ]}') q; 1528 1529SELECT 1530 ia3 1531FROM 1532 jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[1, 2]], [[3, 4]] ]}') q; 1533 1534SELECT 1535 ia3 1536FROM 1537 jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8]] ]}') q; 1538 1539SELECT 1540 ia3 1541FROM 1542 jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8], [9, 10]] ]}') q; 1543 1544SELECT 1545 ta 1546FROM 1547 jsonb_populate_record(NULL::jsbrec, '{"ta": null}') q; 1548 1549SELECT 1550 ta 1551FROM 1552 jsonb_populate_record(NULL::jsbrec, '{"ta": 123}') q; 1553 1554SELECT 1555 ta 1556FROM 1557 jsonb_populate_record(NULL::jsbrec, '{"ta": [1, "2", null, 4]}') q; 1558 1559SELECT 1560 ta 1561FROM 1562 jsonb_populate_record(NULL::jsbrec, '{"ta": [[1, 2, 3], {"k": "v"}]}') q; 1563 1564SELECT 1565 c 1566FROM 1567 jsonb_populate_record(NULL::jsbrec, '{"c": null}') q; 1568 1569SELECT 1570 c 1571FROM 1572 jsonb_populate_record(NULL::jsbrec, '{"c": "aaa"}') q; 1573 1574SELECT 1575 c 1576FROM 1577 jsonb_populate_record(NULL::jsbrec, '{"c": "aaaaaaaaaa"}') q; 1578 1579SELECT 1580 c 1581FROM 1582 jsonb_populate_record(NULL::jsbrec, '{"c": "aaaaaaaaaaaaa"}') q; 1583 1584SELECT 1585 ca 1586FROM 1587 jsonb_populate_record(NULL::jsbrec, '{"ca": null}') q; 1588 1589SELECT 1590 ca 1591FROM 1592 jsonb_populate_record(NULL::jsbrec, '{"ca": 123}') q; 1593 1594SELECT 1595 ca 1596FROM 1597 jsonb_populate_record(NULL::jsbrec, '{"ca": [1, "2", null, 4]}') q; 1598 1599SELECT 1600 ca 1601FROM 1602 jsonb_populate_record(NULL::jsbrec, '{"ca": ["aaaaaaaaaaaaaaaa"]}') q; 1603 1604SELECT 1605 ca 1606FROM 1607 jsonb_populate_record(NULL::jsbrec, '{"ca": [[1, 2, 3], {"k": "v"}]}') q; 1608 1609SELECT 1610 js 1611FROM 1612 jsonb_populate_record(NULL::jsbrec, '{"js": null}') q; 1613 1614SELECT 1615 js 1616FROM 1617 jsonb_populate_record(NULL::jsbrec, '{"js": true}') q; 1618 1619SELECT 1620 js 1621FROM 1622 jsonb_populate_record(NULL::jsbrec, '{"js": 123.45}') q; 1623 1624SELECT 1625 js 1626FROM 1627 jsonb_populate_record(NULL::jsbrec, '{"js": "123.45"}') q; 1628 1629SELECT 1630 js 1631FROM 1632 jsonb_populate_record(NULL::jsbrec, '{"js": "abc"}') q; 1633 1634SELECT 1635 js 1636FROM 1637 jsonb_populate_record(NULL::jsbrec, '{"js": [123, "123", null, {"key": "value"}]}') q; 1638 1639SELECT 1640 js 1641FROM 1642 jsonb_populate_record(NULL::jsbrec, '{"js": {"a": "bbb", "b": null, "c": 123.45}}') q; 1643 1644SELECT 1645 jsb 1646FROM 1647 jsonb_populate_record(NULL::jsbrec, '{"jsb": null}') q; 1648 1649SELECT 1650 jsb 1651FROM 1652 jsonb_populate_record(NULL::jsbrec, '{"jsb": true}') q; 1653 1654SELECT 1655 jsb 1656FROM 1657 jsonb_populate_record(NULL::jsbrec, '{"jsb": 123.45}') q; 1658 1659SELECT 1660 jsb 1661FROM 1662 jsonb_populate_record(NULL::jsbrec, '{"jsb": "123.45"}') q; 1663 1664SELECT 1665 jsb 1666FROM 1667 jsonb_populate_record(NULL::jsbrec, '{"jsb": "abc"}') q; 1668 1669SELECT 1670 jsb 1671FROM 1672 jsonb_populate_record(NULL::jsbrec, '{"jsb": [123, "123", null, {"key": "value"}]}') q; 1673 1674SELECT 1675 jsb 1676FROM 1677 jsonb_populate_record(NULL::jsbrec, '{"jsb": {"a": "bbb", "b": null, "c": 123.45}}') q; 1678 1679SELECT 1680 jsa 1681FROM 1682 jsonb_populate_record(NULL::jsbrec, '{"jsa": null}') q; 1683 1684SELECT 1685 jsa 1686FROM 1687 jsonb_populate_record(NULL::jsbrec, '{"jsa": 123}') q; 1688 1689SELECT 1690 jsa 1691FROM 1692 jsonb_populate_record(NULL::jsbrec, '{"jsa": [1, "2", null, 4]}') q; 1693 1694SELECT 1695 jsa 1696FROM 1697 jsonb_populate_record(NULL::jsbrec, '{"jsa": ["aaa", null, [1, 2, "3", {}], { "k" : "v" }]}') q; 1698 1699SELECT 1700 rec 1701FROM 1702 jsonb_populate_record(NULL::jsbrec, '{"rec": 123}') q; 1703 1704SELECT 1705 rec 1706FROM 1707 jsonb_populate_record(NULL::jsbrec, '{"rec": [1, 2]}') q; 1708 1709SELECT 1710 rec 1711FROM 1712 jsonb_populate_record(NULL::jsbrec, '{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}') q; 1713 1714SELECT 1715 rec 1716FROM 1717 jsonb_populate_record(NULL::jsbrec, '{"rec": "(abc,42,01.02.2003)"}') q; 1718 1719SELECT 1720 reca 1721FROM 1722 jsonb_populate_record(NULL::jsbrec, '{"reca": 123}') q; 1723 1724SELECT 1725 reca 1726FROM 1727 jsonb_populate_record(NULL::jsbrec, '{"reca": [1, 2]}') q; 1728 1729SELECT 1730 reca 1731FROM 1732 jsonb_populate_record(NULL::jsbrec, '{"reca": [{"a": "abc", "b": 456}, null, {"c": "01.02.2003", "x": 43.2}]}') q; 1733 1734SELECT 1735 reca 1736FROM 1737 jsonb_populate_record(NULL::jsbrec, '{"reca": ["(abc,42,01.02.2003)"]}') q; 1738 1739SELECT 1740 reca 1741FROM 1742 jsonb_populate_record(NULL::jsbrec, '{"reca": "{\"(abc,42,01.02.2003)\"}"}') q; 1743 1744SELECT 1745 rec 1746FROM 1747 jsonb_populate_record(ROW (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ROW ('x', 3, '2012-12-31 15:30:56')::jbpop, NULL)::jsbrec, '{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}') q; 1748 1749-- anonymous record type 1750SELECT 1751 jsonb_populate_record(NULL::record, '{"x": 0, "y": 1}'); 1752 1753SELECT 1754 jsonb_populate_record(ROW (1, 2), '{"f1": 0, "f2": 1}'); 1755 1756-- composite domain 1757SELECT 1758 jsonb_populate_record(NULL::jb_ordered_pair, '{"x": 0, "y": 1}'); 1759 1760SELECT 1761 jsonb_populate_record(ROW (1, 2)::jb_ordered_pair, '{"x": 0}'); 1762 1763SELECT 1764 jsonb_populate_record(ROW (1, 2)::jb_ordered_pair, '{"x": 1, "y": 0}'); 1765 1766-- populate_recordset 1767SELECT 1768 * 1769FROM 1770 jsonb_populate_recordset(NULL::jbpop, '[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 1771 1772SELECT 1773 * 1774FROM 1775 jsonb_populate_recordset(ROW ('def', 99, NULL)::jbpop, '[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 1776 1777SELECT 1778 * 1779FROM 1780 jsonb_populate_recordset(NULL::jbpop, '[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 1781 1782SELECT 1783 * 1784FROM 1785 jsonb_populate_recordset(ROW ('def', 99, NULL)::jbpop, '[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 1786 1787SELECT 1788 * 1789FROM 1790 jsonb_populate_recordset(ROW ('def', 99, NULL)::jbpop, '[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; 1791 1792SELECT 1793 * 1794FROM 1795 jsonb_populate_recordset(ROW ('def', 99, NULL)::jbpop, '[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; 1796 1797SELECT 1798 * 1799FROM 1800 jsonb_populate_recordset(NULL::jbpop, '[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 1801 1802SELECT 1803 * 1804FROM 1805 jsonb_populate_recordset(ROW ('def', 99, NULL)::jbpop, '[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 1806 1807SELECT 1808 * 1809FROM 1810 jsonb_populate_recordset(ROW ('def', 99, NULL)::jbpop, '[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; 1811 1812-- anonymous record type 1813SELECT 1814 jsonb_populate_recordset(NULL::record, '[{"x": 0, "y": 1}]'); 1815 1816SELECT 1817 jsonb_populate_recordset(ROW (1, 2), '[{"f1": 0, "f2": 1}]'); 1818 1819SELECT 1820 i, 1821 jsonb_populate_recordset(ROW (i, 50), '[{"f1":"42"},{"f2":"43"}]') 1822FROM ( 1823 VALUES (1), 1824 (2)) v (i); 1825 1826-- empty array is a corner case 1827SELECT 1828 jsonb_populate_recordset(NULL::record, '[]'); 1829 1830SELECT 1831 jsonb_populate_recordset(ROW (1, 2), '[]'); 1832 1833SELECT 1834 * 1835FROM 1836 jsonb_populate_recordset(NULL::jbpop, '[]') q; 1837 1838-- composite domain 1839SELECT 1840 jsonb_populate_recordset(NULL::jb_ordered_pair, '[{"x": 0, "y": 1}]'); 1841 1842SELECT 1843 jsonb_populate_recordset(ROW (1, 2)::jb_ordered_pair, '[{"x": 0}, {"y": 3}]'); 1844 1845SELECT 1846 jsonb_populate_recordset(ROW (1, 2)::jb_ordered_pair, '[{"x": 1, "y": 0}]'); 1847 1848-- negative cases where the wrong record type is supplied 1849SELECT 1850 * 1851FROM 1852 jsonb_populate_recordset(ROW (0::int), '[{"a":"1","b":"2"},{"a":"3"}]') q (a text, 1853 b text); 1854 1855SELECT 1856 * 1857FROM 1858 jsonb_populate_recordset(ROW (0::int, 0::int), '[{"a":"1","b":"2"},{"a":"3"}]') q (a text, 1859 b text); 1860 1861SELECT 1862 * 1863FROM 1864 jsonb_populate_recordset(ROW (0::int, 0::int, 0::int), '[{"a":"1","b":"2"},{"a":"3"}]') q (a text, 1865 b text); 1866 1867SELECT 1868 * 1869FROM 1870 jsonb_populate_recordset(ROW (1000000000::int, 50::int), '[{"b":"2"},{"a":"3"}]') q (a text, 1871 b text); 1872 1873-- jsonb_to_record and jsonb_to_recordset 1874SELECT 1875 * 1876FROM 1877 jsonb_to_record('{"a":1,"b":"foo","c":"bar"}') AS x (a int, 1878 b text, 1879 d text); 1880 1881SELECT 1882 * 1883FROM 1884 jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]') AS x (a int, 1885 b text, 1886 c boolean); 1887 1888SELECT 1889 *, 1890 c IS NULL AS c_is_null 1891FROM 1892 jsonb_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}}'::jsonb) AS t (a int, 1893 b jsonb, 1894 c text, 1895 x int, 1896 ca char(5)[], 1897 ia int[][], 1898 r jbpop); 1899 1900SELECT 1901 *, 1902 c IS NULL AS c_is_null 1903FROM 1904 jsonb_to_recordset('[{"a":1, "b":{"c":16, "d":2}, "x":8}]'::jsonb) AS t (a int, 1905 b jsonb, 1906 c text, 1907 x int); 1908 1909SELECT 1910 * 1911FROM 1912 jsonb_to_record('{"ia": null}') AS x (ia _int4); 1913 1914SELECT 1915 * 1916FROM 1917 jsonb_to_record('{"ia": 123}') AS x (ia _int4); 1918 1919SELECT 1920 * 1921FROM 1922 jsonb_to_record('{"ia": [1, "2", null, 4]}') AS x (ia _int4); 1923 1924SELECT 1925 * 1926FROM 1927 jsonb_to_record('{"ia": [[1, 2], [3, 4]]}') AS x (ia _int4); 1928 1929SELECT 1930 * 1931FROM 1932 jsonb_to_record('{"ia": [[1], 2]}') AS x (ia _int4); 1933 1934SELECT 1935 * 1936FROM 1937 jsonb_to_record('{"ia": [[1], [2, 3]]}') AS x (ia _int4); 1938 1939SELECT 1940 * 1941FROM 1942 jsonb_to_record('{"ia2": [1, 2, 3]}') AS x (ia2 int[][]); 1943 1944SELECT 1945 * 1946FROM 1947 jsonb_to_record('{"ia2": [[1, 2], [3, 4]]}') AS x (ia2 int4[][]); 1948 1949SELECT 1950 * 1951FROM 1952 jsonb_to_record('{"ia2": [[[1], [2], [3]]]}') AS x (ia2 int4[][]); 1953 1954-- test type info caching in jsonb_populate_record() 1955CREATE TEMP TABLE jsbpoptest ( 1956 js jsonb 1957); 1958 1959INSERT INTO jsbpoptest 1960SELECT 1961 '{ 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}'::jsonb 1966FROM 1967 generate_series(1, 3); 1968 1969SELECT 1970 (jsonb_populate_record(NULL::jsbrec, js)).* 1971FROM 1972 jsbpoptest; 1973 1974DROP TYPE jsbrec; 1975 1976DROP TYPE jsbrec_i_not_null; 1977 1978DROP DOMAIN jsb_int_not_null; 1979 1980DROP DOMAIN jsb_int_array_1d; 1981 1982DROP DOMAIN jsb_int_array_2d; 1983 1984DROP DOMAIN jb_ordered_pair; 1985 1986DROP TYPE jb_unordered_pair; 1987 1988-- indexing 1989SELECT 1990 count(*) 1991FROM 1992 testjsonb 1993WHERE 1994 j @> '{"wait":null}'; 1995 1996SELECT 1997 count(*) 1998FROM 1999 testjsonb 2000WHERE 2001 j @> '{"wait":"CC"}'; 2002 2003SELECT 2004 count(*) 2005FROM 2006 testjsonb 2007WHERE 2008 j @> '{"wait":"CC", "public":true}'; 2009 2010SELECT 2011 count(*) 2012FROM 2013 testjsonb 2014WHERE 2015 j @> '{"age":25}'; 2016 2017SELECT 2018 count(*) 2019FROM 2020 testjsonb 2021WHERE 2022 j @> '{"age":25.0}'; 2023 2024SELECT 2025 count(*) 2026FROM 2027 testjsonb 2028WHERE 2029 j ? 'public'; 2030 2031SELECT 2032 count(*) 2033FROM 2034 testjsonb 2035WHERE 2036 j ? 'bar'; 2037 2038SELECT 2039 count(*) 2040FROM 2041 testjsonb 2042WHERE 2043 j ? | ARRAY['public', 'disabled']; 2044 2045SELECT 2046 count(*) 2047FROM 2048 testjsonb 2049WHERE 2050 j ?& ARRAY['public', 'disabled']; 2051 2052SELECT 2053 count(*) 2054FROM 2055 testjsonb 2056WHERE 2057 j @@ '$.wait == null'; 2058 2059SELECT 2060 count(*) 2061FROM 2062 testjsonb 2063WHERE 2064 j @@ '"CC" == $.wait'; 2065 2066SELECT 2067 count(*) 2068FROM 2069 testjsonb 2070WHERE 2071 j @@ '$.wait == "CC" && true == $.public'; 2072 2073SELECT 2074 count(*) 2075FROM 2076 testjsonb 2077WHERE 2078 j @@ '$.age == 25'; 2079 2080SELECT 2081 count(*) 2082FROM 2083 testjsonb 2084WHERE 2085 j @@ '$.age == 25.0'; 2086 2087SELECT 2088 count(*) 2089FROM 2090 testjsonb 2091WHERE 2092 j @@ 'exists($)'; 2093 2094SELECT 2095 count(*) 2096FROM 2097 testjsonb 2098WHERE 2099 j @@ 'exists($.public)'; 2100 2101SELECT 2102 count(*) 2103FROM 2104 testjsonb 2105WHERE 2106 j @@ 'exists($.bar)'; 2107 2108SELECT 2109 count(*) 2110FROM 2111 testjsonb 2112WHERE 2113 j @@ 'exists($.public) || exists($.disabled)'; 2114 2115SELECT 2116 count(*) 2117FROM 2118 testjsonb 2119WHERE 2120 j @@ 'exists($.public) && exists($.disabled)'; 2121 2122SELECT 2123 count(*) 2124FROM 2125 testjsonb 2126WHERE 2127 j @ ? '$.wait ? (@ == null)'; 2128 2129SELECT 2130 count(*) 2131FROM 2132 testjsonb 2133WHERE 2134 j @ ? '$.wait ? ("CC" == @)'; 2135 2136SELECT 2137 count(*) 2138FROM 2139 testjsonb 2140WHERE 2141 j @ ? '$ ? (@.wait == "CC" && true == @.public)'; 2142 2143SELECT 2144 count(*) 2145FROM 2146 testjsonb 2147WHERE 2148 j @ ? '$.age ? (@ == 25)'; 2149 2150SELECT 2151 count(*) 2152FROM 2153 testjsonb 2154WHERE 2155 j @ ? '$ ? (@.age == 25.0)'; 2156 2157SELECT 2158 count(*) 2159FROM 2160 testjsonb 2161WHERE 2162 j @ ? '$'; 2163 2164SELECT 2165 count(*) 2166FROM 2167 testjsonb 2168WHERE 2169 j @ ? '$.public'; 2170 2171SELECT 2172 count(*) 2173FROM 2174 testjsonb 2175WHERE 2176 j @ ? '$.bar'; 2177 2178CREATE INDEX jidx ON testjsonb USING gin (j); 2179 2180SET enable_seqscan = OFF; 2181 2182SELECT 2183 count(*) 2184FROM 2185 testjsonb 2186WHERE 2187 j @> '{"wait":null}'; 2188 2189SELECT 2190 count(*) 2191FROM 2192 testjsonb 2193WHERE 2194 j @> '{"wait":"CC"}'; 2195 2196SELECT 2197 count(*) 2198FROM 2199 testjsonb 2200WHERE 2201 j @> '{"wait":"CC", "public":true}'; 2202 2203SELECT 2204 count(*) 2205FROM 2206 testjsonb 2207WHERE 2208 j @> '{"age":25}'; 2209 2210SELECT 2211 count(*) 2212FROM 2213 testjsonb 2214WHERE 2215 j @> '{"age":25.0}'; 2216 2217SELECT 2218 count(*) 2219FROM 2220 testjsonb 2221WHERE 2222 j @> '{"array":["foo"]}'; 2223 2224SELECT 2225 count(*) 2226FROM 2227 testjsonb 2228WHERE 2229 j @> '{"array":["bar"]}'; 2230 2231-- exercise GIN_SEARCH_MODE_ALL 2232SELECT 2233 count(*) 2234FROM 2235 testjsonb 2236WHERE 2237 j @> '{}'; 2238 2239SELECT 2240 count(*) 2241FROM 2242 testjsonb 2243WHERE 2244 j ? 'public'; 2245 2246SELECT 2247 count(*) 2248FROM 2249 testjsonb 2250WHERE 2251 j ? 'bar'; 2252 2253SELECT 2254 count(*) 2255FROM 2256 testjsonb 2257WHERE 2258 j ? | ARRAY['public', 'disabled']; 2259 2260SELECT 2261 count(*) 2262FROM 2263 testjsonb 2264WHERE 2265 j ?& ARRAY['public', 'disabled']; 2266 2267EXPLAIN ( 2268 COSTS OFF 2269) 2270SELECT 2271 count(*) 2272FROM 2273 testjsonb 2274WHERE 2275 j @@ '$.wait == null'; 2276 2277SELECT 2278 count(*) 2279FROM 2280 testjsonb 2281WHERE 2282 j @@ '$.wait == null'; 2283 2284SELECT 2285 count(*) 2286FROM 2287 testjsonb 2288WHERE 2289 j @@ 'exists($ ? (@.wait == null))'; 2290 2291SELECT 2292 count(*) 2293FROM 2294 testjsonb 2295WHERE 2296 j @@ 'exists($.wait ? (@ == null))'; 2297 2298SELECT 2299 count(*) 2300FROM 2301 testjsonb 2302WHERE 2303 j @@ '"CC" == $.wait'; 2304 2305SELECT 2306 count(*) 2307FROM 2308 testjsonb 2309WHERE 2310 j @@ '$.wait == "CC" && true == $.public'; 2311 2312SELECT 2313 count(*) 2314FROM 2315 testjsonb 2316WHERE 2317 j @@ '$.age == 25'; 2318 2319SELECT 2320 count(*) 2321FROM 2322 testjsonb 2323WHERE 2324 j @@ '$.age == 25.0'; 2325 2326SELECT 2327 count(*) 2328FROM 2329 testjsonb 2330WHERE 2331 j @@ '$.array[*] == "foo"'; 2332 2333SELECT 2334 count(*) 2335FROM 2336 testjsonb 2337WHERE 2338 j @@ '$.array[*] == "bar"'; 2339 2340SELECT 2341 count(*) 2342FROM 2343 testjsonb 2344WHERE 2345 j @@ 'exists($ ? (@.array[*] == "bar"))'; 2346 2347SELECT 2348 count(*) 2349FROM 2350 testjsonb 2351WHERE 2352 j @@ 'exists($.array ? (@[*] == "bar"))'; 2353 2354SELECT 2355 count(*) 2356FROM 2357 testjsonb 2358WHERE 2359 j @@ 'exists($.array[*] ? (@ == "bar"))'; 2360 2361SELECT 2362 count(*) 2363FROM 2364 testjsonb 2365WHERE 2366 j @@ 'exists($)'; 2367 2368SELECT 2369 count(*) 2370FROM 2371 testjsonb 2372WHERE 2373 j @@ 'exists($.public)'; 2374 2375SELECT 2376 count(*) 2377FROM 2378 testjsonb 2379WHERE 2380 j @@ 'exists($.bar)'; 2381 2382SELECT 2383 count(*) 2384FROM 2385 testjsonb 2386WHERE 2387 j @@ 'exists($.public) || exists($.disabled)'; 2388 2389SELECT 2390 count(*) 2391FROM 2392 testjsonb 2393WHERE 2394 j @@ 'exists($.public) && exists($.disabled)'; 2395 2396EXPLAIN ( 2397 COSTS OFF 2398) 2399SELECT 2400 count(*) 2401FROM 2402 testjsonb 2403WHERE 2404 j @ ? '$.wait ? (@ == null)'; 2405 2406SELECT 2407 count(*) 2408FROM 2409 testjsonb 2410WHERE 2411 j @ ? '$.wait ? (@ == null)'; 2412 2413SELECT 2414 count(*) 2415FROM 2416 testjsonb 2417WHERE 2418 j @ ? '$.wait ? ("CC" == @)'; 2419 2420SELECT 2421 count(*) 2422FROM 2423 testjsonb 2424WHERE 2425 j @ ? '$ ? (@.wait == "CC" && true == @.public)'; 2426 2427SELECT 2428 count(*) 2429FROM 2430 testjsonb 2431WHERE 2432 j @ ? '$.age ? (@ == 25)'; 2433 2434SELECT 2435 count(*) 2436FROM 2437 testjsonb 2438WHERE 2439 j @ ? '$ ? (@.age == 25.0)'; 2440 2441SELECT 2442 count(*) 2443FROM 2444 testjsonb 2445WHERE 2446 j @ ? '$ ? (@.array[*] == "bar")'; 2447 2448SELECT 2449 count(*) 2450FROM 2451 testjsonb 2452WHERE 2453 j @ ? '$.array ? (@[*] == "bar")'; 2454 2455SELECT 2456 count(*) 2457FROM 2458 testjsonb 2459WHERE 2460 j @ ? '$.array[*] ? (@ == "bar")'; 2461 2462SELECT 2463 count(*) 2464FROM 2465 testjsonb 2466WHERE 2467 j @ ? '$'; 2468 2469SELECT 2470 count(*) 2471FROM 2472 testjsonb 2473WHERE 2474 j @ ? '$.public'; 2475 2476SELECT 2477 count(*) 2478FROM 2479 testjsonb 2480WHERE 2481 j @ ? '$.bar'; 2482 2483-- array exists - array elements should behave as keys (for GIN index scans too) 2484CREATE INDEX jidx_array ON testjsonb USING gin ((j -> 'array')); 2485 2486SELECT 2487 count(*) 2488FROM 2489 testjsonb 2490WHERE 2491 j -> 'array' ? 'bar'; 2492 2493-- type sensitive array exists - should return no rows (since "exists" only 2494-- matches strings that are either object keys or array elements) 2495SELECT 2496 count(*) 2497FROM 2498 testjsonb 2499WHERE 2500 j -> 'array' ? '5'::text; 2501 2502-- However, a raw scalar is *contained* within the array 2503SELECT 2504 count(*) 2505FROM 2506 testjsonb 2507WHERE 2508 j -> 'array' @> '5'::jsonb; 2509 2510RESET enable_seqscan; 2511 2512SELECT 2513 count(*) 2514FROM ( 2515 SELECT 2516 (jsonb_each(j)).key 2517 FROM 2518 testjsonb) AS wow; 2519 2520SELECT 2521 key, 2522 count(*) 2523FROM ( 2524 SELECT 2525 (jsonb_each(j)).key 2526 FROM 2527 testjsonb) AS wow 2528GROUP BY 2529 key 2530ORDER BY 2531 count DESC, 2532 key; 2533 2534-- sort/hash 2535SELECT 2536 count(DISTINCT j) 2537FROM 2538 testjsonb; 2539 2540SET enable_hashagg = OFF; 2541 2542SELECT 2543 count(*) 2544FROM ( 2545 SELECT 2546 j 2547 FROM ( 2548 SELECT 2549 * 2550 FROM 2551 testjsonb 2552 UNION ALL 2553 SELECT 2554 * 2555 FROM 2556 testjsonb) js 2557 GROUP BY 2558 j) js2; 2559 2560SET enable_hashagg = ON; 2561 2562SET enable_sort = OFF; 2563 2564SELECT 2565 count(*) 2566FROM ( 2567 SELECT 2568 j 2569 FROM ( 2570 SELECT 2571 * 2572 FROM 2573 testjsonb 2574 UNION ALL 2575 SELECT 2576 * 2577 FROM 2578 testjsonb) js 2579 GROUP BY 2580 j) js2; 2581 2582SELECT DISTINCT 2583 * 2584FROM ( 2585 VALUES (jsonb '{}' || ''::text), 2586 ('{}')) v (j); 2587 2588SET enable_sort = ON; 2589 2590RESET enable_hashagg; 2591 2592RESET enable_sort; 2593 2594DROP INDEX jidx; 2595 2596DROP INDEX jidx_array; 2597 2598-- btree 2599CREATE INDEX jidx ON testjsonb USING btree (j); 2600 2601SET enable_seqscan = OFF; 2602 2603SELECT 2604 count(*) 2605FROM 2606 testjsonb 2607WHERE 2608 j > '{"p":1}'; 2609 2610SELECT 2611 count(*) 2612FROM 2613 testjsonb 2614WHERE 2615 j = '{"pos":98, "line":371, "node":"CBA", "indexed":true}'; 2616 2617--gin path opclass 2618DROP INDEX jidx; 2619 2620CREATE INDEX jidx ON testjsonb USING gin (j jsonb_path_ops); 2621 2622SET enable_seqscan = OFF; 2623 2624SELECT 2625 count(*) 2626FROM 2627 testjsonb 2628WHERE 2629 j @> '{"wait":null}'; 2630 2631SELECT 2632 count(*) 2633FROM 2634 testjsonb 2635WHERE 2636 j @> '{"wait":"CC"}'; 2637 2638SELECT 2639 count(*) 2640FROM 2641 testjsonb 2642WHERE 2643 j @> '{"wait":"CC", "public":true}'; 2644 2645SELECT 2646 count(*) 2647FROM 2648 testjsonb 2649WHERE 2650 j @> '{"age":25}'; 2651 2652SELECT 2653 count(*) 2654FROM 2655 testjsonb 2656WHERE 2657 j @> '{"age":25.0}'; 2658 2659-- exercise GIN_SEARCH_MODE_ALL 2660SELECT 2661 count(*) 2662FROM 2663 testjsonb 2664WHERE 2665 j @> '{}'; 2666 2667SELECT 2668 count(*) 2669FROM 2670 testjsonb 2671WHERE 2672 j @@ '$.wait == null'; 2673 2674SELECT 2675 count(*) 2676FROM 2677 testjsonb 2678WHERE 2679 j @@ 'exists($ ? (@.wait == null))'; 2680 2681SELECT 2682 count(*) 2683FROM 2684 testjsonb 2685WHERE 2686 j @@ 'exists($.wait ? (@ == null))'; 2687 2688SELECT 2689 count(*) 2690FROM 2691 testjsonb 2692WHERE 2693 j @@ '"CC" == $.wait'; 2694 2695SELECT 2696 count(*) 2697FROM 2698 testjsonb 2699WHERE 2700 j @@ '$.wait == "CC" && true == $.public'; 2701 2702SELECT 2703 count(*) 2704FROM 2705 testjsonb 2706WHERE 2707 j @@ '$.age == 25'; 2708 2709SELECT 2710 count(*) 2711FROM 2712 testjsonb 2713WHERE 2714 j @@ '$.age == 25.0'; 2715 2716SELECT 2717 count(*) 2718FROM 2719 testjsonb 2720WHERE 2721 j @@ '$.array[*] == "foo"'; 2722 2723SELECT 2724 count(*) 2725FROM 2726 testjsonb 2727WHERE 2728 j @@ '$.array[*] == "bar"'; 2729 2730SELECT 2731 count(*) 2732FROM 2733 testjsonb 2734WHERE 2735 j @@ 'exists($ ? (@.array[*] == "bar"))'; 2736 2737SELECT 2738 count(*) 2739FROM 2740 testjsonb 2741WHERE 2742 j @@ 'exists($.array ? (@[*] == "bar"))'; 2743 2744SELECT 2745 count(*) 2746FROM 2747 testjsonb 2748WHERE 2749 j @@ 'exists($.array[*] ? (@ == "bar"))'; 2750 2751SELECT 2752 count(*) 2753FROM 2754 testjsonb 2755WHERE 2756 j @@ 'exists($)'; 2757 2758EXPLAIN ( 2759 COSTS OFF 2760) 2761SELECT 2762 count(*) 2763FROM 2764 testjsonb 2765WHERE 2766 j @ ? '$.wait ? (@ == null)'; 2767 2768SELECT 2769 count(*) 2770FROM 2771 testjsonb 2772WHERE 2773 j @ ? '$.wait ? (@ == null)'; 2774 2775SELECT 2776 count(*) 2777FROM 2778 testjsonb 2779WHERE 2780 j @ ? '$.wait ? ("CC" == @)'; 2781 2782SELECT 2783 count(*) 2784FROM 2785 testjsonb 2786WHERE 2787 j @ ? '$ ? (@.wait == "CC" && true == @.public)'; 2788 2789SELECT 2790 count(*) 2791FROM 2792 testjsonb 2793WHERE 2794 j @ ? '$.age ? (@ == 25)'; 2795 2796SELECT 2797 count(*) 2798FROM 2799 testjsonb 2800WHERE 2801 j @ ? '$ ? (@.age == 25.0)'; 2802 2803SELECT 2804 count(*) 2805FROM 2806 testjsonb 2807WHERE 2808 j @ ? '$ ? (@.array[*] == "bar")'; 2809 2810SELECT 2811 count(*) 2812FROM 2813 testjsonb 2814WHERE 2815 j @ ? '$.array ? (@[*] == "bar")'; 2816 2817SELECT 2818 count(*) 2819FROM 2820 testjsonb 2821WHERE 2822 j @ ? '$.array[*] ? (@ == "bar")'; 2823 2824SELECT 2825 count(*) 2826FROM 2827 testjsonb 2828WHERE 2829 j @ ? '$'; 2830 2831SELECT 2832 count(*) 2833FROM 2834 testjsonb 2835WHERE 2836 j @ ? '$.public'; 2837 2838SELECT 2839 count(*) 2840FROM 2841 testjsonb 2842WHERE 2843 j @ ? '$.bar'; 2844 2845RESET enable_seqscan; 2846 2847DROP INDEX jidx; 2848 2849-- nested tests 2850SELECT 2851 '{"ff":{"a":12,"b":16}}'::jsonb; 2852 2853SELECT 2854 '{"ff":{"a":12,"b":16},"qq":123}'::jsonb; 2855 2856SELECT 2857 '{"aa":["a","aaa"],"qq":{"a":12,"b":16,"c":["c1","c2"],"d":{"d1":"d1","d2":"d2","d1":"d3"}}}'::jsonb; 2858 2859SELECT 2860 '{"aa":["a","aaa"],"qq":{"a":"12","b":"16","c":["c1","c2"],"d":{"d1":"d1","d2":"d2"}}}'::jsonb; 2861 2862SELECT 2863 '{"aa":["a","aaa"],"qq":{"a":"12","b":"16","c":["c1","c2",["c3"],{"c4":4}],"d":{"d1":"d1","d2":"d2"}}}'::jsonb; 2864 2865SELECT 2866 '{"ff":["a","aaa"]}'::jsonb; 2867 2868SELECT 2869 '{"ff":{"a":12,"b":16},"qq":123,"x":[1,2],"Y":null}'::jsonb -> 'ff', 2870 '{"ff":{"a":12,"b":16},"qq":123,"x":[1,2],"Y":null}'::jsonb -> 'qq', 2871 ('{"ff":{"a":12,"b":16},"qq":123,"x":[1,2],"Y":null}'::jsonb -> 'Y') IS NULL AS f, 2872 ('{"ff":{"a":12,"b":16},"qq":123,"x":[1,2],"Y":null}'::jsonb ->> 'Y') IS NULL AS t, 2873 '{"ff":{"a":12,"b":16},"qq":123,"x":[1,2],"Y":null}'::jsonb -> 'x'; 2874 2875-- nested containment 2876SELECT 2877 '{"a":[1,2],"c":"b"}'::jsonb @> '{"a":[1,2]}'; 2878 2879SELECT 2880 '{"a":[2,1],"c":"b"}'::jsonb @> '{"a":[1,2]}'; 2881 2882SELECT 2883 '{"a":{"1":2},"c":"b"}'::jsonb @> '{"a":[1,2]}'; 2884 2885SELECT 2886 '{"a":{"2":1},"c":"b"}'::jsonb @> '{"a":[1,2]}'; 2887 2888SELECT 2889 '{"a":{"1":2},"c":"b"}'::jsonb @> '{"a":{"1":2}}'; 2890 2891SELECT 2892 '{"a":{"2":1},"c":"b"}'::jsonb @> '{"a":{"1":2}}'; 2893 2894SELECT 2895 '["a","b"]'::jsonb @> '["a","b","c","b"]'; 2896 2897SELECT 2898 '["a","b","c","b"]'::jsonb @> '["a","b"]'; 2899 2900SELECT 2901 '["a","b","c",[1,2]]'::jsonb @> '["a",[1,2]]'; 2902 2903SELECT 2904 '["a","b","c",[1,2]]'::jsonb @> '["b",[1,2]]'; 2905 2906SELECT 2907 '{"a":[1,2],"c":"b"}'::jsonb @> '{"a":[1]}'; 2908 2909SELECT 2910 '{"a":[1,2],"c":"b"}'::jsonb @> '{"a":[2]}'; 2911 2912SELECT 2913 '{"a":[1,2],"c":"b"}'::jsonb @> '{"a":[3]}'; 2914 2915SELECT 2916 '{"a":[1,2,{"c":3,"x":4}],"c":"b"}'::jsonb @> '{"a":[{"c":3}]}'; 2917 2918SELECT 2919 '{"a":[1,2,{"c":3,"x":4}],"c":"b"}'::jsonb @> '{"a":[{"x":4}]}'; 2920 2921SELECT 2922 '{"a":[1,2,{"c":3,"x":4}],"c":"b"}'::jsonb @> '{"a":[{"x":4},3]}'; 2923 2924SELECT 2925 '{"a":[1,2,{"c":3,"x":4}],"c":"b"}'::jsonb @> '{"a":[{"x":4},1]}'; 2926 2927-- check some corner cases for indexed nested containment (bug #13756) 2928CREATE temp TABLE nestjsonb ( 2929 j jsonb 2930); 2931 2932INSERT INTO nestjsonb (j) 2933 VALUES ('{"a":[["b",{"x":1}],["b",{"x":2}]],"c":3}'); 2934 2935INSERT INTO nestjsonb (j) 2936 VALUES ('[[14,2,3]]'); 2937 2938INSERT INTO nestjsonb (j) 2939 VALUES ('[1,[14,2,3]]'); 2940 2941CREATE INDEX ON nestjsonb USING gin (j jsonb_path_ops); 2942 2943SET enable_seqscan = ON; 2944 2945SET enable_bitmapscan = OFF; 2946 2947SELECT 2948 * 2949FROM 2950 nestjsonb 2951WHERE 2952 j @> '{"a":[[{"x":2}]]}'::jsonb; 2953 2954SELECT 2955 * 2956FROM 2957 nestjsonb 2958WHERE 2959 j @> '{"c":3}'; 2960 2961SELECT 2962 * 2963FROM 2964 nestjsonb 2965WHERE 2966 j @> '[[14]]'; 2967 2968SET enable_seqscan = OFF; 2969 2970SET enable_bitmapscan = ON; 2971 2972SELECT 2973 * 2974FROM 2975 nestjsonb 2976WHERE 2977 j @> '{"a":[[{"x":2}]]}'::jsonb; 2978 2979SELECT 2980 * 2981FROM 2982 nestjsonb 2983WHERE 2984 j @> '{"c":3}'; 2985 2986SELECT 2987 * 2988FROM 2989 nestjsonb 2990WHERE 2991 j @> '[[14]]'; 2992 2993RESET enable_seqscan; 2994 2995RESET enable_bitmapscan; 2996 2997-- nested object field / array index lookup 2998SELECT 2999 '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'n'; 3000 3001SELECT 3002 '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'a'; 3003 3004SELECT 3005 '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'b'; 3006 3007SELECT 3008 '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'c'; 3009 3010SELECT 3011 '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'd'; 3012 3013SELECT 3014 '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'd' -> '1'; 3015 3016SELECT 3017 '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'e'; 3018 3019SELECT 3020 '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 0; 3021 3022--expecting error 3023SELECT 3024 '["a","b","c",[1,2],null]'::jsonb -> 0; 3025 3026SELECT 3027 '["a","b","c",[1,2],null]'::jsonb -> 1; 3028 3029SELECT 3030 '["a","b","c",[1,2],null]'::jsonb -> 2; 3031 3032SELECT 3033 '["a","b","c",[1,2],null]'::jsonb -> 3; 3034 3035SELECT 3036 '["a","b","c",[1,2],null]'::jsonb -> 3 -> 1; 3037 3038SELECT 3039 '["a","b","c",[1,2],null]'::jsonb -> 4; 3040 3041SELECT 3042 '["a","b","c",[1,2],null]'::jsonb -> 5; 3043 3044SELECT 3045 '["a","b","c",[1,2],null]'::jsonb -> - 1; 3046 3047SELECT 3048 '["a","b","c",[1,2],null]'::jsonb -> - 5; 3049 3050SELECT 3051 '["a","b","c",[1,2],null]'::jsonb -> - 6; 3052 3053--nested path extraction 3054SELECT 3055 '{"a":"b","c":[1,2,3]}'::jsonb #> '{0}'; 3056 3057SELECT 3058 '{"a":"b","c":[1,2,3]}'::jsonb #> '{a}'; 3059 3060SELECT 3061 '{"a":"b","c":[1,2,3]}'::jsonb #> '{c}'; 3062 3063SELECT 3064 '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,0}'; 3065 3066SELECT 3067 '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,1}'; 3068 3069SELECT 3070 '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,2}'; 3071 3072SELECT 3073 '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,3}'; 3074 3075SELECT 3076 '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,-1}'; 3077 3078SELECT 3079 '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,-3}'; 3080 3081SELECT 3082 '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,-4}'; 3083 3084SELECT 3085 '[0,1,2,[3,4],{"5":"five"}]'::jsonb #> '{0}'; 3086 3087SELECT 3088 '[0,1,2,[3,4],{"5":"five"}]'::jsonb #> '{3}'; 3089 3090SELECT 3091 '[0,1,2,[3,4],{"5":"five"}]'::jsonb #> '{4}'; 3092 3093SELECT 3094 '[0,1,2,[3,4],{"5":"five"}]'::jsonb #> '{4,5}'; 3095 3096--nested exists 3097SELECT 3098 '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'n'; 3099 3100SELECT 3101 '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'a'; 3102 3103SELECT 3104 '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'b'; 3105 3106SELECT 3107 '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'c'; 3108 3109SELECT 3110 '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'd'; 3111 3112SELECT 3113 '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'e'; 3114 3115-- jsonb_strip_nulls 3116SELECT 3117 jsonb_strip_nulls (NULL); 3118 3119SELECT 3120 jsonb_strip_nulls ('1'); 3121 3122SELECT 3123 jsonb_strip_nulls ('"a string"'); 3124 3125SELECT 3126 jsonb_strip_nulls ('null'); 3127 3128SELECT 3129 jsonb_strip_nulls ('[1,2,null,3,4]'); 3130 3131SELECT 3132 jsonb_strip_nulls ('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}'); 3133 3134SELECT 3135 jsonb_strip_nulls ('[1,{"a":1,"b":null,"c":2},3]'); 3136 3137-- an empty object is not null and should not be stripped 3138SELECT 3139 jsonb_strip_nulls ('{"a": {"b": null, "c": null}, "d": {} }'); 3140 3141SELECT 3142 jsonb_pretty('{"a": "test", "b": [1, 2, 3], "c": "test3", "d":{"dd": "test4", "dd2":{"ddd": "test5"}}}'); 3143 3144SELECT 3145 jsonb_pretty('[{"f1":1,"f2":null},2,null,[[{"x":true},6,7],8],3]'); 3146 3147SELECT 3148 jsonb_pretty('{"a":["b", "c"], "d": {"e":"f"}}'); 3149 3150SELECT 3151 jsonb_concat ('{"d": "test", "a": [1, 2]}', '{"g": "test2", "c": {"c1":1, "c2":2}}'); 3152 3153SELECT 3154 '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"cq":"l", "b":"g", "fg":false}'; 3155 3156SELECT 3157 '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"aq":"l"}'; 3158 3159SELECT 3160 '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"aa":"l"}'; 3161 3162SELECT 3163 '{"aa":1 , "b":2, "cq":3}'::jsonb || '{}'; 3164 3165SELECT 3166 '["a", "b"]'::jsonb || '["c"]'; 3167 3168SELECT 3169 '["a", "b"]'::jsonb || '["c", "d"]'; 3170 3171SELECT 3172 '["c"]' || '["a", "b"]'::jsonb; 3173 3174SELECT 3175 '["a", "b"]'::jsonb || '"c"'; 3176 3177SELECT 3178 '"c"' || '["a", "b"]'::jsonb; 3179 3180SELECT 3181 '[]'::jsonb || '["a"]'::jsonb; 3182 3183SELECT 3184 '[]'::jsonb || '"a"'::jsonb; 3185 3186SELECT 3187 '"b"'::jsonb || '"a"'::jsonb; 3188 3189SELECT 3190 '{}'::jsonb || '{"a":"b"}'::jsonb; 3191 3192SELECT 3193 '[]'::jsonb || '{"a":"b"}'::jsonb; 3194 3195SELECT 3196 '{"a":"b"}'::jsonb || '[]'::jsonb; 3197 3198SELECT 3199 '"a"'::jsonb || '{"a":1}'; 3200 3201SELECT 3202 '{"a":1}' || '"a"'::jsonb; 3203 3204SELECT 3205 '["a", "b"]'::jsonb || '{"c":1}'; 3206 3207SELECT 3208 '{"c": 1}'::jsonb || '["a", "b"]'; 3209 3210SELECT 3211 '{}'::jsonb || '{"cq":"l", "b":"g", "fg":false}'; 3212 3213SELECT 3214 pg_column_size('{}'::jsonb || '{}'::jsonb) = pg_column_size('{}'::jsonb); 3215 3216SELECT 3217 pg_column_size('{"aa":1}'::jsonb || '{"b":2}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb); 3218 3219SELECT 3220 pg_column_size('{"aa":1, "b":2}'::jsonb || '{}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb); 3221 3222SELECT 3223 pg_column_size('{}'::jsonb || '{"aa":1, "b":2}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb); 3224 3225SELECT 3226 jsonb_delete ('{"a":1 , "b":2, "c":3}'::jsonb, 'a'); 3227 3228SELECT 3229 jsonb_delete ('{"a":null , "b":2, "c":3}'::jsonb, 'a'); 3230 3231SELECT 3232 jsonb_delete ('{"a":1 , "b":2, "c":3}'::jsonb, 'b'); 3233 3234SELECT 3235 jsonb_delete ('{"a":1 , "b":2, "c":3}'::jsonb, 'c'); 3236 3237SELECT 3238 jsonb_delete ('{"a":1 , "b":2, "c":3}'::jsonb, 'd'); 3239 3240SELECT 3241 '{"a":1 , "b":2, "c":3}'::jsonb - 'a'; 3242 3243SELECT 3244 '{"a":null , "b":2, "c":3}'::jsonb - 'a'; 3245 3246SELECT 3247 '{"a":1 , "b":2, "c":3}'::jsonb - 'b'; 3248 3249SELECT 3250 '{"a":1 , "b":2, "c":3}'::jsonb - 'c'; 3251 3252SELECT 3253 '{"a":1 , "b":2, "c":3}'::jsonb - 'd'; 3254 3255SELECT 3256 pg_column_size('{"a":1 , "b":2, "c":3}'::jsonb - 'b') = pg_column_size('{"a":1, "b":2}'::jsonb); 3257 3258SELECT 3259 '["a","b","c"]'::jsonb - 3; 3260 3261SELECT 3262 '["a","b","c"]'::jsonb - 2; 3263 3264SELECT 3265 '["a","b","c"]'::jsonb - 1; 3266 3267SELECT 3268 '["a","b","c"]'::jsonb - 0; 3269 3270SELECT 3271 '["a","b","c"]'::jsonb - -1; 3272 3273SELECT 3274 '["a","b","c"]'::jsonb - -2; 3275 3276SELECT 3277 '["a","b","c"]'::jsonb - -3; 3278 3279SELECT 3280 '["a","b","c"]'::jsonb - -4; 3281 3282SELECT 3283 '{"a":1 , "b":2, "c":3}'::jsonb - '{b}'::text[]; 3284 3285SELECT 3286 '{"a":1 , "b":2, "c":3}'::jsonb - '{c,b}'::text[]; 3287 3288SELECT 3289 '{"a":1 , "b":2, "c":3}'::jsonb - '{}'::text[]; 3290 3291SELECT 3292 jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]'); 3293 3294SELECT 3295 jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '[1,2,3]'); 3296 3297SELECT 3298 jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '[1,2,3]'); 3299 3300SELECT 3301 jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '[1,2,3]'); 3302 3303SELECT 3304 jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '{"1": 2}'); 3305 3306SELECT 3307 jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"1": 2}'); 3308 3309SELECT 3310 jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '{"1": 2}'); 3311 3312SELECT 3313 jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '{"1": 2}'); 3314 3315SELECT 3316 jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '"test"'); 3317 3318SELECT 3319 jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"f": "test"}'); 3320 3321SELECT 3322 jsonb_delete_path ('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{n}'); 3323 3324SELECT 3325 jsonb_delete_path ('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{b,-1}'); 3326 3327SELECT 3328 jsonb_delete_path ('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{d,1,0}'); 3329 3330SELECT 3331 '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb # - '{n}'; 3332 3333SELECT 3334 '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb # - '{b,-1}'; 3335 3336SELECT 3337 '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb # - '{b,-1e}'; 3338 3339-- invalid array subscript 3340SELECT 3341 '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb # - '{d,1,0}'; 3342 3343-- empty structure and error conditions for delete and replace 3344SELECT 3345 '"a"'::jsonb - 'a'; 3346 3347-- error 3348SELECT 3349 '{}'::jsonb - 'a'; 3350 3351SELECT 3352 '[]'::jsonb - 'a'; 3353 3354SELECT 3355 '"a"'::jsonb - 1; 3356 3357-- error 3358SELECT 3359 '{}'::jsonb - 1; 3360 3361-- error 3362SELECT 3363 '[]'::jsonb - 1; 3364 3365SELECT 3366 '"a"'::jsonb # - '{a}'; 3367 3368-- error 3369SELECT 3370 '{}'::jsonb # - '{a}'; 3371 3372SELECT 3373 '[]'::jsonb # - '{a}'; 3374 3375SELECT 3376 jsonb_set('"a"', '{a}', '"b"'); 3377 3378--error 3379SELECT 3380 jsonb_set('{}', '{a}', '"b"', FALSE); 3381 3382SELECT 3383 jsonb_set('[]', '{1}', '"b"', FALSE); 3384 3385SELECT 3386 jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0}', '[2,3,4]', FALSE); 3387 3388-- jsonb_set adding instead of replacing 3389-- prepend to array 3390SELECT 3391 jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}', '{b,-33}', '{"foo":123}'); 3392 3393-- append to array 3394SELECT 3395 jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}', '{b,33}', '{"foo":123}'); 3396 3397-- check nesting levels addition 3398SELECT 3399 jsonb_set('{"a":1,"b":[4,5,[0,1,2],6,7],"c":{"d":4}}', '{b,2,33}', '{"foo":123}'); 3400 3401-- add new key 3402SELECT 3403 jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}', '{c,e}', '{"foo":123}'); 3404 3405-- adding doesn't do anything if elements before last aren't present 3406SELECT 3407 jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}', '{x,-33}', '{"foo":123}'); 3408 3409SELECT 3410 jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}', '{x,y}', '{"foo":123}'); 3411 3412-- add to empty object 3413SELECT 3414 jsonb_set('{}', '{x}', '{"foo":123}'); 3415 3416--add to empty array 3417SELECT 3418 jsonb_set('[]', '{0}', '{"foo":123}'); 3419 3420SELECT 3421 jsonb_set('[]', '{99}', '{"foo":123}'); 3422 3423SELECT 3424 jsonb_set('[]', '{-99}', '{"foo":123}'); 3425 3426SELECT 3427 jsonb_set('{"a": [1, 2, 3]}', '{a, non_integer}', '"new_value"'); 3428 3429SELECT 3430 jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, non_integer}', '"new_value"'); 3431 3432SELECT 3433 jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, NULL}', '"new_value"'); 3434 3435-- jsonb_insert 3436SELECT 3437 jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"'); 3438 3439SELECT 3440 jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', TRUE); 3441 3442SELECT 3443 jsonb_insert('{"a": {"b": {"c": [0, 1, "test1", "test2"]}}}', '{a, b, c, 2}', '"new_value"'); 3444 3445SELECT 3446 jsonb_insert('{"a": {"b": {"c": [0, 1, "test1", "test2"]}}}', '{a, b, c, 2}', '"new_value"', TRUE); 3447 3448SELECT 3449 jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '{"b": "value"}'); 3450 3451SELECT 3452 jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '["value1", "value2"]'); 3453 3454-- edge cases 3455SELECT 3456 jsonb_insert('{"a": [0,1,2]}', '{a, 0}', '"new_value"'); 3457 3458SELECT 3459 jsonb_insert('{"a": [0,1,2]}', '{a, 0}', '"new_value"', TRUE); 3460 3461SELECT 3462 jsonb_insert('{"a": [0,1,2]}', '{a, 2}', '"new_value"'); 3463 3464SELECT 3465 jsonb_insert('{"a": [0,1,2]}', '{a, 2}', '"new_value"', TRUE); 3466 3467SELECT 3468 jsonb_insert('{"a": [0,1,2]}', '{a, -1}', '"new_value"'); 3469 3470SELECT 3471 jsonb_insert('{"a": [0,1,2]}', '{a, -1}', '"new_value"', TRUE); 3472 3473SELECT 3474 jsonb_insert('[]', '{1}', '"new_value"'); 3475 3476SELECT 3477 jsonb_insert('[]', '{1}', '"new_value"', TRUE); 3478 3479SELECT 3480 jsonb_insert('{"a": []}', '{a, 1}', '"new_value"'); 3481 3482SELECT 3483 jsonb_insert('{"a": []}', '{a, 1}', '"new_value"', TRUE); 3484 3485SELECT 3486 jsonb_insert('{"a": [0,1,2]}', '{a, 10}', '"new_value"'); 3487 3488SELECT 3489 jsonb_insert('{"a": [0,1,2]}', '{a, -10}', '"new_value"'); 3490 3491-- jsonb_insert should be able to insert new value for objects, but not to replace 3492SELECT 3493 jsonb_insert('{"a": {"b": "value"}}', '{a, c}', '"new_value"'); 3494 3495SELECT 3496 jsonb_insert('{"a": {"b": "value"}}', '{a, c}', '"new_value"', TRUE); 3497 3498SELECT 3499 jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"'); 3500 3501SELECT 3502 jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"', TRUE); 3503 3504-- jsonb to tsvector 3505SELECT 3506 to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); 3507 3508-- jsonb to tsvector with config 3509SELECT 3510 to_tsvector('simple', '{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); 3511 3512-- jsonb to tsvector with stop words 3513SELECT 3514 to_tsvector('english', '{"a": "aaa in bbb ddd ccc", "b": ["the eee fff ggg"], "c": {"d": "hhh. iii"}}'::jsonb); 3515 3516-- jsonb to tsvector with numeric values 3517SELECT 3518 to_tsvector('english', '{"a": "aaa in bbb ddd ccc", "b": 123, "c": 456}'::jsonb); 3519 3520-- jsonb_to_tsvector 3521SELECT 3522 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"all"'); 3523 3524SELECT 3525 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"key"'); 3526 3527SELECT 3528 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"string"'); 3529 3530SELECT 3531 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"numeric"'); 3532 3533SELECT 3534 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"boolean"'); 3535 3536SELECT 3537 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '["string", "numeric"]'); 3538 3539SELECT 3540 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"all"'); 3541 3542SELECT 3543 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"key"'); 3544 3545SELECT 3546 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"string"'); 3547 3548SELECT 3549 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"numeric"'); 3550 3551SELECT 3552 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"boolean"'); 3553 3554SELECT 3555 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '["string", "numeric"]'); 3556 3557-- to_tsvector corner cases 3558SELECT 3559 to_tsvector('""'::jsonb); 3560 3561SELECT 3562 to_tsvector('{}'::jsonb); 3563 3564SELECT 3565 to_tsvector('[]'::jsonb); 3566 3567SELECT 3568 to_tsvector('null'::jsonb); 3569 3570-- jsonb_to_tsvector corner cases 3571SELECT 3572 jsonb_to_tsvector ('""'::jsonb, '"all"'); 3573 3574SELECT 3575 jsonb_to_tsvector ('{}'::jsonb, '"all"'); 3576 3577SELECT 3578 jsonb_to_tsvector ('[]'::jsonb, '"all"'); 3579 3580SELECT 3581 jsonb_to_tsvector ('null'::jsonb, '"all"'); 3582 3583SELECT 3584 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '""'); 3585 3586SELECT 3587 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '{}'); 3588 3589SELECT 3590 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '[]'); 3591 3592SELECT 3593 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, 'null'); 3594 3595SELECT 3596 jsonb_to_tsvector ('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '["all", null]'); 3597 3598-- ts_headline for jsonb 3599SELECT 3600 ts_headline('{"a": "aaa bbb", "b": {"c": "ccc ddd fff", "c1": "ccc1 ddd1"}, "d": ["ggg hhh", "iii jjj"]}'::jsonb, tsquery ('bbb & ddd & hhh')); 3601 3602SELECT 3603 ts_headline('english', '{"a": "aaa bbb", "b": {"c": "ccc ddd fff"}, "d": ["ggg hhh", "iii jjj"]}'::jsonb, tsquery ('bbb & ddd & hhh')); 3604 3605SELECT 3606 ts_headline('{"a": "aaa bbb", "b": {"c": "ccc ddd fff", "c1": "ccc1 ddd1"}, "d": ["ggg hhh", "iii jjj"]}'::jsonb, tsquery ('bbb & ddd & hhh'), 'StartSel = <, StopSel = >'); 3607 3608SELECT 3609 ts_headline('english', '{"a": "aaa bbb", "b": {"c": "ccc ddd fff", "c1": "ccc1 ddd1"}, "d": ["ggg hhh", "iii jjj"]}'::jsonb, tsquery ('bbb & ddd & hhh'), 'StartSel = <, StopSel = >'); 3610 3611-- corner cases for ts_headline with jsonb 3612SELECT 3613 ts_headline('null'::jsonb, tsquery ('aaa & bbb')); 3614 3615SELECT 3616 ts_headline('{}'::jsonb, tsquery ('aaa & bbb')); 3617 3618SELECT 3619 ts_headline('[]'::jsonb, tsquery ('aaa & bbb')); 3620 3621-- casts 3622SELECT 3623 'true'::jsonb::bool; 3624 3625SELECT 3626 '[]'::jsonb::bool; 3627 3628SELECT 3629 '1.0'::jsonb::float; 3630 3631SELECT 3632 '[1.0]'::jsonb::float; 3633 3634SELECT 3635 '12345'::jsonb::int4; 3636 3637SELECT 3638 '"hello"'::jsonb::int4; 3639 3640SELECT 3641 '12345'::jsonb::numeric; 3642 3643SELECT 3644 '{}'::jsonb::numeric; 3645 3646SELECT 3647 '12345.05'::jsonb::numeric; 3648 3649SELECT 3650 '12345.05'::jsonb::float4; 3651 3652SELECT 3653 '12345.05'::jsonb::float8; 3654 3655SELECT 3656 '12345.05'::jsonb::int2; 3657 3658SELECT 3659 '12345.05'::jsonb::int4; 3660 3661SELECT 3662 '12345.05'::jsonb::int8; 3663 3664SELECT 3665 '12345.0000000000000000000000000000000000000000000005'::jsonb::numeric; 3666 3667SELECT 3668 '12345.0000000000000000000000000000000000000000000005'::jsonb::float4; 3669 3670SELECT 3671 '12345.0000000000000000000000000000000000000000000005'::jsonb::float8; 3672 3673SELECT 3674 '12345.0000000000000000000000000000000000000000000005'::jsonb::int2; 3675 3676SELECT 3677 '12345.0000000000000000000000000000000000000000000005'::jsonb::int4; 3678 3679SELECT 3680 '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; 3681 3682