1select jsonb '{"a": 12}' @? '$'; 2select jsonb '{"a": 12}' @? '1'; 3select jsonb '{"a": 12}' @? '$.a.b'; 4select jsonb '{"a": 12}' @? '$.b'; 5select jsonb '{"a": 12}' @? '$.a + 2'; 6select jsonb '{"a": 12}' @? '$.b + 2'; 7select jsonb '{"a": {"a": 12}}' @? '$.a.a'; 8select jsonb '{"a": {"a": 12}}' @? '$.*.a'; 9select jsonb '{"b": {"a": 12}}' @? '$.*.a'; 10select jsonb '{"b": {"a": 12}}' @? '$.*.b'; 11select jsonb '{"b": {"a": 12}}' @? 'strict $.*.b'; 12select jsonb '{}' @? '$.*'; 13select jsonb '{"a": 1}' @? '$.*'; 14select jsonb '{"a": {"b": 1}}' @? 'lax $.**{1}'; 15select jsonb '{"a": {"b": 1}}' @? 'lax $.**{2}'; 16select jsonb '{"a": {"b": 1}}' @? 'lax $.**{3}'; 17select jsonb '[]' @? '$[*]'; 18select jsonb '[1]' @? '$[*]'; 19select jsonb '[1]' @? '$[1]'; 20select jsonb '[1]' @? 'strict $[1]'; 21select jsonb_path_query('[1]', 'strict $[1]'); 22select jsonb_path_query('[1]', 'strict $[1]', silent => true); 23select jsonb '[1]' @? 'lax $[10000000000000000]'; 24select jsonb '[1]' @? 'strict $[10000000000000000]'; 25select jsonb_path_query('[1]', 'lax $[10000000000000000]'); 26select jsonb_path_query('[1]', 'strict $[10000000000000000]'); 27select jsonb '[1]' @? '$[0]'; 28select jsonb '[1]' @? '$[0.3]'; 29select jsonb '[1]' @? '$[0.5]'; 30select jsonb '[1]' @? '$[0.9]'; 31select jsonb '[1]' @? '$[1.2]'; 32select jsonb '[1]' @? 'strict $[1.2]'; 33select jsonb '{"a": [1,2,3], "b": [3,4,5]}' @? '$ ? (@.a[*] > @.b[*])'; 34select jsonb '{"a": [1,2,3], "b": [3,4,5]}' @? '$ ? (@.a[*] >= @.b[*])'; 35select jsonb '{"a": [1,2,3], "b": [3,4,"5"]}' @? '$ ? (@.a[*] >= @.b[*])'; 36select jsonb '{"a": [1,2,3], "b": [3,4,"5"]}' @? 'strict $ ? (@.a[*] >= @.b[*])'; 37select jsonb '{"a": [1,2,3], "b": [3,4,null]}' @? '$ ? (@.a[*] >= @.b[*])'; 38select jsonb '1' @? '$ ? ((@ == "1") is unknown)'; 39select jsonb '1' @? '$ ? ((@ == 1) is unknown)'; 40select jsonb '[{"a": 1}, {"a": 2}]' @? '$[0 to 1] ? (@.a > 1)'; 41 42select jsonb_path_exists('[{"a": 1}, {"a": 2}, 3]', 'lax $[*].a', silent => false); 43select jsonb_path_exists('[{"a": 1}, {"a": 2}, 3]', 'lax $[*].a', silent => true); 44select jsonb_path_exists('[{"a": 1}, {"a": 2}, 3]', 'strict $[*].a', silent => false); 45select jsonb_path_exists('[{"a": 1}, {"a": 2}, 3]', 'strict $[*].a', silent => true); 46 47select jsonb_path_query('1', 'lax $.a'); 48select jsonb_path_query('1', 'strict $.a'); 49select jsonb_path_query('1', 'strict $.*'); 50select jsonb_path_query('1', 'strict $.a', silent => true); 51select jsonb_path_query('1', 'strict $.*', silent => true); 52select jsonb_path_query('[]', 'lax $.a'); 53select jsonb_path_query('[]', 'strict $.a'); 54select jsonb_path_query('[]', 'strict $.a', silent => true); 55select jsonb_path_query('{}', 'lax $.a'); 56select jsonb_path_query('{}', 'strict $.a'); 57select jsonb_path_query('{}', 'strict $.a', silent => true); 58 59select jsonb_path_query('1', 'strict $[1]'); 60select jsonb_path_query('1', 'strict $[*]'); 61select jsonb_path_query('[]', 'strict $[1]'); 62select jsonb_path_query('[]', 'strict $["a"]'); 63select jsonb_path_query('1', 'strict $[1]', silent => true); 64select jsonb_path_query('1', 'strict $[*]', silent => true); 65select jsonb_path_query('[]', 'strict $[1]', silent => true); 66select jsonb_path_query('[]', 'strict $["a"]', silent => true); 67 68select jsonb_path_query('{"a": 12, "b": {"a": 13}}', '$.a'); 69select jsonb_path_query('{"a": 12, "b": {"a": 13}}', '$.b'); 70select jsonb_path_query('{"a": 12, "b": {"a": 13}}', '$.*'); 71select jsonb_path_query('{"a": 12, "b": {"a": 13}}', 'lax $.*.a'); 72select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[*].a'); 73select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[*].*'); 74select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0].a'); 75select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[1].a'); 76select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[2].a'); 77select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0,1].a'); 78select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0 to 10].a'); 79select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0 to 10 / 0].a'); 80select jsonb_path_query('[12, {"a": 13}, {"b": 14}, "ccc", true]', '$[2.5 - 1 to $.size() - 2]'); 81select jsonb_path_query('1', 'lax $[0]'); 82select jsonb_path_query('1', 'lax $[*]'); 83select jsonb_path_query('[1]', 'lax $[0]'); 84select jsonb_path_query('[1]', 'lax $[*]'); 85select jsonb_path_query('[1,2,3]', 'lax $[*]'); 86select jsonb_path_query('[1,2,3]', 'strict $[*].a'); 87select jsonb_path_query('[1,2,3]', 'strict $[*].a', silent => true); 88select jsonb_path_query('[]', '$[last]'); 89select jsonb_path_query('[]', '$[last ? (exists(last))]'); 90select jsonb_path_query('[]', 'strict $[last]'); 91select jsonb_path_query('[]', 'strict $[last]', silent => true); 92select jsonb_path_query('[1]', '$[last]'); 93select jsonb_path_query('[1,2,3]', '$[last]'); 94select jsonb_path_query('[1,2,3]', '$[last - 1]'); 95select jsonb_path_query('[1,2,3]', '$[last ? (@.type() == "number")]'); 96select jsonb_path_query('[1,2,3]', '$[last ? (@.type() == "string")]'); 97select jsonb_path_query('[1,2,3]', '$[last ? (@.type() == "string")]', silent => true); 98 99select * from jsonb_path_query('{"a": 10}', '$'); 100select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)'); 101select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)', '1'); 102select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)', '[{"value" : 13}]'); 103select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)', '{"value" : 13}'); 104select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)', '{"value" : 8}'); 105select * from jsonb_path_query('{"a": 10}', '$.a ? (@ < $value)', '{"value" : 13}'); 106select * from jsonb_path_query('[10,11,12,13,14,15]', '$[*] ? (@ < $value)', '{"value" : 13}'); 107select * from jsonb_path_query('[10,11,12,13,14,15]', '$[0,1] ? (@ < $x.value)', '{"x": {"value" : 13}}'); 108select * from jsonb_path_query('[10,11,12,13,14,15]', '$[0 to 2] ? (@ < $value)', '{"value" : 15}'); 109select * from jsonb_path_query('[1,"1",2,"2",null]', '$[*] ? (@ == "1")'); 110select * from jsonb_path_query('[1,"1",2,"2",null]', '$[*] ? (@ == $value)', '{"value" : "1"}'); 111select * from jsonb_path_query('[1,"1",2,"2",null]', '$[*] ? (@ == $value)', '{"value" : null}'); 112select * from jsonb_path_query('[1, "2", null]', '$[*] ? (@ != null)'); 113select * from jsonb_path_query('[1, "2", null]', '$[*] ? (@ == null)'); 114select * from jsonb_path_query('{}', '$ ? (@ == @)'); 115select * from jsonb_path_query('[]', 'strict $ ? (@ == @)'); 116 117select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**'); 118select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{0}'); 119select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{0 to last}'); 120select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{1}'); 121select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{1 to last}'); 122select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{2}'); 123select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{2 to last}'); 124select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{3 to last}'); 125select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{last}'); 126select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**.b ? (@ > 0)'); 127select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{0}.b ? (@ > 0)'); 128select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{1}.b ? (@ > 0)'); 129select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{0 to last}.b ? (@ > 0)'); 130select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{1 to last}.b ? (@ > 0)'); 131select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{1 to 2}.b ? (@ > 0)'); 132select jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**.b ? (@ > 0)'); 133select jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{0}.b ? (@ > 0)'); 134select jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{1}.b ? (@ > 0)'); 135select jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{0 to last}.b ? (@ > 0)'); 136select jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{1 to last}.b ? (@ > 0)'); 137select jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{1 to 2}.b ? (@ > 0)'); 138select jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{2 to 3}.b ? (@ > 0)'); 139 140select jsonb '{"a": {"b": 1}}' @? '$.**.b ? ( @ > 0)'; 141select jsonb '{"a": {"b": 1}}' @? '$.**{0}.b ? ( @ > 0)'; 142select jsonb '{"a": {"b": 1}}' @? '$.**{1}.b ? ( @ > 0)'; 143select jsonb '{"a": {"b": 1}}' @? '$.**{0 to last}.b ? ( @ > 0)'; 144select jsonb '{"a": {"b": 1}}' @? '$.**{1 to last}.b ? ( @ > 0)'; 145select jsonb '{"a": {"b": 1}}' @? '$.**{1 to 2}.b ? ( @ > 0)'; 146select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**.b ? ( @ > 0)'; 147select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{0}.b ? ( @ > 0)'; 148select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{1}.b ? ( @ > 0)'; 149select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{0 to last}.b ? ( @ > 0)'; 150select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{1 to last}.b ? ( @ > 0)'; 151select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{1 to 2}.b ? ( @ > 0)'; 152select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{2 to 3}.b ? ( @ > 0)'; 153 154select jsonb_path_query('{"g": {"x": 2}}', '$.g ? (exists (@.x))'); 155select jsonb_path_query('{"g": {"x": 2}}', '$.g ? (exists (@.y))'); 156select jsonb_path_query('{"g": {"x": 2}}', '$.g ? (exists (@.x ? (@ >= 2) ))'); 157select jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'lax $.g ? (exists (@.x))'); 158select jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'lax $.g ? (exists (@.x + "3"))'); 159select jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'lax $.g ? ((exists (@.x + "3")) is unknown)'); 160select jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g[*] ? (exists (@.x))'); 161select jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g[*] ? ((exists (@.x)) is unknown)'); 162select jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g ? (exists (@[*].x))'); 163select jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g ? ((exists (@[*].x)) is unknown)'); 164 165--test ternary logic 166select 167 x, y, 168 jsonb_path_query( 169 '[true, false, null]', 170 '$[*] ? (@ == true && ($x == true && $y == true) || 171 @ == false && !($x == true && $y == true) || 172 @ == null && ($x == true && $y == true) is unknown)', 173 jsonb_build_object('x', x, 'y', y) 174 ) as "x && y" 175from 176 (values (jsonb 'true'), ('false'), ('"null"')) x(x), 177 (values (jsonb 'true'), ('false'), ('"null"')) y(y); 178 179select 180 x, y, 181 jsonb_path_query( 182 '[true, false, null]', 183 '$[*] ? (@ == true && ($x == true || $y == true) || 184 @ == false && !($x == true || $y == true) || 185 @ == null && ($x == true || $y == true) is unknown)', 186 jsonb_build_object('x', x, 'y', y) 187 ) as "x || y" 188from 189 (values (jsonb 'true'), ('false'), ('"null"')) x(x), 190 (values (jsonb 'true'), ('false'), ('"null"')) y(y); 191 192select jsonb '{"a": 1, "b":1}' @? '$ ? (@.a == @.b)'; 193select jsonb '{"c": {"a": 1, "b":1}}' @? '$ ? (@.a == @.b)'; 194select jsonb '{"c": {"a": 1, "b":1}}' @? '$.c ? (@.a == @.b)'; 195select jsonb '{"c": {"a": 1, "b":1}}' @? '$.c ? ($.c.a == @.b)'; 196select jsonb '{"c": {"a": 1, "b":1}}' @? '$.* ? (@.a == @.b)'; 197select jsonb '{"a": 1, "b":1}' @? '$.** ? (@.a == @.b)'; 198select jsonb '{"c": {"a": 1, "b":1}}' @? '$.** ? (@.a == @.b)'; 199 200select jsonb_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == 1 + 1)'); 201select jsonb_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == (1 + 1))'); 202select jsonb_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == @.b + 1)'); 203select jsonb_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == (@.b + 1))'); 204select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == - 1)'; 205select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == -1)'; 206select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == -@.b)'; 207select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == - @.b)'; 208select jsonb '{"c": {"a": 0, "b":1}}' @? '$.** ? (@.a == 1 - @.b)'; 209select jsonb '{"c": {"a": 2, "b":1}}' @? '$.** ? (@.a == 1 - - @.b)'; 210select jsonb '{"c": {"a": 0, "b":1}}' @? '$.** ? (@.a == 1 - +@.b)'; 211select jsonb '[1,2,3]' @? '$ ? (+@[*] > +2)'; 212select jsonb '[1,2,3]' @? '$ ? (+@[*] > +3)'; 213select jsonb '[1,2,3]' @? '$ ? (-@[*] < -2)'; 214select jsonb '[1,2,3]' @? '$ ? (-@[*] < -3)'; 215select jsonb '1' @? '$ ? ($ > 0)'; 216 217-- arithmetic errors 218select jsonb_path_query('[1,2,0,3]', '$[*] ? (2 / @ > 0)'); 219select jsonb_path_query('[1,2,0,3]', '$[*] ? ((2 / @ > 0) is unknown)'); 220select jsonb_path_query('0', '1 / $'); 221select jsonb_path_query('0', '1 / $ + 2'); 222select jsonb_path_query('0', '-(3 + 1 % $)'); 223select jsonb_path_query('1', '$ + "2"'); 224select jsonb_path_query('[1, 2]', '3 * $'); 225select jsonb_path_query('"a"', '-$'); 226select jsonb_path_query('[1,"2",3]', '+$'); 227select jsonb_path_query('1', '$ + "2"', silent => true); 228select jsonb_path_query('[1, 2]', '3 * $', silent => true); 229select jsonb_path_query('"a"', '-$', silent => true); 230select jsonb_path_query('[1,"2",3]', '+$', silent => true); 231select jsonb '["1",2,0,3]' @? '-$[*]'; 232select jsonb '[1,"2",0,3]' @? '-$[*]'; 233select jsonb '["1",2,0,3]' @? 'strict -$[*]'; 234select jsonb '[1,"2",0,3]' @? 'strict -$[*]'; 235 236-- unwrapping of operator arguments in lax mode 237select jsonb_path_query('{"a": [2]}', 'lax $.a * 3'); 238select jsonb_path_query('{"a": [2]}', 'lax $.a + 3'); 239select jsonb_path_query('{"a": [2, 3, 4]}', 'lax -$.a'); 240-- should fail 241select jsonb_path_query('{"a": [1, 2]}', 'lax $.a * 3'); 242select jsonb_path_query('{"a": [1, 2]}', 'lax $.a * 3', silent => true); 243 244-- extension: boolean expressions 245select jsonb_path_query('2', '$ > 1'); 246select jsonb_path_query('2', '$ <= 1'); 247select jsonb_path_query('2', '$ == "2"'); 248select jsonb '2' @? '$ == "2"'; 249 250select jsonb '2' @@ '$ > 1'; 251select jsonb '2' @@ '$ <= 1'; 252select jsonb '2' @@ '$ == "2"'; 253select jsonb '2' @@ '1'; 254select jsonb '{}' @@ '$'; 255select jsonb '[]' @@ '$'; 256select jsonb '[1,2,3]' @@ '$[*]'; 257select jsonb '[]' @@ '$[*]'; 258select jsonb_path_match('[[1, true], [2, false]]', 'strict $[*] ? (@[0] > $x) [1]', '{"x": 1}'); 259select jsonb_path_match('[[1, true], [2, false]]', 'strict $[*] ? (@[0] < $x) [1]', '{"x": 2}'); 260 261select jsonb_path_match('[{"a": 1}, {"a": 2}, 3]', 'lax exists($[*].a)', silent => false); 262select jsonb_path_match('[{"a": 1}, {"a": 2}, 3]', 'lax exists($[*].a)', silent => true); 263select jsonb_path_match('[{"a": 1}, {"a": 2}, 3]', 'strict exists($[*].a)', silent => false); 264select jsonb_path_match('[{"a": 1}, {"a": 2}, 3]', 'strict exists($[*].a)', silent => true); 265 266 267select jsonb_path_query('[null,1,true,"a",[],{}]', '$.type()'); 268select jsonb_path_query('[null,1,true,"a",[],{}]', 'lax $.type()'); 269select jsonb_path_query('[null,1,true,"a",[],{}]', '$[*].type()'); 270select jsonb_path_query('null', 'null.type()'); 271select jsonb_path_query('null', 'true.type()'); 272select jsonb_path_query('null', '(123).type()'); 273select jsonb_path_query('null', '"123".type()'); 274 275select jsonb_path_query('{"a": 2}', '($.a - 5).abs() + 10'); 276select jsonb_path_query('{"a": 2.5}', '-($.a * $.a).floor() % 4.3'); 277select jsonb_path_query('[1, 2, 3]', '($[*] > 2) ? (@ == true)'); 278select jsonb_path_query('[1, 2, 3]', '($[*] > 3).type()'); 279select jsonb_path_query('[1, 2, 3]', '($[*].a > 3).type()'); 280select jsonb_path_query('[1, 2, 3]', 'strict ($[*].a > 3).type()'); 281 282select jsonb_path_query('[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]', 'strict $[*].size()'); 283select jsonb_path_query('[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]', 'strict $[*].size()', silent => true); 284select jsonb_path_query('[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]', 'lax $[*].size()'); 285 286select jsonb_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].abs()'); 287select jsonb_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].floor()'); 288select jsonb_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].ceiling()'); 289select jsonb_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].ceiling().abs()'); 290select jsonb_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].ceiling().abs().type()'); 291 292select jsonb_path_query('[{},1]', '$[*].keyvalue()'); 293select jsonb_path_query('[{},1]', '$[*].keyvalue()', silent => true); 294select jsonb_path_query('{}', '$.keyvalue()'); 295select jsonb_path_query('{"a": 1, "b": [1, 2], "c": {"a": "bbb"}}', '$.keyvalue()'); 296select jsonb_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', '$[*].keyvalue()'); 297select jsonb_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', 'strict $.keyvalue()'); 298select jsonb_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', 'lax $.keyvalue()'); 299select jsonb_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', 'strict $.keyvalue().a'); 300select jsonb '{"a": 1, "b": [1, 2]}' @? 'lax $.keyvalue()'; 301select jsonb '{"a": 1, "b": [1, 2]}' @? 'lax $.keyvalue().key'; 302 303select jsonb_path_query('null', '$.double()'); 304select jsonb_path_query('true', '$.double()'); 305select jsonb_path_query('null', '$.double()', silent => true); 306select jsonb_path_query('true', '$.double()', silent => true); 307select jsonb_path_query('[]', '$.double()'); 308select jsonb_path_query('[]', 'strict $.double()'); 309select jsonb_path_query('{}', '$.double()'); 310select jsonb_path_query('[]', 'strict $.double()', silent => true); 311select jsonb_path_query('{}', '$.double()', silent => true); 312select jsonb_path_query('1.23', '$.double()'); 313select jsonb_path_query('"1.23"', '$.double()'); 314select jsonb_path_query('"1.23aaa"', '$.double()'); 315select jsonb_path_query('1e1000', '$.double()'); 316select jsonb_path_query('"nan"', '$.double()'); 317select jsonb_path_query('"NaN"', '$.double()'); 318select jsonb_path_query('"inf"', '$.double()'); 319select jsonb_path_query('"-inf"', '$.double()'); 320select jsonb_path_query('"inf"', '$.double()', silent => true); 321select jsonb_path_query('"-inf"', '$.double()', silent => true); 322 323select jsonb_path_query('{}', '$.abs()'); 324select jsonb_path_query('true', '$.floor()'); 325select jsonb_path_query('"1.2"', '$.ceiling()'); 326select jsonb_path_query('{}', '$.abs()', silent => true); 327select jsonb_path_query('true', '$.floor()', silent => true); 328select jsonb_path_query('"1.2"', '$.ceiling()', silent => true); 329 330select jsonb_path_query('["", "a", "abc", "abcabc"]', '$[*] ? (@ starts with "abc")'); 331select jsonb_path_query('["", "a", "abc", "abcabc"]', 'strict $ ? (@[*] starts with "abc")'); 332select jsonb_path_query('["", "a", "abd", "abdabc"]', 'strict $ ? (@[*] starts with "abc")'); 333select jsonb_path_query('["abc", "abcabc", null, 1]', 'strict $ ? (@[*] starts with "abc")'); 334select jsonb_path_query('["abc", "abcabc", null, 1]', 'strict $ ? ((@[*] starts with "abc") is unknown)'); 335select jsonb_path_query('[[null, 1, "abc", "abcabc"]]', 'lax $ ? (@[*] starts with "abc")'); 336select jsonb_path_query('[[null, 1, "abd", "abdabc"]]', 'lax $ ? ((@[*] starts with "abc") is unknown)'); 337select jsonb_path_query('[null, 1, "abd", "abdabc"]', 'lax $[*] ? ((@ starts with "abc") is unknown)'); 338 339select jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "babc", "adc\nabc", "ab\nadc"]', 'lax $[*] ? (@ like_regex "^ab.*c")'); 340select jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "babc", "adc\nabc", "ab\nadc"]', 'lax $[*] ? (@ like_regex "^ab.*c" flag "i")'); 341select jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "babc", "adc\nabc", "ab\nadc"]', 'lax $[*] ? (@ like_regex "^ab.*c" flag "m")'); 342select jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "babc", "adc\nabc", "ab\nadc"]', 'lax $[*] ? (@ like_regex "^ab.*c" flag "s")'); 343select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "a\\b" flag "q")'); 344select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "a\\b" flag "")'); 345select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\b$" flag "q")'); 346select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\B$" flag "q")'); 347select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\B$" flag "iq")'); 348select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\b$" flag "")'); 349 350select jsonb_path_query('null', '$.datetime()'); 351select jsonb_path_query('true', '$.datetime()'); 352select jsonb_path_query('1', '$.datetime()'); 353select jsonb_path_query('[]', '$.datetime()'); 354select jsonb_path_query('[]', 'strict $.datetime()'); 355select jsonb_path_query('{}', '$.datetime()'); 356select jsonb_path_query('"bogus"', '$.datetime()'); 357select jsonb_path_query('"12:34"', '$.datetime("aaa")'); 358select jsonb_path_query('"aaaa"', '$.datetime("HH24")'); 359 360select jsonb '"10-03-2017"' @? '$.datetime("dd-mm-yyyy")'; 361select jsonb_path_query('"10-03-2017"', '$.datetime("dd-mm-yyyy")'); 362select jsonb_path_query('"10-03-2017"', '$.datetime("dd-mm-yyyy").type()'); 363select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy")'); 364select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy").type()'); 365 366select jsonb_path_query('"10-03-2017 12:34"', ' $.datetime("dd-mm-yyyy HH24:MI").type()'); 367select jsonb_path_query('"10-03-2017 12:34 +05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM").type()'); 368select jsonb_path_query('"12:34:56"', '$.datetime("HH24:MI:SS").type()'); 369select jsonb_path_query('"12:34:56 +05:20"', '$.datetime("HH24:MI:SS TZH:TZM").type()'); 370 371select jsonb_path_query('"10-03-2017T12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")'); 372select jsonb_path_query('"10-03-2017t12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")'); 373select jsonb_path_query('"10-03-2017 12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")'); 374 375set time zone '+00'; 376 377select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")'); 378select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); 379select jsonb_path_query('"10-03-2017 12:34 +05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); 380select jsonb_path_query('"10-03-2017 12:34 -05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); 381select jsonb_path_query('"10-03-2017 12:34 +05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'); 382select jsonb_path_query('"10-03-2017 12:34 -05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'); 383select jsonb_path_query('"12:34"', '$.datetime("HH24:MI")'); 384select jsonb_path_query('"12:34"', '$.datetime("HH24:MI TZH")'); 385select jsonb_path_query('"12:34 +05"', '$.datetime("HH24:MI TZH")'); 386select jsonb_path_query('"12:34 -05"', '$.datetime("HH24:MI TZH")'); 387select jsonb_path_query('"12:34 +05:20"', '$.datetime("HH24:MI TZH:TZM")'); 388select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")'); 389 390set time zone '+10'; 391 392select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")'); 393select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); 394select jsonb_path_query('"10-03-2017 12:34 +05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); 395select jsonb_path_query('"10-03-2017 12:34 -05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); 396select jsonb_path_query('"10-03-2017 12:34 +05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'); 397select jsonb_path_query('"10-03-2017 12:34 -05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'); 398select jsonb_path_query('"12:34"', '$.datetime("HH24:MI")'); 399select jsonb_path_query('"12:34"', '$.datetime("HH24:MI TZH")'); 400select jsonb_path_query('"12:34 +05"', '$.datetime("HH24:MI TZH")'); 401select jsonb_path_query('"12:34 -05"', '$.datetime("HH24:MI TZH")'); 402select jsonb_path_query('"12:34 +05:20"', '$.datetime("HH24:MI TZH:TZM")'); 403select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")'); 404 405set time zone default; 406 407select jsonb_path_query('"2017-03-10"', '$.datetime().type()'); 408select jsonb_path_query('"2017-03-10"', '$.datetime()'); 409select jsonb_path_query('"2017-03-10 12:34:56"', '$.datetime().type()'); 410select jsonb_path_query('"2017-03-10 12:34:56"', '$.datetime()'); 411select jsonb_path_query('"2017-03-10 12:34:56+3"', '$.datetime().type()'); 412select jsonb_path_query('"2017-03-10 12:34:56+3"', '$.datetime()'); 413select jsonb_path_query('"2017-03-10 12:34:56+3:10"', '$.datetime().type()'); 414select jsonb_path_query('"2017-03-10 12:34:56+3:10"', '$.datetime()'); 415select jsonb_path_query('"2017-03-10T12:34:56+3:10"', '$.datetime()'); 416select jsonb_path_query('"2017-03-10t12:34:56+3:10"', '$.datetime()'); 417select jsonb_path_query('"12:34:56"', '$.datetime().type()'); 418select jsonb_path_query('"12:34:56"', '$.datetime()'); 419select jsonb_path_query('"12:34:56+3"', '$.datetime().type()'); 420select jsonb_path_query('"12:34:56+3"', '$.datetime()'); 421select jsonb_path_query('"12:34:56+3:10"', '$.datetime().type()'); 422select jsonb_path_query('"12:34:56+3:10"', '$.datetime()'); 423 424set time zone '+00'; 425 426-- date comparison 427select jsonb_path_query( 428 '["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', 429 '$[*].datetime() ? (@ == "10.03.2017".datetime("dd.mm.yyyy"))'); 430select jsonb_path_query( 431 '["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', 432 '$[*].datetime() ? (@ >= "10.03.2017".datetime("dd.mm.yyyy"))'); 433select jsonb_path_query( 434 '["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', 435 '$[*].datetime() ? (@ < "10.03.2017".datetime("dd.mm.yyyy"))'); 436select jsonb_path_query_tz( 437 '["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', 438 '$[*].datetime() ? (@ == "10.03.2017".datetime("dd.mm.yyyy"))'); 439select jsonb_path_query_tz( 440 '["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', 441 '$[*].datetime() ? (@ >= "10.03.2017".datetime("dd.mm.yyyy"))'); 442select jsonb_path_query_tz( 443 '["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', 444 '$[*].datetime() ? (@ < "10.03.2017".datetime("dd.mm.yyyy"))'); 445 446-- time comparison 447select jsonb_path_query( 448 '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', 449 '$[*].datetime() ? (@ == "12:35".datetime("HH24:MI"))'); 450select jsonb_path_query( 451 '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', 452 '$[*].datetime() ? (@ >= "12:35".datetime("HH24:MI"))'); 453select jsonb_path_query( 454 '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', 455 '$[*].datetime() ? (@ < "12:35".datetime("HH24:MI"))'); 456select jsonb_path_query_tz( 457 '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', 458 '$[*].datetime() ? (@ == "12:35".datetime("HH24:MI"))'); 459select jsonb_path_query_tz( 460 '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', 461 '$[*].datetime() ? (@ >= "12:35".datetime("HH24:MI"))'); 462select jsonb_path_query_tz( 463 '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', 464 '$[*].datetime() ? (@ < "12:35".datetime("HH24:MI"))'); 465 466-- timetz comparison 467select jsonb_path_query( 468 '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]', 469 '$[*].datetime() ? (@ == "12:35 +1".datetime("HH24:MI TZH"))'); 470select jsonb_path_query( 471 '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]', 472 '$[*].datetime() ? (@ >= "12:35 +1".datetime("HH24:MI TZH"))'); 473select jsonb_path_query( 474 '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]', 475 '$[*].datetime() ? (@ < "12:35 +1".datetime("HH24:MI TZH"))'); 476select jsonb_path_query_tz( 477 '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]', 478 '$[*].datetime() ? (@ == "12:35 +1".datetime("HH24:MI TZH"))'); 479select jsonb_path_query_tz( 480 '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]', 481 '$[*].datetime() ? (@ >= "12:35 +1".datetime("HH24:MI TZH"))'); 482select jsonb_path_query_tz( 483 '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]', 484 '$[*].datetime() ? (@ < "12:35 +1".datetime("HH24:MI TZH"))'); 485 486-- timestamp comparison 487select jsonb_path_query( 488 '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', 489 '$[*].datetime() ? (@ == "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))'); 490select jsonb_path_query( 491 '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', 492 '$[*].datetime() ? (@ >= "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))'); 493select jsonb_path_query( 494 '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', 495 '$[*].datetime() ? (@ < "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))'); 496select jsonb_path_query_tz( 497 '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', 498 '$[*].datetime() ? (@ == "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))'); 499select jsonb_path_query_tz( 500 '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', 501 '$[*].datetime() ? (@ >= "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))'); 502select jsonb_path_query_tz( 503 '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', 504 '$[*].datetime() ? (@ < "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))'); 505 506-- timestamptz comparison 507select jsonb_path_query( 508 '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', 509 '$[*].datetime() ? (@ == "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))'); 510select jsonb_path_query( 511 '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', 512 '$[*].datetime() ? (@ >= "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))'); 513select jsonb_path_query( 514 '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', 515 '$[*].datetime() ? (@ < "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))'); 516select jsonb_path_query_tz( 517 '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', 518 '$[*].datetime() ? (@ == "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))'); 519select jsonb_path_query_tz( 520 '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', 521 '$[*].datetime() ? (@ >= "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))'); 522select jsonb_path_query_tz( 523 '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', 524 '$[*].datetime() ? (@ < "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))'); 525 526-- overflow during comparison 527select jsonb_path_query('"1000000-01-01"', '$.datetime() > "2020-01-01 12:00:00".datetime()'::jsonpath); 528 529set time zone default; 530 531-- jsonpath operators 532 533SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*]'); 534SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*] ? (@.a > 10)'); 535 536SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a'); 537SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a'); 538SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ == 1)'); 539SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 10)'); 540SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 1, "max": 4}'); 541SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 3, "max": 4}'); 542 543SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a'); 544SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a', silent => true); 545SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a'); 546SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ == 1)'); 547SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 10)'); 548SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 1, "max": 4}'); 549SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 3, "max": 4}'); 550 551SELECT jsonb '[{"a": 1}, {"a": 2}]' @? '$[*].a ? (@ > 1)'; 552SELECT jsonb '[{"a": 1}, {"a": 2}]' @? '$[*] ? (@.a > 2)'; 553SELECT jsonb_path_exists('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 1)'); 554SELECT jsonb_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.a > $min && @.a < $max)', vars => '{"min": 1, "max": 4}'); 555SELECT jsonb_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.a > $min && @.a < $max)', vars => '{"min": 3, "max": 4}'); 556 557SELECT jsonb_path_match('true', '$', silent => false); 558SELECT jsonb_path_match('false', '$', silent => false); 559SELECT jsonb_path_match('null', '$', silent => false); 560SELECT jsonb_path_match('1', '$', silent => true); 561SELECT jsonb_path_match('1', '$', silent => false); 562SELECT jsonb_path_match('"a"', '$', silent => false); 563SELECT jsonb_path_match('{}', '$', silent => false); 564SELECT jsonb_path_match('[true]', '$', silent => false); 565SELECT jsonb_path_match('{}', 'lax $.a', silent => false); 566SELECT jsonb_path_match('{}', 'strict $.a', silent => false); 567SELECT jsonb_path_match('{}', 'strict $.a', silent => true); 568SELECT jsonb_path_match('[true, true]', '$[*]', silent => false); 569SELECT jsonb '[{"a": 1}, {"a": 2}]' @@ '$[*].a > 1'; 570SELECT jsonb '[{"a": 1}, {"a": 2}]' @@ '$[*].a > 2'; 571SELECT jsonb_path_match('[{"a": 1}, {"a": 2}]', '$[*].a > 1'); 572 573-- test string comparison (Unicode codepoint collation) 574WITH str(j, num) AS 575( 576 SELECT jsonb_build_object('s', s), num 577 FROM unnest('{"", "a", "ab", "abc", "abcd", "b", "A", "AB", "ABC", "ABc", "ABcD", "B"}'::text[]) WITH ORDINALITY AS a(s, num) 578) 579SELECT 580 s1.j, s2.j, 581 jsonb_path_query_first(s1.j, '$.s < $s', vars => s2.j) lt, 582 jsonb_path_query_first(s1.j, '$.s <= $s', vars => s2.j) le, 583 jsonb_path_query_first(s1.j, '$.s == $s', vars => s2.j) eq, 584 jsonb_path_query_first(s1.j, '$.s >= $s', vars => s2.j) ge, 585 jsonb_path_query_first(s1.j, '$.s > $s', vars => s2.j) gt 586FROM str s1, str s2 587ORDER BY s1.num, s2.num; 588