github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/json_builtins (about) 1 ## json_typeof and jsonb_typeof 2 3 query T 4 SELECT json_typeof('-123.4'::JSON) 5 ---- 6 number 7 8 query T 9 SELECT jsonb_typeof('-123.4'::JSON) 10 ---- 11 number 12 13 query T 14 SELECT json_typeof('"-123.4"'::JSON) 15 ---- 16 string 17 18 query T 19 SELECT jsonb_typeof('"-123.4"'::JSON) 20 ---- 21 string 22 23 query T 24 SELECT json_typeof('{"1": {"2": 3}}'::JSON) 25 ---- 26 object 27 28 query T 29 SELECT jsonb_typeof('{"1": {"2": 3}}'::JSON) 30 ---- 31 object 32 33 query T 34 SELECT json_typeof('[1, 2, [3]]'::JSON) 35 ---- 36 array 37 38 query T 39 SELECT jsonb_typeof('[1, 2, [3]]'::JSON) 40 ---- 41 array 42 43 query T 44 SELECT json_typeof('true'::JSON) 45 ---- 46 boolean 47 48 query T 49 SELECT jsonb_typeof('true'::JSON) 50 ---- 51 boolean 52 53 query T 54 SELECT json_typeof('false'::JSON) 55 ---- 56 boolean 57 58 query T 59 SELECT jsonb_typeof('false'::JSON) 60 ---- 61 boolean 62 63 query T 64 SELECT json_typeof('null'::JSON) 65 ---- 66 null 67 68 query T 69 SELECT jsonb_typeof('null'::JSON) 70 ---- 71 null 72 73 ## array_to_json 74 query T 75 SELECT array_to_json(ARRAY[[1, 2], [3, 4]]) 76 ---- 77 [[1, 2], [3, 4]] 78 79 query T 80 SELECT array_to_json('{1, 2, 3}'::INT[]) 81 ---- 82 [1, 2, 3] 83 84 query T 85 SELECT array_to_json('{"a", "b", "c"}'::STRING[]) 86 ---- 87 ["a", "b", "c"] 88 89 query T 90 SELECT array_to_json('{1.0, 2.0, 3.0}'::DECIMAL[]) 91 ---- 92 [1.0, 2.0, 3.0] 93 94 query T 95 SELECT array_to_json(NULL) 96 ---- 97 NULL 98 99 query T 100 SELECT array_to_json(ARRAY[1, 2, 3], NULL) 101 ---- 102 NULL 103 104 query T 105 SELECT array_to_json(ARRAY[1, 2, 3], false) 106 ---- 107 [1, 2, 3] 108 109 query error pq: array_to_json\(\): pretty printing is not supported 110 SELECT array_to_json(ARRAY[1, 2, 3], true) 111 112 query error pq: unknown signature: array_to_json\(string\) 113 SELECT array_to_json('hello world') 114 115 ## to_json and to_jsonb 116 117 query T 118 SELECT to_json(123::INT) 119 ---- 120 123 121 122 query T 123 SELECT to_json('\a'::TEXT) 124 ---- 125 "\\a" 126 127 query T 128 SELECT to_json('\a'::TEXT COLLATE "fr_FR") 129 ---- 130 "\\a" 131 132 query T 133 SELECT to_json(3::OID::INT::OID) 134 ---- 135 "3" 136 137 query T 138 SELECT to_json('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::UUID); 139 ---- 140 "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11" 141 142 query T 143 SELECT to_json('\x0001'::BYTEA) 144 ---- 145 "\\x0001" 146 147 query T 148 SELECT to_json(true::BOOL) 149 ---- 150 true 151 152 query T 153 SELECT to_json(false::BOOL) 154 ---- 155 false 156 157 query T 158 SELECT to_json('"a"'::JSON) 159 ---- 160 "a" 161 162 query T 163 SELECT to_json(1.234::FLOAT) 164 ---- 165 1.234 166 167 query T 168 SELECT to_json(1.234::DECIMAL) 169 ---- 170 1.234 171 172 query T 173 SELECT to_json('10.1.0.0/16'::INET) 174 ---- 175 "10.1.0.0/16" 176 177 query T 178 SELECT to_json(ARRAY[[1, 2], [3, 4]]) 179 ---- 180 [[1, 2], [3, 4]] 181 182 query T 183 SELECT to_json('2014-05-28 12:22:35.614298'::TIMESTAMP) 184 ---- 185 "2014-05-28T12:22:35.614298" 186 187 query T 188 SELECT to_json('2014-05-28 12:22:35.614298-04'::TIMESTAMPTZ) 189 ---- 190 "2014-05-28T16:22:35.614298Z" 191 192 query T 193 SELECT to_json('2014-05-28 12:22:35.614298-04'::TIMESTAMP) 194 ---- 195 "2014-05-28T12:22:35.614298" 196 197 query T 198 SELECT to_json('2014-05-28'::DATE) 199 ---- 200 "2014-05-28" 201 202 query T 203 SELECT to_json('00:00:00'::TIME) 204 ---- 205 "00:00:00" 206 207 query T 208 SELECT to_json('2h45m2s234ms'::INTERVAL) 209 ---- 210 "02:45:02.234" 211 212 query T 213 SELECT to_json((1, 2, 'hello', NULL, NULL)) 214 ---- 215 {"f1": 1, "f2": 2, "f3": "hello", "f4": null, "f5": null} 216 217 query T 218 SELECT to_jsonb(123::INT) 219 ---- 220 123 221 222 query T 223 SELECT to_jsonb('\a'::TEXT) 224 ---- 225 "\\a" 226 227 query T 228 SELECT to_jsonb('\a'::TEXT COLLATE "fr_FR") 229 ---- 230 "\\a" 231 232 query T 233 SELECT to_jsonb(3::OID::INT::OID) 234 ---- 235 "3" 236 237 query T 238 SELECT to_jsonb('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::UUID); 239 ---- 240 "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11" 241 242 query T 243 SELECT to_jsonb('\x0001'::BYTEA) 244 ---- 245 "\\x0001" 246 247 query T 248 SELECT to_jsonb(true::BOOL) 249 ---- 250 true 251 252 query T 253 SELECT to_jsonb(false::BOOL) 254 ---- 255 false 256 257 query T 258 SELECT to_jsonb('"a"'::JSON) 259 ---- 260 "a" 261 262 query T 263 SELECT to_jsonb(1.234::FLOAT) 264 ---- 265 1.234 266 267 query T 268 SELECT to_jsonb(1.234::DECIMAL) 269 ---- 270 1.234 271 272 query T 273 SELECT to_jsonb('10.1.0.0/16'::INET) 274 ---- 275 "10.1.0.0/16" 276 277 query T 278 SELECT to_jsonb(ARRAY[[1, 2], [3, 4]]) 279 ---- 280 [[1, 2], [3, 4]] 281 282 query T 283 SELECT to_jsonb('2014-05-28 12:22:35.614298'::TIMESTAMP) 284 ---- 285 "2014-05-28T12:22:35.614298" 286 287 query T 288 SELECT to_jsonb('2014-05-28 12:22:35.614298-04'::TIMESTAMPTZ) 289 ---- 290 "2014-05-28T16:22:35.614298Z" 291 292 query T 293 SELECT to_jsonb('2014-05-28 12:22:35.614298-04'::TIMESTAMP) 294 ---- 295 "2014-05-28T12:22:35.614298" 296 297 query T 298 SELECT to_jsonb('2014-05-28'::DATE) 299 ---- 300 "2014-05-28" 301 302 query T 303 SELECT to_jsonb('00:00:00'::TIME) 304 ---- 305 "00:00:00" 306 307 query T 308 SELECT to_jsonb('2h45m2s234ms'::INTERVAL) 309 ---- 310 "02:45:02.234" 311 312 query T 313 SELECT to_jsonb((1, 2, 'hello', NULL, NULL)) 314 ---- 315 {"f1": 1, "f2": 2, "f3": "hello", "f4": null, "f5": null} 316 317 query T 318 SELECT to_json(x.*) FROM (VALUES (1,2)) AS x(a,b); 319 ---- 320 {"a": 1, "b": 2} 321 322 query T 323 SELECT to_json(x.*) FROM (VALUES (1,2)) AS x(a); 324 ---- 325 {"a": 1, "column2": 2} 326 327 # TODO(#44465): Implement the test cases below to be compatible with Postgres 328 # and delete this one 329 query T 330 SELECT to_json(x.*) FROM (VALUES (1,2)) AS x(column2); 331 ---- 332 {"column2": 2} 333 334 # Odd, but postgres-compatible 335 # query T 336 # SELECT to_json(x.*) FROM (VALUES (1,2)) AS x(a,a); 337 # ---- 338 # {"a": 1, "a": 2} 339 340 # query T 341 # SELECT to_json(x.*) FROM (VALUES (1,2)) AS x(column1); 342 # ---- 343 # {"column1": 1, "column2": 2} 344 345 # query T 346 # SELECT to_json(x.*) FROM (VALUES (1,2)) AS x(column2); 347 # ---- 348 # {"column2": 1, "column2": 2} 349 350 # Regression test for #39502. 351 statement ok 352 SELECT json_agg((3808362714,)) 353 354 ## json_array_elements and jsonb_array_elements 355 356 query T colnames 357 SELECT json_array_elements('[1, 2, 3]'::JSON) 358 ---- 359 json_array_elements 360 1 361 2 362 3 363 364 query T colnames 365 SELECT * FROM json_array_elements('[1, 2, 3]'::JSON) 366 ---- 367 value 368 1 369 2 370 3 371 372 query T colnames 373 SELECT jsonb_array_elements('[1, 2, 3]'::JSON) 374 ---- 375 jsonb_array_elements 376 1 377 2 378 3 379 380 query T colnames 381 SELECT * FROM jsonb_array_elements('[1, 2, 3]'::JSON) 382 ---- 383 value 384 1 385 2 386 3 387 388 query T colnames 389 SELECT json_array_elements('[1, true, null, "text", -1.234, {"2": 3, "4": "5"}, [1, 2, 3]]'::JSON) 390 ---- 391 json_array_elements 392 1 393 true 394 null 395 "text" 396 -1.234 397 {"2": 3, "4": "5"} 398 [1, 2, 3] 399 400 query T colnames 401 SELECT * FROM json_array_elements('[1, true, null, "text", -1.234, {"2": 3, "4": "5"}, [1, 2, 3]]'::JSON) 402 ---- 403 value 404 1 405 true 406 null 407 "text" 408 -1.234 409 {"2": 3, "4": "5"} 410 [1, 2, 3] 411 412 query T 413 SELECT json_array_elements('[]'::JSON) 414 ---- 415 416 417 query error pq: cannot be called on a non-array 418 SELECT json_array_elements('{"1": 2}'::JSON) 419 420 query error pq: cannot be called on a non-array 421 SELECT jsonb_array_elements('{"1": 2}'::JSON) 422 423 424 ## json_array_elements_text and jsonb_array_elements_text 425 426 query T colnames 427 SELECT json_array_elements_text('[1, 2, 3]'::JSON) 428 ---- 429 json_array_elements_text 430 1 431 2 432 3 433 434 query T colnames 435 SELECT * FROM json_array_elements_text('[1, 2, 3]'::JSON) 436 ---- 437 value 438 1 439 2 440 3 441 442 query T colnames 443 SELECT json_array_elements_text('[1, 2, 3]'::JSON) 444 ---- 445 json_array_elements_text 446 1 447 2 448 3 449 450 query T colnames 451 SELECT * FROM json_array_elements_text('[1, 2, 3]'::JSON) 452 ---- 453 value 454 1 455 2 456 3 457 458 query T 459 SELECT json_array_elements_text('[1, true, null, "text", -1.234, {"2": 3, "4": "5"}, [1, 2, 3]]'::JSON) 460 ---- 461 1 462 true 463 NULL 464 text 465 -1.234 466 {"2": 3, "4": "5"} 467 [1, 2, 3] 468 469 query T 470 SELECT json_array_elements('[]'::JSON) 471 ---- 472 473 query error pq: cannot be called on a non-array 474 SELECT json_array_elements_text('{"1": 2}'::JSON) 475 476 query error pq: cannot be called on a non-array 477 SELECT jsonb_array_elements_text('{"1": 2}'::JSON) 478 479 480 ## json_object_keys and jsonb_object_keys 481 482 query T 483 SELECT json_object_keys('{"1": 2, "3": 4}'::JSON) 484 ---- 485 1 486 3 487 488 query T 489 SELECT jsonb_object_keys('{"1": 2, "3": 4}'::JSON) 490 ---- 491 1 492 3 493 494 query T 495 SELECT json_object_keys('{}'::JSON) 496 ---- 497 498 query T 499 SELECT json_object_keys('{"\"1\"": 2}'::JSON) 500 ---- 501 "1" 502 503 # Keys are sorted. 504 query T colnames 505 SELECT json_object_keys('{"a": 1, "1": 2, "3": {"4": 5, "6": 7}}'::JSON) 506 ---- 507 json_object_keys 508 1 509 3 510 a 511 512 query T colnames 513 SELECT * FROM json_object_keys('{"a": 1, "1": 2, "3": {"4": 5, "6": 7}}'::JSON) 514 ---- 515 json_object_keys 516 1 517 3 518 a 519 520 query error pq: cannot call json_object_keys on a scalar 521 SELECT json_object_keys('null'::JSON) 522 523 query error pq: cannot call json_object_keys on an array 524 SELECT json_object_keys('[1, 2, 3]'::JSON) 525 526 ## json_build_object 527 528 query T 529 SELECT json_build_object() 530 ---- 531 {} 532 533 query T 534 SELECT json_build_object('a', 2, 'b', 4) 535 ---- 536 {"a": 2, "b": 4} 537 538 query T 539 SELECT jsonb_build_object(true,'val',1, 0, 1.3, 2, date '2019-02-03' - date '2019-01-01', 4, '2001-01-01 11:00+3'::timestamptz, '11:00+3'::timetz) 540 ---- 541 {"1": 0, "1.3": 2, "2001-01-01 08:00:00+00:00": "11:00:00+03:00:00", "33": 4, "true": "val"} 542 543 query T 544 SELECT json_build_object('a',1,'b',1.2,'c',true,'d',null,'e','{"x": 3, "y": [1,2,3]}'::JSON) 545 ---- 546 {"a": 1, "b": 1.2, "c": true, "d": null, "e": {"x": 3, "y": [1, 2, 3]}} 547 548 query T 549 SELECT json_build_object( 550 'a', json_build_object('b',false,'c',99), 551 'd', json_build_object('e',ARRAY[9,8,7]::int[]) 552 ) 553 ---- 554 {"a": {"b": false, "c": 99}, "d": {"e": [9, 8, 7]}} 555 556 query T 557 SELECT json_build_object(a,3) FROM (SELECT 1 AS a, 2 AS b) r 558 ---- 559 {"1": 3} 560 561 query T 562 SELECT json_build_object('\a'::TEXT COLLATE "fr_FR", 1) 563 ---- 564 {"\\a": 1} 565 566 query T 567 SELECT json_build_object('\a', 1) 568 ---- 569 {"\\a": 1} 570 571 query T 572 SELECT json_build_object(json_object_keys('{"x":3, "y":4}'::JSON), 2) 573 ---- 574 {"x": 2} 575 {"y": 2} 576 577 # Regression for panic when bit array is passed as argument. 578 query T 579 SELECT json_build_object('a', '0100110'::varbit) 580 ---- 581 {"a": "0100110"} 582 583 # even number of arguments 584 query error pq: json_build_object\(\): argument list must have even number of elements 585 SELECT json_build_object(1,2,3) 586 587 # keys must be scalar and not null 588 query error pq: json_build_object\(\): argument 1 cannot be null 589 SELECT json_build_object(null,2) 590 591 query error pq: json_build_object\(\): key value must be scalar, not array, tuple, or json 592 SELECT json_build_object((1,2),3) 593 594 query error pq: json_build_object\(\): key value must be scalar, not array, tuple, or json 595 SELECT json_build_object('{"a":1,"b":2}'::JSON, 3) 596 597 query error pq: json_build_object\(\): key value must be scalar, not array, tuple, or json 598 SELECT json_build_object('{1,2,3}'::int[], 3) 599 600 query T 601 SELECT json_extract_path('{"a": 1}', 'a') 602 ---- 603 1 604 605 query T 606 SELECT json_extract_path('{"a": 1}', 'a', NULL) 607 ---- 608 NULL 609 610 query T 611 SELECT json_extract_path('{"a": 1}') 612 ---- 613 {"a": 1} 614 615 query T 616 SELECT json_extract_path('{"a": {"b": 2}}', 'a') 617 ---- 618 {"b": 2} 619 620 query T 621 SELECT json_extract_path('{"a": {"b": 2}}', 'a', 'b') 622 ---- 623 2 624 625 query T 626 SELECT jsonb_extract_path('{"a": {"b": 2}}', 'a', 'b') 627 ---- 628 2 629 630 query T 631 SELECT json_extract_path('{"a": {"b": 2}}', 'a', 'b', 'c') 632 ---- 633 NULL 634 635 query T 636 SELECT jsonb_pretty('{"a": 1}') 637 ---- 638 { 639 "a": 1 640 } 641 642 query T 643 SELECT '[1,2,3]'::JSON || '[4,5,6]'::JSON 644 ---- 645 [1, 2, 3, 4, 5, 6] 646 647 query T 648 SELECT '{"a": 1, "b": 2}'::JSON || '{"b": 3, "c": 4}' 649 ---- 650 {"a": 1, "b": 3, "c": 4} 651 652 query error pgcode 22023 invalid concatenation of jsonb objects 653 SELECT '{"a": 1, "b": 2}'::JSON || '"c"' 654 655 query T 656 SELECT json_build_array() 657 ---- 658 [] 659 660 query T 661 SELECT json_build_array('\x0001'::BYTEA) 662 ---- 663 ["\\x0001"] 664 665 query T 666 SELECT json_build_array(1, '1'::JSON, 1.2, NULL, ARRAY['x', 'y']) 667 ---- 668 [1, 1, 1.2, null, ["x", "y"]] 669 670 query T 671 SELECT jsonb_build_array() 672 ---- 673 [] 674 675 query T 676 SELECT jsonb_build_array('\x0001'::BYTEA) 677 ---- 678 ["\\x0001"] 679 680 query T 681 SELECT jsonb_build_array(1, '1'::JSON, 1.2, NULL, ARRAY['x', 'y']) 682 ---- 683 [1, 1, 1.2, null, ["x", "y"]] 684 685 # Regression for #37318 686 query T 687 SELECT jsonb_build_array('+Inf'::FLOAT8, 'NaN'::FLOAT8)::STRING::JSONB 688 ---- 689 ["Infinity", "NaN"] 690 691 query error pq: json_object\(\): array must have even number of elements 692 SELECT json_object('{a,b,c}'::TEXT[]) 693 694 query error pq: json_object\(\): null value not allowed for object key 695 SELECT json_object('{NULL, a}'::TEXT[]) 696 697 query error pq: json_object\(\): null value not allowed for object key 698 SELECT json_object('{a,b,NULL,"d e f"}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[]) 699 700 query error pq: json_object\(\): mismatched array dimensions 701 SELECT json_object('{a,b,c,"d e f",g}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[]) 702 703 query error pq: json_object\(\): mismatched array dimensions 704 SELECT json_object('{a,b,c,"d e f"}'::TEXT[],'{1,2,3,"a b c",g}'::TEXT[]) 705 706 query error pq: unknown signature: json_object\(collatedstring\{fr_FR\}\[\]\) 707 SELECT json_object(ARRAY['a'::TEXT COLLATE "fr_FR"]) 708 709 query T 710 SELECT json_object('{}'::TEXT[]) 711 ---- 712 {} 713 714 query T 715 SELECT json_object('{}'::TEXT[], '{}'::TEXT[]) 716 ---- 717 {} 718 719 query T 720 SELECT json_object('{b, 3, a, 1, b, 4, a, 2}'::TEXT[]) 721 ---- 722 {"a": "2", "b": "4"} 723 724 query T 725 SELECT json_object('{b, b, a, a}'::TEXT[], '{1, 2, 3, 4}'::TEXT[]) 726 ---- 727 {"a": "4", "b": "2"} 728 729 query T 730 SELECT json_object('{a,1,b,2,3,NULL,"d e f","a b c"}'::TEXT[]) 731 ---- 732 {"3": null, "a": "1", "b": "2", "d e f": "a b c"} 733 734 query T 735 SELECT json_object('{a,b,"","d e f"}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[]) 736 ---- 737 {"": "3", "a": "1", "b": "2", "d e f": "a b c"} 738 739 query T 740 SELECT json_object('{a,b,c,"d e f"}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[]) 741 ---- 742 {"a": "1", "b": "2", "c": "3", "d e f": "a b c"} 743 744 query error pq: jsonb_object\(\): array must have even number of elements 745 SELECT jsonb_object('{a,b,c}'::TEXT[]) 746 747 query error pq: jsonb_object\(\): null value not allowed for object key 748 SELECT jsonb_object('{NULL, a}'::TEXT[]) 749 750 query error pq: jsonb_object\(\): null value not allowed for object key 751 SELECT jsonb_object('{a,b,NULL,"d e f"}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[]) 752 753 query error pq: jsonb_object\(\): mismatched array dimensions 754 SELECT jsonb_object('{a,b,c,"d e f",g}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[]) 755 756 query error pq: jsonb_object\(\): mismatched array dimensions 757 SELECT jsonb_object('{a,b,c,"d e f"}'::TEXT[],'{1,2,3,"a b c",g}'::TEXT[]) 758 759 query error pq: unknown signature: jsonb_object\(collatedstring\{fr_FR\}\[\]\) 760 SELECT jsonb_object(ARRAY['a'::TEXT COLLATE "fr_FR"]) 761 762 query T 763 SELECT jsonb_object('{}'::TEXT[]) 764 ---- 765 {} 766 767 query T 768 SELECT jsonb_object('{}'::TEXT[], '{}'::TEXT[]) 769 ---- 770 {} 771 772 query T 773 SELECT jsonb_object('{b, 3, a, 1, b, 4, a, 2}'::TEXT[]) 774 ---- 775 {"a": "2", "b": "4"} 776 777 query T 778 SELECT jsonb_object('{b, b, a, a}'::TEXT[], '{1, 2, 3, 4}'::TEXT[]) 779 ---- 780 {"a": "4", "b": "2"} 781 782 query T 783 SELECT jsonb_object('{a,1,b,2,3,NULL,"d e f","a b c"}'::TEXT[]) 784 ---- 785 {"3": null, "a": "1", "b": "2", "d e f": "a b c"} 786 787 query T 788 SELECT jsonb_object('{a,b,"","d e f"}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[]) 789 ---- 790 {"": "3", "a": "1", "b": "2", "d e f": "a b c"} 791 792 query T 793 SELECT jsonb_object('{a,b,c,"d e f"}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[]) 794 ---- 795 {"a": "1", "b": "2", "c": "3", "d e f": "a b c"} 796 797 query error pq: cannot deconstruct an array as an object 798 SELECT json_each('[1]'::JSON) 799 800 query error pq: cannot deconstruct a scalar 801 SELECT json_each('null'::JSON) 802 803 query TT 804 SELECT * FROM json_each('{}') q 805 ---- 806 807 query T colnames 808 SELECT json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') 809 ---- 810 json_each 811 (f1,"[1, 2, 3]") 812 (f2,"{""f3"": 1}") 813 (f4,null) 814 (f5,99) 815 (f6,"""stringy""") 816 817 query TT colnames 818 SELECT * FROM json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q 819 ---- 820 key value 821 f1 [1, 2, 3] 822 f2 {"f3": 1} 823 f4 null 824 f5 99 825 f6 "stringy" 826 827 query error pq: cannot deconstruct an array as an object 828 SELECT jsonb_each('[1]'::JSON) 829 830 query error pq: cannot deconstruct a scalar 831 SELECT jsonb_each('null'::JSON) 832 833 query TT 834 SELECT * FROM jsonb_each('{}') q 835 ---- 836 837 query T colnames 838 SELECT jsonb_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') 839 ---- 840 jsonb_each 841 (f1,"[1, 2, 3]") 842 (f2,"{""f3"": 1}") 843 (f4,null) 844 (f5,99) 845 (f6,"""stringy""") 846 847 query TT colnames 848 SELECT * FROM jsonb_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q 849 ---- 850 key value 851 f1 [1, 2, 3] 852 f2 {"f3": 1} 853 f4 null 854 f5 99 855 f6 "stringy" 856 857 query error pq: cannot deconstruct an array as an object 858 SELECT jsonb_each_text('[1]'::JSON) 859 860 query error pq: cannot deconstruct a scalar 861 SELECT jsonb_each_text('null'::JSON) 862 863 query TT 864 SELECT * FROM jsonb_each_text('{}') q 865 ---- 866 867 query T colnames 868 SELECT jsonb_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') 869 ---- 870 jsonb_each_text 871 (f1,"[1, 2, 3]") 872 (f2,"{""f3"": 1}") 873 (f4,) 874 (f5,99) 875 (f6,stringy) 876 877 query T colnames 878 SELECT jsonb_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q 879 ---- 880 q 881 (f1,"[1, 2, 3]") 882 (f2,"{""f3"": 1}") 883 (f4,) 884 (f5,99) 885 (f6,stringy) 886 887 query TT colnames 888 SELECT * FROM jsonb_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q 889 ---- 890 key value 891 f1 [1, 2, 3] 892 f2 {"f3": 1} 893 f4 NULL 894 f5 99 895 f6 stringy 896 897 query error pq: cannot deconstruct an array as an object 898 SELECT json_each_text('[1]'::JSON) 899 900 query error pq: cannot deconstruct a scalar 901 SELECT json_each_text('null'::JSON) 902 903 query TT 904 SELECT * FROM json_each_text('{}') q 905 ---- 906 907 query TT 908 SELECT * FROM json_each_text('{}') q 909 ---- 910 911 query T colnames 912 SELECT json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') 913 ---- 914 json_each_text 915 (f1,"[1, 2, 3]") 916 (f2,"{""f3"": 1}") 917 (f4,) 918 (f5,99) 919 (f6,stringy) 920 921 query T colnames 922 SELECT json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q 923 ---- 924 q 925 (f1,"[1, 2, 3]") 926 (f2,"{""f3"": 1}") 927 (f4,) 928 (f5,99) 929 (f6,stringy) 930 931 query TT colnames 932 SELECT * FROM json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q 933 ---- 934 key value 935 f1 [1, 2, 3] 936 f2 {"f3": 1} 937 f4 NULL 938 f5 99 939 f6 stringy 940 941 query T 942 SELECT json_set('{"a":1}', '{a}'::STRING[], '2') 943 ---- 944 {"a": 2} 945 946 query T 947 SELECT jsonb_set('{"a":1}', '{b}'::STRING[], '2') 948 ---- 949 {"a": 1, "b": 2} 950 951 statement error path element at position 1 is null 952 SELECT jsonb_set('{"a":1}', ARRAY[null, 'foo']::STRING[], '2') 953 954 statement error path element at position 1 is null 955 SELECT jsonb_set('{"a":1}', '{null,foo}'::STRING[], '2', true) 956 957 statement error path element at position 2 is null 958 SELECT jsonb_set('{"a":1}', '{foo,null}'::STRING[], '2', true) 959 960 query T 961 SELECT jsonb_set('{"a":1}', '{b}'::STRING[], '2', true) 962 ---- 963 {"a": 1, "b": 2} 964 965 query T 966 SELECT jsonb_set('{"a":1}', '{b}'::STRING[], '2', false) 967 ---- 968 {"a": 1} 969 970 query T 971 SELECT jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}'::STRING[], '[2,3,4]', false) 972 ---- 973 [{"f1": [2, 3, 4], "f2": null}, 2, null, 3] 974 975 query T 976 SELECT jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}'::STRING[], '[2,3,4]') 977 ---- 978 [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2] 979 980 query T 981 SELECT jsonb_insert('{"a": [0, 1, 2]}', '{a, 1}'::STRING[], '"new_value"'); 982 ---- 983 {"a": [0, "new_value", 1, 2]} 984 985 query T 986 SELECT jsonb_insert('[0, 1, 2, {"a": ["a", "b", "d"]}, 4]', '{3, a, 2}'::STRING[], '"c"') 987 ---- 988 [0, 1, 2, {"a": ["a", "b", "c", "d"]}, 4] 989 990 query T 991 SELECT jsonb_insert('{"a": "foo"}', '{b}'::STRING[], '"bar"') 992 ---- 993 {"a": "foo", "b": "bar"} 994 995 query T 996 SELECT jsonb_insert(NULL, '{a}', NULL, false) 997 ---- 998 NULL 999 1000 query T 1001 SELECT jsonb_insert('{"a": [0, 1, 2]}', '{a, 1}'::STRING[], '"new_value"', true) 1002 ---- 1003 {"a": [0, 1, "new_value", 2]} 1004 1005 query T 1006 SELECT jsonb_insert('{"a": [0, 1, 2]}', '{a, -1}'::STRING[], '"new_value"', true) 1007 ---- 1008 {"a": [0, 1, 2, "new_value"]} 1009 1010 query error pq: jsonb_insert\(\): cannot replace existing key 1011 SELECT jsonb_insert('{"a": "foo"}', '{a}'::STRING[], '"new_value"', false) 1012 1013 query T 1014 SELECT jsonb_insert('{"a": "foo"}', '{a, 0}'::STRING[], '"new_value"', false) 1015 ---- 1016 {"a": "foo"} 1017 1018 query T 1019 SELECT jsonb_insert('[0, 1, 2, 3]', '{3}'::STRING[], '10', true) 1020 ---- 1021 [0, 1, 2, 3, 10] 1022 1023 statement error cannot set path in scalar 1024 SELECT jsonb_insert('1', '{a}'::STRING[], '10', true) 1025 1026 query T 1027 SELECT jsonb_insert('1', NULL, '10') 1028 ---- 1029 NULL 1030 1031 statement error path element at position 1 is null 1032 SELECT jsonb_insert('{"a": [0, 1, 2], "b": "hello", "c": "world"}', '{NULL, a, 0}'::STRING[], '"new_val"') 1033 1034 statement error path element at position 2 is null 1035 SELECT jsonb_insert('{"a": [0, 1, 2], "b": "hello", "c": "world"}', '{a, NULL, 0}'::STRING[], '"new_val"') 1036 1037 query T 1038 SELECT jsonb_strip_nulls(NULL) 1039 ---- 1040 NULL 1041 1042 query T 1043 SELECT json_strip_nulls('1') 1044 ---- 1045 1 1046 1047 query T 1048 SELECT json_strip_nulls('"a string"') 1049 ---- 1050 "a string" 1051 1052 query T 1053 SELECT json_strip_nulls('null') 1054 ---- 1055 null 1056 1057 query T 1058 SELECT json_strip_nulls('[1,2,null,3,4]') 1059 ---- 1060 [1, 2, null, 3, 4] 1061 1062 query T 1063 SELECT json_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}') 1064 ---- 1065 {"a": 1, "c": [2, null, 3], "d": {"e": 4}} 1066 1067 query T 1068 SELECT json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]') 1069 ---- 1070 [1, {"a": 1, "c": 2}, 3] 1071 1072 query T 1073 SELECT jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {}}') 1074 ---- 1075 {"a": {}, "d": {}} 1076 1077 query T 1078 SELECT jsonb_strip_nulls(NULL) 1079 ---- 1080 NULL 1081 1082 query T 1083 SELECT jsonb_strip_nulls('1') 1084 ---- 1085 1 1086 1087 query T 1088 SELECT jsonb_strip_nulls('"a string"') 1089 ---- 1090 "a string" 1091 1092 query T 1093 SELECT jsonb_strip_nulls('null') 1094 ---- 1095 null 1096 1097 query T 1098 SELECT jsonb_strip_nulls('[1,2,null,3,4]') 1099 ---- 1100 [1, 2, null, 3, 4] 1101 1102 query T 1103 SELECT jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}') 1104 ---- 1105 {"a": 1, "c": [2, null, 3], "d": {"e": 4}} 1106 1107 query T 1108 SELECT jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]') 1109 ---- 1110 [1, {"a": 1, "c": 2}, 3] 1111 1112 query T 1113 SELECT jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {}}') 1114 ---- 1115 {"a": {}, "d": {}} 1116 1117 query error pq: json_array_length\(\): cannot get array length of a non-array 1118 SELECT json_array_length('{"f1":1,"f2":[5,6]}') 1119 1120 query error pq: json_array_length\(\): cannot get array length of a scalar 1121 SELECT json_array_length('4') 1122 1123 query I 1124 SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') 1125 ---- 1126 5 1127 1128 query I 1129 SELECT json_array_length('[]') 1130 ---- 1131 0 1132 1133 query error pq: jsonb_array_length\(\): cannot get array length of a non-array 1134 SELECT jsonb_array_length('{"f1":1,"f2":[5,6]}') 1135 1136 query error pq: jsonb_array_length\(\): cannot get array length of a scalar 1137 SELECT jsonb_array_length('4') 1138 1139 query I 1140 SELECT jsonb_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') 1141 ---- 1142 5 1143 1144 query I 1145 SELECT jsonb_array_length('[]') 1146 ---- 1147 0 1148 1149 query TTT 1150 SELECT row_to_json(row(1,'foo')), row_to_json(NULL), row_to_json(row()) 1151 ---- 1152 {"f1": 1, "f2": "foo"} NULL {} 1153 1154 1155 # TODO(jordan,radu): this should also work without the .*. 1156 query T 1157 select row_to_json(t.*) 1158 from ( 1159 select 1 as a, 2 as b 1160 ) t 1161 ---- 1162 {"a": 1, "b": 2} 1163 1164 query T 1165 SELECT '["a", {"b":1}]'::jsonb #- '{1,b}' 1166 ---- 1167 ["a", {}]