github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/json (about) 1 ## Basic creation 2 3 query TT 4 SELECT '1'::JSONB, '2'::JSON 5 ---- 6 1 2 7 8 query T 9 SELECT pg_typeof(JSON '1') 10 ---- 11 jsonb 12 13 query T 14 SELECT pg_typeof(JSONB '1') 15 ---- 16 jsonb 17 18 query T 19 SELECT '1.00'::JSON 20 ---- 21 1.00 22 23 statement error unexpected EOF 24 SELECT '{'::JSON 25 26 query T 27 SELECT '"hello"'::JSON 28 ---- 29 "hello" 30 31 query T 32 SELECT '"abc\n123"'::JSON 33 ---- 34 "abc\n123" 35 36 query TTT 37 SELECT 'true'::JSON, 'false'::JSON, 'null'::JSON 38 ---- 39 true false null 40 41 query T 42 SELECT '[]'::JSON 43 ---- 44 [] 45 46 query T 47 SELECT '[1, 2, 3]'::JSON 48 ---- 49 [1, 2, 3] 50 51 query T 52 SELECT '[1, "hello", [[[true, false]]]]'::JSON 53 ---- 54 [1, "hello", [[[true, false]]]] 55 56 query T 57 SELECT '[1, "hello", {"a": ["foo", {"b": 3}]}]'::JSON 58 ---- 59 [1, "hello", {"a": ["foo", {"b": 3}]}] 60 61 query T 62 SELECT '{}'::JSON 63 ---- 64 {} 65 66 query T 67 SELECT '{"a": "b", "c": "d"}'::JSON 68 ---- 69 {"a": "b", "c": "d"} 70 71 query T 72 SELECT '{"a": 1, "c": {"foo": "bar"}}'::JSON 73 ---- 74 {"a": 1, "c": {"foo": "bar"}} 75 76 # Only the final occurrence of a key in an object is kept. 77 query T 78 SELECT '{"a": 1, "a": 2}'::JSON 79 ---- 80 {"a": 2} 81 82 query T 83 SELECT NULL::JSON 84 ---- 85 NULL 86 87 statement error arrays of jsonb not allowed.*\nHINT:.*\n.*23468 88 SELECT ARRAY['"hello"'::JSON] 89 90 statement error arrays of jsonb not allowed.*\nHINT:.*\n.*23468 91 SELECT '{}'::JSONB[] 92 93 statement error arrays of jsonb not allowed.*\nHINT:.*\n.*23468 94 CREATE TABLE x (y JSONB[]) 95 96 statement ok 97 CREATE TABLE foo (bar JSON) 98 99 statement ok 100 INSERT INTO foo VALUES 101 ('{"a": "b"}'), 102 ('[1, 2, 3]'), 103 ('"hello"'), 104 ('1.000'), 105 ('true'), 106 ('false'), 107 (NULL), 108 ('{"x": [1, 2, 3]}'), 109 ('{"x": {"y": "z"}}') 110 111 query T rowsort 112 SELECT bar FROM foo 113 ---- 114 {"a": "b"} 115 [1, 2, 3] 116 "hello" 117 1.000 118 true 119 false 120 NULL 121 {"x": [1, 2, 3]} 122 {"x": {"y": "z"}} 123 124 query T 125 SELECT bar FROM foo WHERE bar->>'a' = 'b' 126 ---- 127 {"a": "b"} 128 129 query T 130 SELECT bar FROM foo WHERE bar ? 'a' 131 ---- 132 {"a": "b"} 133 134 query BBBBBBB 135 VALUES ( 136 '"hello"'::JSONB ? 'hello', 137 '"hello"'::JSONB ? 'goodbye', 138 '"hello"'::JSONB ? 'ello', 139 '"hello"'::JSONB ? 'h', 140 'true'::JSONB ? 'true', 141 '1'::JSONB ? '1', 142 'null'::JSONB ? 'null' 143 ) 144 ---- 145 true false false false false false false 146 147 query T 148 SELECT bar FROM foo WHERE bar ? 'hello' 149 ---- 150 "hello" 151 152 query T 153 SELECT bar FROM foo WHERE bar ? 'goodbye' 154 ---- 155 156 query T 157 SELECT bar FROM foo WHERE bar ?| ARRAY['a','b'] 158 ---- 159 {"a": "b"} 160 161 query T 162 SELECT bar FROM foo WHERE bar ?& ARRAY['a','b'] 163 ---- 164 165 # ?| and ?& ignore NULLs. 166 query T 167 SELECT bar FROM foo WHERE bar ?| ARRAY['a', null] 168 ---- 169 {"a": "b"} 170 171 # TODO(justin): #29355 172 # query T 173 # SELECT bar FROM foo WHERE bar ?| ARRAY[null, null]::STRING[] 174 # ---- 175 176 query T 177 SELECT bar FROM foo WHERE bar ?& ARRAY['a', null] 178 ---- 179 {"a": "b"} 180 181 query T 182 SELECT bar FROM foo WHERE bar->'a' = '"b"'::JSON 183 ---- 184 {"a": "b"} 185 186 statement error pgcode 0A000 can't order by column type jsonb.*\nHINT.*\n.*35706 187 SELECT bar FROM foo ORDER BY bar 188 189 statement error pgcode 0A000 column k is of type jsonb and thus is not indexable 190 CREATE TABLE pk (k JSON PRIMARY KEY) 191 192 query T rowsort 193 SELECT bar->'a' FROM foo 194 ---- 195 "b" 196 NULL 197 NULL 198 NULL 199 NULL 200 NULL 201 NULL 202 NULL 203 NULL 204 205 query T 206 SELECT * from foo where bar->'x' = '[1]' 207 ---- 208 209 query T 210 SELECT * from foo where bar->'x' = '{}' 211 ---- 212 213 statement ok 214 DELETE FROM foo 215 216 statement ok 217 INSERT INTO foo VALUES ('{"a": {"c": "d"}}'); 218 219 query TT 220 SELECT bar->'a'->'c', bar->'a'->>'c' FROM foo 221 ---- 222 "d" d 223 224 statement ok 225 CREATE TABLE multiple (a JSON, b JSON) 226 227 statement ok 228 INSERT INTO multiple VALUES ('{"a":"b"}', '[1,2,3,4,"foo"]') 229 230 query T 231 SELECT a FROM multiple 232 ---- 233 {"a": "b"} 234 235 query T 236 SELECT b FROM multiple 237 ---- 238 [1, 2, 3, 4, "foo"] 239 240 ## Comparisons 241 242 # We opt to not expose <, >, <=, >= at this time, to avoid having to commit to 243 # an ordering. 244 query B 245 SELECT '1'::JSON = '1'::JSON 246 ---- 247 true 248 249 query B 250 SELECT '1'::JSON = '1' 251 ---- 252 true 253 254 query B 255 SELECT '1'::JSON = '2'::JSON 256 ---- 257 false 258 259 query B 260 SELECT '1.00'::JSON = '1'::JSON 261 ---- 262 true 263 264 query BB 265 SELECT '"hello"'::JSON = '"hello"'::JSON, '"hello"'::JSON = '"goodbye"'::JSON 266 ---- 267 true false 268 269 query B 270 SELECT '"hello"'::JSON IN ('"hello"'::JSON, '1'::JSON, '[]'::JSON) 271 ---- 272 true 273 274 query B 275 SELECT 'false'::JSON IN ('"hello"'::JSON, '1'::JSON, '[]'::JSON) 276 ---- 277 false 278 279 ## Operators 280 281 query T 282 SELECT '{"a": 1}'::JSONB->'a' 283 ---- 284 1 285 286 query T 287 SELECT pg_typeof('{"a": 1}'::JSONB->'a') 288 ---- 289 jsonb 290 291 query T 292 SELECT '{"a": 1, "b": 2}'::JSONB->'b' 293 ---- 294 2 295 296 query T 297 SELECT '{"a": 1, "b": {"c": 3}}'::JSONB->'b'->'c' 298 ---- 299 3 300 301 query TT 302 SELECT '{"a": 1, "b": 2}'::JSONB->'c', '{"c": 1}'::JSONB->'a' 303 ---- 304 NULL NULL 305 306 query TT 307 SELECT '2'::JSONB->'b', '[1,2,3]'::JSONB->'0' 308 ---- 309 NULL NULL 310 311 query T 312 SELECT '[1, 2, 3]'::JSONB->0 313 ---- 314 1 315 316 query T 317 SELECT '[1, 2, 3]'::JSONB->3 318 ---- 319 NULL 320 321 query T 322 SELECT '{"a": "b"}'::JSONB->>'a' 323 ---- 324 b 325 326 query T 327 SELECT '[null]'::JSONB->>0 328 ---- 329 NULL 330 331 query T 332 SELECT '{"a":null}'::JSONB->>'a' 333 ---- 334 NULL 335 336 query T 337 SELECT pg_typeof('{"a": 1}'::JSONB->>'a') 338 ---- 339 text 340 341 query T 342 SELECT '{"a": 1, "b": 2}'::JSONB->>'b' 343 ---- 344 2 345 346 query TT 347 SELECT '{"a": 1, "b": 2}'::JSONB->>'c', '{"c": 1}'::JSONB->>'a' 348 ---- 349 NULL NULL 350 351 query TT 352 SELECT '2'::JSONB->>'b', '[1,2,3]'::JSONB->>'0' 353 ---- 354 NULL NULL 355 356 query T 357 SELECT '[1, 2, 3]'::JSONB->>0 358 ---- 359 1 360 361 query T 362 SELECT '[1, 2, 3]'::JSONB->>3 363 ---- 364 NULL 365 366 query T 367 SELECT '{"a": 1}'::JSONB#>'{a}'::STRING[] 368 ---- 369 1 370 371 query T 372 SELECT '{"a": {"b": "c"}}'::JSONB#>'{a,b}'::STRING[] 373 ---- 374 "c" 375 376 query T 377 SELECT '{"a": ["b"]}'::JSONB#>'{a,b}'::STRING[] 378 ---- 379 NULL 380 381 query T 382 SELECT '{"a": ["b"]}'::JSONB#>'{a,0}'::STRING[] 383 ---- 384 "b" 385 386 query T 387 SELECT '{"a": 1}'::JSONB#>>ARRAY['foo', null] 388 ---- 389 NULL 390 391 query T 392 SELECT '{"a": 1}'::JSONB#>>'{a}'::STRING[] 393 ---- 394 1 395 396 query T 397 SELECT '{"a": {"b": "c"}}'::JSONB#>>'{a,b}'::STRING[] 398 ---- 399 c 400 401 query T 402 SELECT '{"a": ["b"]}'::JSONB#>>'{a,b}'::STRING[] 403 ---- 404 NULL 405 406 query T 407 SELECT '{"a": ["b"]}'::JSONB#>>'{a,0}'::STRING[] 408 ---- 409 b 410 411 query T 412 SELECT '{"a": [null]}'::JSONB#>>'{a,0}'::STRING[] 413 ---- 414 NULL 415 416 query BB 417 SELECT '{"a": 1}'::JSONB ? 'a', '{"a": 1}'::JSONB ? 'b' 418 ---- 419 true false 420 421 query BB 422 SELECT '{"a": 1, "b": 1}'::JSONB ? 'a', '{"a": 1, "b": 1}'::JSONB ? 'b' 423 ---- 424 true true 425 426 query BB 427 SELECT '{"a": 1}'::JSONB ?| ARRAY['a', 'b'], '{"b": 1}'::JSONB ?| ARRAY['a', 'b'] 428 ---- 429 true true 430 431 query B 432 SELECT '{"c": 1}'::JSONB ?| ARRAY['a', 'b'] 433 ---- 434 false 435 436 query BB 437 SELECT '{"a": 1}'::JSONB ?& ARRAY['a', 'b'], '{"b": 1}'::JSONB ?& ARRAY['a', 'b'] 438 ---- 439 false false 440 441 query B 442 SELECT '{"a": 1, "b": 1, "c": 1}'::JSONB ?& ARRAY['a', 'b'] 443 ---- 444 true 445 446 ## Arrays do not `?` their stringified indices. 447 query B 448 SELECT '[1, 2, 3]'::JSONB ? '0' 449 ---- 450 false 451 452 ## Arrays `?` string elements. 453 query B 454 SELECT '["foo", "bar", "baz"]'::JSONB ? 'foo' 455 ---- 456 true 457 458 query B 459 SELECT '["foo", "bar", "baz"]'::JSONB ? 'baz' 460 ---- 461 true 462 463 query B 464 SELECT '["foo", "bar", "baz"]'::JSONB ? 'gup' 465 ---- 466 false 467 468 query B 469 SELECT '["foo", "bar", "baz"]'::JSONB ?| ARRAY['foo', 'gup'] 470 ---- 471 true 472 473 query B 474 SELECT '["foo", "bar", "baz"]'::JSONB ?| ARRAY['buh', 'gup'] 475 ---- 476 false 477 478 query B 479 SELECT '["foo", "bar", "baz"]'::JSONB ?& ARRAY['foo', 'bar'] 480 ---- 481 true 482 483 query B 484 SELECT '["foo", "bar", "baz"]'::JSONB ?& ARRAY['foo', 'buh'] 485 ---- 486 false 487 488 query T 489 SELECT '{"a": 1}'::JSONB - 'a' 490 ---- 491 {} 492 493 query T 494 SELECT '{"a": 1}'::JSONB - 'b' 495 ---- 496 {"a": 1} 497 498 # `-` is one of the very few cases that PG errors in a JSON type mismatch with operators. 499 query T 500 SELECT '[1,2,3]'::JSONB - 0 501 ---- 502 [2, 3] 503 504 query T 505 SELECT '[1,2,3]'::JSONB - 1 506 ---- 507 [1, 3] 508 509 statement error pgcode 22023 cannot delete from scalar 510 SELECT '3'::JSONB - 'b' 511 512 statement error pgcode 22023 cannot delete from object using integer index 513 SELECT '{}'::JSONB - 1 514 515 query B 516 SELECT '[1, 2, 3]'::JSONB <@ '[1, 2]'::JSONB 517 ---- 518 false 519 520 query B 521 SELECT '[1, 2]'::JSONB <@ '[1, 2, 3]'::JSONB 522 ---- 523 true 524 525 query B 526 SELECT '[1, 2]'::JSONB @> '[1, 2, 3]'::JSONB 527 ---- 528 false 529 530 query B 531 SELECT '[1, 2, 3]'::JSONB @> '[1, 2]'::JSONB 532 ---- 533 true 534 535 query B 536 SELECT '{"a": [1, 2, 3]}'::JSONB->'a' @> '2'::JSONB 537 ---- 538 true 539 540 statement ok 541 CREATE TABLE x (j JSONB) 542 543 statement ok 544 INSERT INTO x VALUES ('{"a": [1,2,3]}') 545 546 query B 547 SELECT true FROM x WHERE j->'a' @> '2'::JSONB 548 ---- 549 true 550 551 statement ok 552 CREATE INVERTED INDEX ON x (j) 553 554 query B 555 SELECT true FROM x WHERE j->'a' @> '2'::JSONB 556 ---- 557 true 558 559 query T 560 SELECT '{"foo": {"bar": 1}}'::JSONB #- ARRAY['foo', 'bar'] 561 ---- 562 {"foo": {}} 563 564 statement error path element at position 1 is null 565 SELECT '{"foo": {"bar": 1}}'::JSONB #- ARRAY[null, 'foo'] 566 567 statement error path element at position 2 is null 568 SELECT '{"foo": {"bar": 1}}'::JSONB #- ARRAY['foo', null] 569 570 query T 571 SELECT '{"foo": {"bar": 1}}'::JSONB #- ARRAY['foo'] 572 ---- 573 {} 574 575 query T 576 SELECT '{"foo": {"bar": 1}}'::JSONB #- ARRAY['bar'] 577 ---- 578 {"foo": {"bar": 1}} 579 580 query T 581 SELECT '{"foo": {"bar": 1}, "one": 1, "two": 2}'::JSONB #- ARRAY['one'] 582 ---- 583 {"foo": {"bar": 1}, "two": 2} 584 585 query T 586 SELECT '{}'::JSONB #- ARRAY['foo'] 587 ---- 588 {} 589 590 query T 591 SELECT '{"foo": {"bar": 1}}'::JSONB #- ARRAY[''] 592 ---- 593 {"foo": {"bar": 1}} 594 595 query T 596 SELECT '{"a": "b"}'::JSONB::STRING 597 ---- 598 {"a": "b"} 599 600 query T 601 SELECT CAST('{"a": "b"}'::JSONB AS STRING) 602 ---- 603 {"a": "b"} 604 605 query T 606 SELECT '["1", "2", "3"]'::JSONB - '1' 607 ---- 608 ["2", "3"] 609 610 query T 611 SELECT '["1", "2", "1", "2", "3"]'::JSONB - '2' 612 ---- 613 ["1", "1", "3"] 614 615 query T 616 SELECT '["1", "2", "3"]'::JSONB - '4' 617 ---- 618 ["1", "2", "3"] 619 620 query T 621 SELECT '[]'::JSONB - '1' 622 ---- 623 [] 624 625 query T 626 SELECT '["1", "2", "3"]'::JSONB - '' 627 ---- 628 ["1", "2", "3"] 629 630 query T 631 SELECT '[1, "1", 1.0]'::JSONB - '1' 632 ---- 633 [1, 1.0] 634 635 query T 636 SELECT '[1, 2, 3]'::JSONB #- ARRAY['0'] 637 ---- 638 [2, 3] 639 640 query T 641 SELECT '[1, 2, 3]'::JSONB #- ARRAY['3'] 642 ---- 643 [1, 2, 3] 644 645 query T 646 SELECT '[]'::JSONB #- ARRAY['0'] 647 ---- 648 [] 649 650 statement error pgcode 22P02 a path element is not an integer: foo 651 SELECT '["foo"]'::JSONB #- ARRAY['foo'] 652 653 query T 654 SELECT '{"a": ["foo"]}'::JSONB #- ARRAY['a', '0'] 655 ---- 656 {"a": []} 657 658 query T 659 SELECT '{"a": ["foo", "bar"]}'::JSONB #- ARRAY['a', '1'] 660 ---- 661 {"a": ["foo"]} 662 663 query T 664 SELECT '{"a": []}'::JSONB #- ARRAY['a', '0'] 665 ---- 666 {"a": []} 667 668 query T 669 SELECT '{"a":123,"b":456,"c":567}'::JSONB - array[]:::text[]; 670 ---- 671 {"a": 123, "b": 456, "c": 567} 672 673 query T 674 SELECT '{"a":123,"b":456,"c":567}'::JSONB - array['a','c']; 675 ---- 676 {"b": 456} 677 678 query T 679 SELECT '{"a":123,"c":"asdf"}'::JSONB - array['a','c']; 680 ---- 681 {} 682 683 query T 684 SELECT '{}'::JSONB - array['a','c']; 685 ---- 686 {} 687 688 query T 689 SELECT '{"b": [], "c": {"a": "b"}}'::JSONB - array['a']; 690 ---- 691 {"b": [], "c": {"a": "b"}} 692 693 # Regression test for #34756. 694 query T 695 SELECT '{"b": [], "c": {"a": "b"}}'::JSONB - array['foo', NULL] 696 ---- 697 {"b": [], "c": {"a": "b"}} 698 699 statement error pgcode 22P02 a path element is not an integer: foo 700 SELECT '{"a": {"b": ["foo"]}}'::JSONB #- ARRAY['a', 'b', 'foo'] 701 702 subtest single_family_jsonb 703 704 statement ok 705 CREATE TABLE json_family (a INT PRIMARY KEY, b JSONB, FAMILY fam0(a), FAMILY fam1(b)) 706 707 statement ok 708 INSERT INTO json_family VALUES(0,'{}') 709 710 statement ok 711 INSERT INTO json_family VALUES(1,'{"a":123,"c":"asdf"}') 712 713 query IT colnames 714 SELECT a, b FROM json_family ORDER BY a 715 ---- 716 a b 717 0 {} 718 1 {"a": 123, "c": "asdf"} 719 720 statement ok 721 DROP TABLE json_family