1SELECT 2 jsonb '{"a": 12}' @ ? '$'; 3 4SELECT 5 jsonb '{"a": 12}' @ ? '1'; 6 7SELECT 8 jsonb '{"a": 12}' @ ? '$.a.b'; 9 10SELECT 11 jsonb '{"a": 12}' @ ? '$.b'; 12 13SELECT 14 jsonb '{"a": 12}' @ ? '$.a + 2'; 15 16SELECT 17 jsonb '{"a": 12}' @ ? '$.b + 2'; 18 19SELECT 20 jsonb '{"a": {"a": 12}}' @ ? '$.a.a'; 21 22SELECT 23 jsonb '{"a": {"a": 12}}' @ ? '$.*.a'; 24 25SELECT 26 jsonb '{"b": {"a": 12}}' @ ? '$.*.a'; 27 28SELECT 29 jsonb '{"b": {"a": 12}}' @ ? '$.*.b'; 30 31SELECT 32 jsonb '{"b": {"a": 12}}' @ ? 'strict $.*.b'; 33 34SELECT 35 jsonb '{}' @ ? '$.*'; 36 37SELECT 38 jsonb '{"a": 1}' @ ? '$.*'; 39 40SELECT 41 jsonb '{"a": {"b": 1}}' @ ? 'lax $.**{1}'; 42 43SELECT 44 jsonb '{"a": {"b": 1}}' @ ? 'lax $.**{2}'; 45 46SELECT 47 jsonb '{"a": {"b": 1}}' @ ? 'lax $.**{3}'; 48 49SELECT 50 jsonb '[]' @ ? '$[*]'; 51 52SELECT 53 jsonb '[1]' @ ? '$[*]'; 54 55SELECT 56 jsonb '[1]' @ ? '$[1]'; 57 58SELECT 59 jsonb '[1]' @ ? 'strict $[1]'; 60 61SELECT 62 jsonb_path_query('[1]', 'strict $[1]'); 63 64SELECT 65 jsonb_path_query('[1]', 'strict $[1]', silent => TRUE); 66 67SELECT 68 jsonb '[1]' @ ? 'lax $[10000000000000000]'; 69 70SELECT 71 jsonb '[1]' @ ? 'strict $[10000000000000000]'; 72 73SELECT 74 jsonb_path_query('[1]', 'lax $[10000000000000000]'); 75 76SELECT 77 jsonb_path_query('[1]', 'strict $[10000000000000000]'); 78 79SELECT 80 jsonb '[1]' @ ? '$[0]'; 81 82SELECT 83 jsonb '[1]' @ ? '$[0.3]'; 84 85SELECT 86 jsonb '[1]' @ ? '$[0.5]'; 87 88SELECT 89 jsonb '[1]' @ ? '$[0.9]'; 90 91SELECT 92 jsonb '[1]' @ ? '$[1.2]'; 93 94SELECT 95 jsonb '[1]' @ ? 'strict $[1.2]'; 96 97SELECT 98 jsonb '{"a": [1,2,3], "b": [3,4,5]}' @ ? '$ ? (@.a[*] > @.b[*])'; 99 100SELECT 101 jsonb '{"a": [1,2,3], "b": [3,4,5]}' @ ? '$ ? (@.a[*] >= @.b[*])'; 102 103SELECT 104 jsonb '{"a": [1,2,3], "b": [3,4,"5"]}' @ ? '$ ? (@.a[*] >= @.b[*])'; 105 106SELECT 107 jsonb '{"a": [1,2,3], "b": [3,4,"5"]}' @ ? 'strict $ ? (@.a[*] >= @.b[*])'; 108 109SELECT 110 jsonb '{"a": [1,2,3], "b": [3,4,null]}' @ ? '$ ? (@.a[*] >= @.b[*])'; 111 112SELECT 113 jsonb '1' @ ? '$ ? ((@ == "1") is unknown)'; 114 115SELECT 116 jsonb '1' @ ? '$ ? ((@ == 1) is unknown)'; 117 118SELECT 119 jsonb '[{"a": 1}, {"a": 2}]' @ ? '$[0 to 1] ? (@.a > 1)'; 120 121SELECT 122 jsonb_path_exists('[{"a": 1}, {"a": 2}, 3]', 'lax $[*].a', silent => FALSE); 123 124SELECT 125 jsonb_path_exists('[{"a": 1}, {"a": 2}, 3]', 'lax $[*].a', silent => TRUE); 126 127SELECT 128 jsonb_path_exists('[{"a": 1}, {"a": 2}, 3]', 'strict $[*].a', silent => FALSE); 129 130SELECT 131 jsonb_path_exists('[{"a": 1}, {"a": 2}, 3]', 'strict $[*].a', silent => TRUE); 132 133SELECT 134 jsonb_path_query('1', 'lax $.a'); 135 136SELECT 137 jsonb_path_query('1', 'strict $.a'); 138 139SELECT 140 jsonb_path_query('1', 'strict $.*'); 141 142SELECT 143 jsonb_path_query('1', 'strict $.a', silent => TRUE); 144 145SELECT 146 jsonb_path_query('1', 'strict $.*', silent => TRUE); 147 148SELECT 149 jsonb_path_query('[]', 'lax $.a'); 150 151SELECT 152 jsonb_path_query('[]', 'strict $.a'); 153 154SELECT 155 jsonb_path_query('[]', 'strict $.a', silent => TRUE); 156 157SELECT 158 jsonb_path_query('{}', 'lax $.a'); 159 160SELECT 161 jsonb_path_query('{}', 'strict $.a'); 162 163SELECT 164 jsonb_path_query('{}', 'strict $.a', silent => TRUE); 165 166SELECT 167 jsonb_path_query('1', 'strict $[1]'); 168 169SELECT 170 jsonb_path_query('1', 'strict $[*]'); 171 172SELECT 173 jsonb_path_query('[]', 'strict $[1]'); 174 175SELECT 176 jsonb_path_query('[]', 'strict $["a"]'); 177 178SELECT 179 jsonb_path_query('1', 'strict $[1]', silent => TRUE); 180 181SELECT 182 jsonb_path_query('1', 'strict $[*]', silent => TRUE); 183 184SELECT 185 jsonb_path_query('[]', 'strict $[1]', silent => TRUE); 186 187SELECT 188 jsonb_path_query('[]', 'strict $["a"]', silent => TRUE); 189 190SELECT 191 jsonb_path_query('{"a": 12, "b": {"a": 13}}', '$.a'); 192 193SELECT 194 jsonb_path_query('{"a": 12, "b": {"a": 13}}', '$.b'); 195 196SELECT 197 jsonb_path_query('{"a": 12, "b": {"a": 13}}', '$.*'); 198 199SELECT 200 jsonb_path_query('{"a": 12, "b": {"a": 13}}', 'lax $.*.a'); 201 202SELECT 203 jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[*].a'); 204 205SELECT 206 jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[*].*'); 207 208SELECT 209 jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0].a'); 210 211SELECT 212 jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[1].a'); 213 214SELECT 215 jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[2].a'); 216 217SELECT 218 jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0,1].a'); 219 220SELECT 221 jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0 to 10].a'); 222 223SELECT 224 jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0 to 10 / 0].a'); 225 226SELECT 227 jsonb_path_query('[12, {"a": 13}, {"b": 14}, "ccc", true]', '$[2.5 - 1 to $.size() - 2]'); 228 229SELECT 230 jsonb_path_query('1', 'lax $[0]'); 231 232SELECT 233 jsonb_path_query('1', 'lax $[*]'); 234 235SELECT 236 jsonb_path_query('[1]', 'lax $[0]'); 237 238SELECT 239 jsonb_path_query('[1]', 'lax $[*]'); 240 241SELECT 242 jsonb_path_query('[1,2,3]', 'lax $[*]'); 243 244SELECT 245 jsonb_path_query('[1,2,3]', 'strict $[*].a'); 246 247SELECT 248 jsonb_path_query('[1,2,3]', 'strict $[*].a', silent => TRUE); 249 250SELECT 251 jsonb_path_query('[]', '$[last]'); 252 253SELECT 254 jsonb_path_query('[]', '$[last ? (exists(last))]'); 255 256SELECT 257 jsonb_path_query('[]', 'strict $[last]'); 258 259SELECT 260 jsonb_path_query('[]', 'strict $[last]', silent => TRUE); 261 262SELECT 263 jsonb_path_query('[1]', '$[last]'); 264 265SELECT 266 jsonb_path_query('[1,2,3]', '$[last]'); 267 268SELECT 269 jsonb_path_query('[1,2,3]', '$[last - 1]'); 270 271SELECT 272 jsonb_path_query('[1,2,3]', '$[last ? (@.type() == "number")]'); 273 274SELECT 275 jsonb_path_query('[1,2,3]', '$[last ? (@.type() == "string")]'); 276 277SELECT 278 jsonb_path_query('[1,2,3]', '$[last ? (@.type() == "string")]', silent => TRUE); 279 280SELECT 281 * 282FROM 283 jsonb_path_query('{"a": 10}', '$'); 284 285SELECT 286 * 287FROM 288 jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)'); 289 290SELECT 291 * 292FROM 293 jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)', '1'); 294 295SELECT 296 * 297FROM 298 jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)', '[{"value" : 13}]'); 299 300SELECT 301 * 302FROM 303 jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)', '{"value" : 13}'); 304 305SELECT 306 * 307FROM 308 jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)', '{"value" : 8}'); 309 310SELECT 311 * 312FROM 313 jsonb_path_query('{"a": 10}', '$.a ? (@ < $value)', '{"value" : 13}'); 314 315SELECT 316 * 317FROM 318 jsonb_path_query('[10,11,12,13,14,15]', '$[*] ? (@ < $value)', '{"value" : 13}'); 319 320SELECT 321 * 322FROM 323 jsonb_path_query('[10,11,12,13,14,15]', '$[0,1] ? (@ < $x.value)', '{"x": {"value" : 13}}'); 324 325SELECT 326 * 327FROM 328 jsonb_path_query('[10,11,12,13,14,15]', '$[0 to 2] ? (@ < $value)', '{"value" : 15}'); 329 330SELECT 331 * 332FROM 333 jsonb_path_query('[1,"1",2,"2",null]', '$[*] ? (@ == "1")'); 334 335SELECT 336 * 337FROM 338 jsonb_path_query('[1,"1",2,"2",null]', '$[*] ? (@ == $value)', '{"value" : "1"}'); 339 340SELECT 341 * 342FROM 343 jsonb_path_query('[1,"1",2,"2",null]', '$[*] ? (@ == $value)', '{"value" : null}'); 344 345SELECT 346 * 347FROM 348 jsonb_path_query('[1, "2", null]', '$[*] ? (@ != null)'); 349 350SELECT 351 * 352FROM 353 jsonb_path_query('[1, "2", null]', '$[*] ? (@ == null)'); 354 355SELECT 356 * 357FROM 358 jsonb_path_query('{}', '$ ? (@ == @)'); 359 360SELECT 361 * 362FROM 363 jsonb_path_query('[]', 'strict $ ? (@ == @)'); 364 365SELECT 366 jsonb_path_query('{"a": {"b": 1}}', 'lax $.**'); 367 368SELECT 369 jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{0}'); 370 371SELECT 372 jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{0 to last}'); 373 374SELECT 375 jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{1}'); 376 377SELECT 378 jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{1 to last}'); 379 380SELECT 381 jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{2}'); 382 383SELECT 384 jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{2 to last}'); 385 386SELECT 387 jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{3 to last}'); 388 389SELECT 390 jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{last}'); 391 392SELECT 393 jsonb_path_query('{"a": {"b": 1}}', 'lax $.**.b ? (@ > 0)'); 394 395SELECT 396 jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{0}.b ? (@ > 0)'); 397 398SELECT 399 jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{1}.b ? (@ > 0)'); 400 401SELECT 402 jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{0 to last}.b ? (@ > 0)'); 403 404SELECT 405 jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{1 to last}.b ? (@ > 0)'); 406 407SELECT 408 jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{1 to 2}.b ? (@ > 0)'); 409 410SELECT 411 jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**.b ? (@ > 0)'); 412 413SELECT 414 jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{0}.b ? (@ > 0)'); 415 416SELECT 417 jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{1}.b ? (@ > 0)'); 418 419SELECT 420 jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{0 to last}.b ? (@ > 0)'); 421 422SELECT 423 jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{1 to last}.b ? (@ > 0)'); 424 425SELECT 426 jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{1 to 2}.b ? (@ > 0)'); 427 428SELECT 429 jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{2 to 3}.b ? (@ > 0)'); 430 431SELECT 432 jsonb '{"a": {"b": 1}}' @ ? '$.**.b ? ( @ > 0)'; 433 434SELECT 435 jsonb '{"a": {"b": 1}}' @ ? '$.**{0}.b ? ( @ > 0)'; 436 437SELECT 438 jsonb '{"a": {"b": 1}}' @ ? '$.**{1}.b ? ( @ > 0)'; 439 440SELECT 441 jsonb '{"a": {"b": 1}}' @ ? '$.**{0 to last}.b ? ( @ > 0)'; 442 443SELECT 444 jsonb '{"a": {"b": 1}}' @ ? '$.**{1 to last}.b ? ( @ > 0)'; 445 446SELECT 447 jsonb '{"a": {"b": 1}}' @ ? '$.**{1 to 2}.b ? ( @ > 0)'; 448 449SELECT 450 jsonb '{"a": {"c": {"b": 1}}}' @ ? '$.**.b ? ( @ > 0)'; 451 452SELECT 453 jsonb '{"a": {"c": {"b": 1}}}' @ ? '$.**{0}.b ? ( @ > 0)'; 454 455SELECT 456 jsonb '{"a": {"c": {"b": 1}}}' @ ? '$.**{1}.b ? ( @ > 0)'; 457 458SELECT 459 jsonb '{"a": {"c": {"b": 1}}}' @ ? '$.**{0 to last}.b ? ( @ > 0)'; 460 461SELECT 462 jsonb '{"a": {"c": {"b": 1}}}' @ ? '$.**{1 to last}.b ? ( @ > 0)'; 463 464SELECT 465 jsonb '{"a": {"c": {"b": 1}}}' @ ? '$.**{1 to 2}.b ? ( @ > 0)'; 466 467SELECT 468 jsonb '{"a": {"c": {"b": 1}}}' @ ? '$.**{2 to 3}.b ? ( @ > 0)'; 469 470SELECT 471 jsonb_path_query('{"g": {"x": 2}}', '$.g ? (exists (@.x))'); 472 473SELECT 474 jsonb_path_query('{"g": {"x": 2}}', '$.g ? (exists (@.y))'); 475 476SELECT 477 jsonb_path_query('{"g": {"x": 2}}', '$.g ? (exists (@.x ? (@ >= 2) ))'); 478 479SELECT 480 jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'lax $.g ? (exists (@.x))'); 481 482SELECT 483 jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'lax $.g ? (exists (@.x + "3"))'); 484 485SELECT 486 jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'lax $.g ? ((exists (@.x + "3")) is unknown)'); 487 488SELECT 489 jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g[*] ? (exists (@.x))'); 490 491SELECT 492 jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g[*] ? ((exists (@.x)) is unknown)'); 493 494SELECT 495 jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g ? (exists (@[*].x))'); 496 497SELECT 498 jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g ? ((exists (@[*].x)) is unknown)'); 499 500--test ternary logic 501SELECT 502 x, 503 y, 504 jsonb_path_query('[true, false, null]', '$[*] ? (@ == true && ($x == true && $y == true) || 505 @ == false && !($x == true && $y == true) || 506 @ == null && ($x == true && $y == true) is unknown)', jsonb_build_object('x', x, 'y', y)) AS "x && y" 507FROM ( 508 VALUES (jsonb 'true'), 509 ('false'), 510 ('"null"')) x (x), 511 ( 512 VALUES (jsonb 'true'), ('false'), ('"null"')) y (y); 513 514SELECT 515 x, 516 y, 517 jsonb_path_query('[true, false, null]', '$[*] ? (@ == true && ($x == true || $y == true) || 518 @ == false && !($x == true || $y == true) || 519 @ == null && ($x == true || $y == true) is unknown)', jsonb_build_object('x', x, 'y', y)) AS "x || y" 520FROM ( 521 VALUES (jsonb 'true'), 522 ('false'), 523 ('"null"')) x (x), 524 ( 525 VALUES (jsonb 'true'), ('false'), ('"null"')) y (y); 526 527SELECT 528 jsonb '{"a": 1, "b":1}' @ ? '$ ? (@.a == @.b)'; 529 530SELECT 531 jsonb '{"c": {"a": 1, "b":1}}' @ ? '$ ? (@.a == @.b)'; 532 533SELECT 534 jsonb '{"c": {"a": 1, "b":1}}' @ ? '$.c ? (@.a == @.b)'; 535 536SELECT 537 jsonb '{"c": {"a": 1, "b":1}}' @ ? '$.c ? ($.c.a == @.b)'; 538 539SELECT 540 jsonb '{"c": {"a": 1, "b":1}}' @ ? '$.* ? (@.a == @.b)'; 541 542SELECT 543 jsonb '{"a": 1, "b":1}' @ ? '$.** ? (@.a == @.b)'; 544 545SELECT 546 jsonb '{"c": {"a": 1, "b":1}}' @ ? '$.** ? (@.a == @.b)'; 547 548SELECT 549 jsonb_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == 1 + 1)'); 550 551SELECT 552 jsonb_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == (1 + 1))'); 553 554SELECT 555 jsonb_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == @.b + 1)'); 556 557SELECT 558 jsonb_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == (@.b + 1))'); 559 560SELECT 561 jsonb '{"c": {"a": -1, "b":1}}' @ ? '$.** ? (@.a == - 1)'; 562 563SELECT 564 jsonb '{"c": {"a": -1, "b":1}}' @ ? '$.** ? (@.a == -1)'; 565 566SELECT 567 jsonb '{"c": {"a": -1, "b":1}}' @ ? '$.** ? (@.a == -@.b)'; 568 569SELECT 570 jsonb '{"c": {"a": -1, "b":1}}' @ ? '$.** ? (@.a == - @.b)'; 571 572SELECT 573 jsonb '{"c": {"a": 0, "b":1}}' @ ? '$.** ? (@.a == 1 - @.b)'; 574 575SELECT 576 jsonb '{"c": {"a": 2, "b":1}}' @ ? '$.** ? (@.a == 1 - - @.b)'; 577 578SELECT 579 jsonb '{"c": {"a": 0, "b":1}}' @ ? '$.** ? (@.a == 1 - +@.b)'; 580 581SELECT 582 jsonb '[1,2,3]' @ ? '$ ? (+@[*] > +2)'; 583 584SELECT 585 jsonb '[1,2,3]' @ ? '$ ? (+@[*] > +3)'; 586 587SELECT 588 jsonb '[1,2,3]' @ ? '$ ? (-@[*] < -2)'; 589 590SELECT 591 jsonb '[1,2,3]' @ ? '$ ? (-@[*] < -3)'; 592 593SELECT 594 jsonb '1' @ ? '$ ? ($ > 0)'; 595 596-- arithmetic errors 597SELECT 598 jsonb_path_query('[1,2,0,3]', '$[*] ? (2 / @ > 0)'); 599 600SELECT 601 jsonb_path_query('[1,2,0,3]', '$[*] ? ((2 / @ > 0) is unknown)'); 602 603SELECT 604 jsonb_path_query('0', '1 / $'); 605 606SELECT 607 jsonb_path_query('0', '1 / $ + 2'); 608 609SELECT 610 jsonb_path_query('0', '-(3 + 1 % $)'); 611 612SELECT 613 jsonb_path_query('1', '$ + "2"'); 614 615SELECT 616 jsonb_path_query('[1, 2]', '3 * $'); 617 618SELECT 619 jsonb_path_query('"a"', '-$'); 620 621SELECT 622 jsonb_path_query('[1,"2",3]', '+$'); 623 624SELECT 625 jsonb_path_query('1', '$ + "2"', silent => TRUE); 626 627SELECT 628 jsonb_path_query('[1, 2]', '3 * $', silent => TRUE); 629 630SELECT 631 jsonb_path_query('"a"', '-$', silent => TRUE); 632 633SELECT 634 jsonb_path_query('[1,"2",3]', '+$', silent => TRUE); 635 636SELECT 637 jsonb '["1",2,0,3]' @ ? '-$[*]'; 638 639SELECT 640 jsonb '[1,"2",0,3]' @ ? '-$[*]'; 641 642SELECT 643 jsonb '["1",2,0,3]' @ ? 'strict -$[*]'; 644 645SELECT 646 jsonb '[1,"2",0,3]' @ ? 'strict -$[*]'; 647 648-- unwrapping of operator arguments in lax mode 649SELECT 650 jsonb_path_query('{"a": [2]}', 'lax $.a * 3'); 651 652SELECT 653 jsonb_path_query('{"a": [2]}', 'lax $.a + 3'); 654 655SELECT 656 jsonb_path_query('{"a": [2, 3, 4]}', 'lax -$.a'); 657 658-- should fail 659SELECT 660 jsonb_path_query('{"a": [1, 2]}', 'lax $.a * 3'); 661 662SELECT 663 jsonb_path_query('{"a": [1, 2]}', 'lax $.a * 3', silent => TRUE); 664 665-- extension: boolean expressions 666SELECT 667 jsonb_path_query('2', '$ > 1'); 668 669SELECT 670 jsonb_path_query('2', '$ <= 1'); 671 672SELECT 673 jsonb_path_query('2', '$ == "2"'); 674 675SELECT 676 jsonb '2' @ ? '$ == "2"'; 677 678SELECT 679 jsonb '2' @@ '$ > 1'; 680 681SELECT 682 jsonb '2' @@ '$ <= 1'; 683 684SELECT 685 jsonb '2' @@ '$ == "2"'; 686 687SELECT 688 jsonb '2' @@ '1'; 689 690SELECT 691 jsonb '{}' @@ '$'; 692 693SELECT 694 jsonb '[]' @@ '$'; 695 696SELECT 697 jsonb '[1,2,3]' @@ '$[*]'; 698 699SELECT 700 jsonb '[]' @@ '$[*]'; 701 702SELECT 703 jsonb_path_match('[[1, true], [2, false]]', 'strict $[*] ? (@[0] > $x) [1]', '{"x": 1}'); 704 705SELECT 706 jsonb_path_match('[[1, true], [2, false]]', 'strict $[*] ? (@[0] < $x) [1]', '{"x": 2}'); 707 708SELECT 709 jsonb_path_match('[{"a": 1}, {"a": 2}, 3]', 'lax exists($[*].a)', silent => FALSE); 710 711SELECT 712 jsonb_path_match('[{"a": 1}, {"a": 2}, 3]', 'lax exists($[*].a)', silent => TRUE); 713 714SELECT 715 jsonb_path_match('[{"a": 1}, {"a": 2}, 3]', 'strict exists($[*].a)', silent => FALSE); 716 717SELECT 718 jsonb_path_match('[{"a": 1}, {"a": 2}, 3]', 'strict exists($[*].a)', silent => TRUE); 719 720SELECT 721 jsonb_path_query('[null,1,true,"a",[],{}]', '$.type()'); 722 723SELECT 724 jsonb_path_query('[null,1,true,"a",[],{}]', 'lax $.type()'); 725 726SELECT 727 jsonb_path_query('[null,1,true,"a",[],{}]', '$[*].type()'); 728 729SELECT 730 jsonb_path_query('null', 'null.type()'); 731 732SELECT 733 jsonb_path_query('null', 'true.type()'); 734 735SELECT 736 jsonb_path_query('null', '(123).type()'); 737 738SELECT 739 jsonb_path_query('null', '"123".type()'); 740 741SELECT 742 jsonb_path_query('{"a": 2}', '($.a - 5).abs() + 10'); 743 744SELECT 745 jsonb_path_query('{"a": 2.5}', '-($.a * $.a).floor() % 4.3'); 746 747SELECT 748 jsonb_path_query('[1, 2, 3]', '($[*] > 2) ? (@ == true)'); 749 750SELECT 751 jsonb_path_query('[1, 2, 3]', '($[*] > 3).type()'); 752 753SELECT 754 jsonb_path_query('[1, 2, 3]', '($[*].a > 3).type()'); 755 756SELECT 757 jsonb_path_query('[1, 2, 3]', 'strict ($[*].a > 3).type()'); 758 759SELECT 760 jsonb_path_query('[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]', 'strict $[*].size()'); 761 762SELECT 763 jsonb_path_query('[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]', 'strict $[*].size()', silent => TRUE); 764 765SELECT 766 jsonb_path_query('[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]', 'lax $[*].size()'); 767 768SELECT 769 jsonb_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].abs()'); 770 771SELECT 772 jsonb_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].floor()'); 773 774SELECT 775 jsonb_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].ceiling()'); 776 777SELECT 778 jsonb_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].ceiling().abs()'); 779 780SELECT 781 jsonb_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].ceiling().abs().type()'); 782 783SELECT 784 jsonb_path_query('[{},1]', '$[*].keyvalue()'); 785 786SELECT 787 jsonb_path_query('[{},1]', '$[*].keyvalue()', silent => TRUE); 788 789SELECT 790 jsonb_path_query('{}', '$.keyvalue()'); 791 792SELECT 793 jsonb_path_query('{"a": 1, "b": [1, 2], "c": {"a": "bbb"}}', '$.keyvalue()'); 794 795SELECT 796 jsonb_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', '$[*].keyvalue()'); 797 798SELECT 799 jsonb_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', 'strict $.keyvalue()'); 800 801SELECT 802 jsonb_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', 'lax $.keyvalue()'); 803 804SELECT 805 jsonb_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', 'strict $.keyvalue().a'); 806 807SELECT 808 jsonb '{"a": 1, "b": [1, 2]}' @ ? 'lax $.keyvalue()'; 809 810SELECT 811 jsonb '{"a": 1, "b": [1, 2]}' @ ? 'lax $.keyvalue().key'; 812 813SELECT 814 jsonb_path_query('null', '$.double()'); 815 816SELECT 817 jsonb_path_query('true', '$.double()'); 818 819SELECT 820 jsonb_path_query('null', '$.double()', silent => TRUE); 821 822SELECT 823 jsonb_path_query('true', '$.double()', silent => TRUE); 824 825SELECT 826 jsonb_path_query('[]', '$.double()'); 827 828SELECT 829 jsonb_path_query('[]', 'strict $.double()'); 830 831SELECT 832 jsonb_path_query('{}', '$.double()'); 833 834SELECT 835 jsonb_path_query('[]', 'strict $.double()', silent => TRUE); 836 837SELECT 838 jsonb_path_query('{}', '$.double()', silent => TRUE); 839 840SELECT 841 jsonb_path_query('1.23', '$.double()'); 842 843SELECT 844 jsonb_path_query('"1.23"', '$.double()'); 845 846SELECT 847 jsonb_path_query('"1.23aaa"', '$.double()'); 848 849SELECT 850 jsonb_path_query('"nan"', '$.double()'); 851 852SELECT 853 jsonb_path_query('"NaN"', '$.double()'); 854 855SELECT 856 jsonb_path_query('"inf"', '$.double()'); 857 858SELECT 859 jsonb_path_query('"-inf"', '$.double()'); 860 861SELECT 862 jsonb_path_query('"inf"', '$.double()', silent => TRUE); 863 864SELECT 865 jsonb_path_query('"-inf"', '$.double()', silent => TRUE); 866 867SELECT 868 jsonb_path_query('{}', '$.abs()'); 869 870SELECT 871 jsonb_path_query('true', '$.floor()'); 872 873SELECT 874 jsonb_path_query('"1.2"', '$.ceiling()'); 875 876SELECT 877 jsonb_path_query('{}', '$.abs()', silent => TRUE); 878 879SELECT 880 jsonb_path_query('true', '$.floor()', silent => TRUE); 881 882SELECT 883 jsonb_path_query('"1.2"', '$.ceiling()', silent => TRUE); 884 885SELECT 886 jsonb_path_query('["", "a", "abc", "abcabc"]', '$[*] ? (@ starts with "abc")'); 887 888SELECT 889 jsonb_path_query('["", "a", "abc", "abcabc"]', 'strict $ ? (@[*] starts with "abc")'); 890 891SELECT 892 jsonb_path_query('["", "a", "abd", "abdabc"]', 'strict $ ? (@[*] starts with "abc")'); 893 894SELECT 895 jsonb_path_query('["abc", "abcabc", null, 1]', 'strict $ ? (@[*] starts with "abc")'); 896 897SELECT 898 jsonb_path_query('["abc", "abcabc", null, 1]', 'strict $ ? ((@[*] starts with "abc") is unknown)'); 899 900SELECT 901 jsonb_path_query('[[null, 1, "abc", "abcabc"]]', 'lax $ ? (@[*] starts with "abc")'); 902 903SELECT 904 jsonb_path_query('[[null, 1, "abd", "abdabc"]]', 'lax $ ? ((@[*] starts with "abc") is unknown)'); 905 906SELECT 907 jsonb_path_query('[null, 1, "abd", "abdabc"]', 'lax $[*] ? ((@ starts with "abc") is unknown)'); 908 909SELECT 910 jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "babc"]', 'lax $[*] ? (@ like_regex "^ab.*c")'); 911 912SELECT 913 jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "babc"]', 'lax $[*] ? (@ like_regex "^a b.* c " flag "ix")'); 914 915SELECT 916 jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "babc"]', 'lax $[*] ? (@ like_regex "^ab.*c" flag "m")'); 917 918SELECT 919 jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "babc"]', 'lax $[*] ? (@ like_regex "^ab.*c" flag "s")'); 920 921-- jsonpath operators 922SELECT 923 jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*]'); 924 925SELECT 926 jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*] ? (@.a > 10)'); 927 928SELECT 929 jsonb_path_query_array('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a'); 930 931SELECT 932 jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a'); 933 934SELECT 935 jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ == 1)'); 936 937SELECT 938 jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 10)'); 939 940SELECT 941 jsonb_path_query_array('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 1, "max": 4}'); 942 943SELECT 944 jsonb_path_query_array('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 3, "max": 4}'); 945 946SELECT 947 jsonb_path_query_first('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a'); 948 949SELECT 950 jsonb_path_query_first('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a', silent => TRUE); 951 952SELECT 953 jsonb_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a'); 954 955SELECT 956 jsonb_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ == 1)'); 957 958SELECT 959 jsonb_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 10)'); 960 961SELECT 962 jsonb_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 1, "max": 4}'); 963 964SELECT 965 jsonb_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 3, "max": 4}'); 966 967SELECT 968 jsonb '[{"a": 1}, {"a": 2}]' @ ? '$[*].a ? (@ > 1)'; 969 970SELECT 971 jsonb '[{"a": 1}, {"a": 2}]' @ ? '$[*] ? (@.a > 2)'; 972 973SELECT 974 jsonb_path_exists('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 1)'); 975 976SELECT 977 jsonb_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.a > $min && @.a < $max)', vars => '{"min": 1, "max": 4}'); 978 979SELECT 980 jsonb_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.a > $min && @.a < $max)', vars => '{"min": 3, "max": 4}'); 981 982SELECT 983 jsonb_path_match('true', '$', silent => FALSE); 984 985SELECT 986 jsonb_path_match('false', '$', silent => FALSE); 987 988SELECT 989 jsonb_path_match('null', '$', silent => FALSE); 990 991SELECT 992 jsonb_path_match('1', '$', silent => TRUE); 993 994SELECT 995 jsonb_path_match('1', '$', silent => FALSE); 996 997SELECT 998 jsonb_path_match('"a"', '$', silent => FALSE); 999 1000SELECT 1001 jsonb_path_match('{}', '$', silent => FALSE); 1002 1003SELECT 1004 jsonb_path_match('[true]', '$', silent => FALSE); 1005 1006SELECT 1007 jsonb_path_match('{}', 'lax $.a', silent => FALSE); 1008 1009SELECT 1010 jsonb_path_match('{}', 'strict $.a', silent => FALSE); 1011 1012SELECT 1013 jsonb_path_match('{}', 'strict $.a', silent => TRUE); 1014 1015SELECT 1016 jsonb_path_match('[true, true]', '$[*]', silent => FALSE); 1017 1018SELECT 1019 jsonb '[{"a": 1}, {"a": 2}]' @@ '$[*].a > 1'; 1020 1021SELECT 1022 jsonb '[{"a": 1}, {"a": 2}]' @@ '$[*].a > 2'; 1023 1024SELECT 1025 jsonb_path_match('[{"a": 1}, {"a": 2}]', '$[*].a > 1'); 1026 1027