src/test/regress/expected/jsonb_jsonpath.out - master - githubmirror/postgres

src/test/regress/expected/jsonb_jsonpath.out
4 938 строк159 KB
Michael Paquier
Fix jsonpath .decimal() to honor silent mode
02 июл 2026, 06:44
02 июл 2026, 06:447b12ae7
select jsonb '{"a": 12}' @? '$';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": 12}' @? '1';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": 12}' @? '$.a.b';
 ?column? 
----------
 f
(1 row)

select jsonb '{"a": 12}' @? '$.b';
 ?column? 
----------
 f
(1 row)

select jsonb '{"a": 12}' @? '$.a + 2';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": 12}' @? '$.b + 2';
 ?column? 
----------
 
(1 row)

select jsonb '{"a": {"a": 12}}' @? '$.a.a';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": {"a": 12}}' @? '$.*.a';
 ?column? 
----------
 t
(1 row)

select jsonb '{"b": {"a": 12}}' @? '$.*.a';
 ?column? 
----------
 t
(1 row)

select jsonb '{"b": {"a": 12}}' @? '$.*.b';
 ?column? 
----------
 f
(1 row)

select jsonb '{"b": {"a": 12}}' @? 'strict $.*.b';
 ?column? 
----------
 
(1 row)

select jsonb '{}' @? '$.*';
 ?column? 
----------
 f
(1 row)

select jsonb '{"a": 1}' @? '$.*';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": {"b": 1}}' @? 'lax $.**{1}';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": {"b": 1}}' @? 'lax $.**{2}';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": {"b": 1}}' @? 'lax $.**{3}';
 ?column? 
----------
 f
(1 row)

select jsonb '[]' @? '$[*]';
 ?column? 
----------
 f
(1 row)

select jsonb '[1]' @? '$[*]';
 ?column? 
----------
 t
(1 row)

select jsonb '[1]' @? '$[1]';
 ?column? 
----------
 f
(1 row)

select jsonb '[1]' @? 'strict $[1]';
 ?column? 
----------
 
(1 row)

select jsonb_path_query('[1]', 'strict $[1]');
ERROR:  jsonpath array subscript is out of bounds
select jsonb_path_query('[1]', 'strict $[1]', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb '[1]' @? 'lax $[10000000000000000]';
 ?column? 
----------
 
(1 row)

select jsonb '[1]' @? 'strict $[10000000000000000]';
 ?column? 
----------
 
(1 row)

select jsonb_path_query('[1]', 'lax $[10000000000000000]');
ERROR:  jsonpath array subscript is out of integer range
select jsonb_path_query('[1]', 'strict $[10000000000000000]');
ERROR:  jsonpath array subscript is out of integer range
select jsonb '[1]' @? '$[0]';
 ?column? 
----------
 t
(1 row)

select jsonb '[1]' @? '$[0.3]';
 ?column? 
----------
 t
(1 row)

select jsonb '[1]' @? '$[0.5]';
 ?column? 
----------
 t
(1 row)

select jsonb '[1]' @? '$[0.9]';
 ?column? 
----------
 t
(1 row)

select jsonb '[1]' @? '$[1.2]';
 ?column? 
----------
 f
(1 row)

select jsonb '[1]' @? 'strict $[1.2]';
 ?column? 
----------
 
(1 row)

select jsonb '{"a": [1,2,3], "b": [3,4,5]}' @? '$ ? (@.a[*] >  @.b[*])';
 ?column? 
----------
 f
(1 row)

select jsonb '{"a": [1,2,3], "b": [3,4,5]}' @? '$ ? (@.a[*] >= @.b[*])';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": [1,2,3], "b": [3,4,"5"]}' @? '$ ? (@.a[*] >= @.b[*])';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": [1,2,3], "b": [3,4,"5"]}' @? 'strict $ ? (@.a[*] >= @.b[*])';
 ?column? 
----------
 f
(1 row)

select jsonb '{"a": [1,2,3], "b": [3,4,null]}' @? '$ ? (@.a[*] >= @.b[*])';
 ?column? 
----------
 t
(1 row)

select jsonb '1' @? '$ ? ((@ == "1") is unknown)';
 ?column? 
----------
 t
(1 row)

select jsonb '1' @? '$ ? ((@ == 1) is unknown)';
 ?column? 
----------
 f
(1 row)

select jsonb '[{"a": 1}, {"a": 2}]' @? '$[0 to 1] ? (@.a > 1)';
 ?column? 
----------
 t
(1 row)

select jsonb_path_exists('[{"a": 1}, {"a": 2}, 3]', 'lax $[*].a', silent => false);
 jsonb_path_exists 
-------------------
 t
(1 row)

select jsonb_path_exists('[{"a": 1}, {"a": 2}, 3]', 'lax $[*].a', silent => true);
 jsonb_path_exists 
-------------------
 t
(1 row)

select jsonb_path_exists('[{"a": 1}, {"a": 2}, 3]', 'strict $[*].a', silent => false);
ERROR:  jsonpath member accessor can only be applied to an object
select jsonb_path_exists('[{"a": 1}, {"a": 2}, 3]', 'strict $[*].a', silent => true);
 jsonb_path_exists 
-------------------
 
(1 row)

select jsonb_path_query('1', 'lax $.a');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1', 'strict $.a');
ERROR:  jsonpath member accessor can only be applied to an object
select jsonb_path_query('1', 'strict $.*');
ERROR:  jsonpath wildcard member accessor can only be applied to an object
select jsonb_path_query('1', 'strict $.a', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1', 'strict $.*', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'lax $.a');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.a');
ERROR:  jsonpath member accessor can only be applied to an object
select jsonb_path_query('[]', 'strict $.a', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', 'lax $.a');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', 'strict $.a');
ERROR:  JSON object does not contain key "a"
select jsonb_path_query('{}', 'strict $.a', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1', 'strict $[1]');
ERROR:  jsonpath array accessor can only be applied to an array
select jsonb_path_query('1', 'strict $[*]');
ERROR:  jsonpath wildcard array accessor can only be applied to an array
select jsonb_path_query('[]', 'strict $[1]');
ERROR:  jsonpath array subscript is out of bounds
select jsonb_path_query('[]', 'strict $["a"]');
ERROR:  jsonpath array subscript is not a single numeric value
select jsonb_path_query('1', 'strict $[1]', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1', 'strict $[*]', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $[1]', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $["a"]', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{"a": 12, "b": {"a": 13}}', '$.a');
 jsonb_path_query 
------------------
 12
(1 row)

select jsonb_path_query('{"a": 12, "b": {"a": 13}}', '$.b');
 jsonb_path_query 
------------------
 {"a": 13}
(1 row)

select jsonb_path_query('{"a": 12, "b": {"a": 13}}', '$.*');
 jsonb_path_query 
------------------
 12
 {"a": 13}
(2 rows)

select jsonb_path_query('{"a": 12, "b": {"a": 13}}', 'lax $.*.a');
 jsonb_path_query 
------------------
 13
(1 row)

select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[*].a');
 jsonb_path_query 
------------------
 13
(1 row)

select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[*].*');
 jsonb_path_query 
------------------
 13
 14
(2 rows)

select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0].a');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[1].a');
 jsonb_path_query 
------------------
 13
(1 row)

select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[2].a');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0,1].a');
 jsonb_path_query 
------------------
 13
(1 row)

select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0 to 10].a');
 jsonb_path_query 
------------------
 13
(1 row)

select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0 to 10 / 0].a');
ERROR:  division by zero
select jsonb_path_query('[12, {"a": 13}, {"b": 14}, "ccc", true]', '$[2.5 - 1 to $.size() - 2]');
 jsonb_path_query 
------------------
 {"a": 13}
 {"b": 14}
 "ccc"
(3 rows)

select jsonb_path_query('1', 'lax $[0]');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('1', 'lax $[*]');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('[1]', 'lax $[0]');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('[1]', 'lax $[*]');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('[1,2,3]', 'lax $[*]');
 jsonb_path_query 
------------------
 1
 2
 3
(3 rows)

select jsonb_path_query('[1,2,3]', 'strict $[*].a');
ERROR:  jsonpath member accessor can only be applied to an object
select jsonb_path_query('[1,2,3]', 'strict $[*].a', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', '$[last]');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', '$[last ? (exists(last))]');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $[last]');
ERROR:  jsonpath array subscript is out of bounds
select jsonb_path_query('[]', 'strict $[last]', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[1]', '$[last]');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('[1,2,3]', '$[last]');
 jsonb_path_query 
------------------
 3
(1 row)

select jsonb_path_query('[1,2,3]', '$[last - 1]');
 jsonb_path_query 
------------------
 2
(1 row)

select jsonb_path_query('[1,2,3]', '$[last ? (@.type() == "number")]');
 jsonb_path_query 
------------------
 3
(1 row)

select jsonb_path_query('[1,2,3]', '$[last ? (@.type() == "string")]');
ERROR:  jsonpath array subscript is not a single numeric value
select jsonb_path_query('[1,2,3]', '$[last ? (@.type() == "string")]', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select * from jsonb_path_query('{"a": 10}', '$');
 jsonb_path_query 
------------------
 {"a": 10}
(1 row)

select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)');
ERROR:  could not find jsonpath variable "value"
select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)', '1');
ERROR:  "vars" argument is not an object
DETAIL:  Jsonpath parameters should be encoded as key-value pairs of "vars" object.
select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)', '[{"value" : 13}]');
ERROR:  "vars" argument is not an object
DETAIL:  Jsonpath parameters should be encoded as key-value pairs of "vars" object.
select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)', '{"value" : 13}');
 jsonb_path_query 
------------------
 {"a": 10}
(1 row)

select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)', '{"value" : 8}');
 jsonb_path_query 
------------------
(0 rows)

select * from jsonb_path_query('{"a": 10}', '$.a ? (@ < $value)', '{"value" : 13}');
 jsonb_path_query 
------------------
 10
(1 row)

select * from jsonb_path_query('[10,11,12,13,14,15]', '$[*] ? (@ < $value)', '{"value" : 13}');
 jsonb_path_query 
------------------
 10
 11
 12
(3 rows)

select * from jsonb_path_query('[10,11,12,13,14,15]', '$[0,1] ? (@ < $x.value)', '{"x": {"value" : 13}}');
 jsonb_path_query 
------------------
 10
 11
(2 rows)

select * from jsonb_path_query('[10,11,12,13,14,15]', '$[0 to 2] ? (@ < $value)', '{"value" : 15}');
 jsonb_path_query 
------------------
 10
 11
 12
(3 rows)

select * from jsonb_path_query('[1,"1",2,"2",null]', '$[*] ? (@ == "1")');
 jsonb_path_query 
------------------
 "1"
(1 row)

select * from jsonb_path_query('[1,"1",2,"2",null]', '$[*] ? (@ == $value)', '{"value" : "1"}');
 jsonb_path_query 
------------------
 "1"
(1 row)

select * from jsonb_path_query('[1,"1",2,"2",null]', '$[*] ? (@ == $value)', '{"value" : null}');
 jsonb_path_query 
------------------
 null
(1 row)

select * from jsonb_path_query('[1, "2", null]', '$[*] ? (@ != null)');
 jsonb_path_query 
------------------
 1
 "2"
(2 rows)

select * from jsonb_path_query('[1, "2", null]', '$[*] ? (@ == null)');
 jsonb_path_query 
------------------
 null
(1 row)

select * from jsonb_path_query('{}', '$ ? (@ == @)');
 jsonb_path_query 
------------------
(0 rows)

select * from jsonb_path_query('[]', 'strict $ ? (@ == @)');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**');
 jsonb_path_query 
------------------
 {"a": {"b": 1}}
 {"b": 1}
 1
(3 rows)

select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{0}');
 jsonb_path_query 
------------------
 {"a": {"b": 1}}
(1 row)

select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{0 to last}');
 jsonb_path_query 
------------------
 {"a": {"b": 1}}
 {"b": 1}
 1
(3 rows)

select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{1}');
 jsonb_path_query 
------------------
 {"b": 1}
(1 row)

select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{1 to last}');
 jsonb_path_query 
------------------
 {"b": 1}
 1
(2 rows)

select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{2}');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{2 to last}');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{3 to last}');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{last}');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**.b ? (@ > 0)');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{0}.b ? (@ > 0)');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{1}.b ? (@ > 0)');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{0 to last}.b ? (@ > 0)');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{1 to last}.b ? (@ > 0)');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('{"a": {"b": 1}}', 'lax $.**{1 to 2}.b ? (@ > 0)');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**.b ? (@ > 0)');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{0}.b ? (@ > 0)');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{1}.b ? (@ > 0)');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{0 to last}.b ? (@ > 0)');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{1 to last}.b ? (@ > 0)');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{1 to 2}.b ? (@ > 0)');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{2 to 3}.b ? (@ > 0)');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb '{"a": {"b": 1}}' @? '$.**.b ? ( @ > 0)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": {"b": 1}}' @? '$.**{0}.b ? ( @ > 0)';
 ?column? 
----------
 f
(1 row)

select jsonb '{"a": {"b": 1}}' @? '$.**{1}.b ? ( @ > 0)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": {"b": 1}}' @? '$.**{0 to last}.b ? ( @ > 0)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": {"b": 1}}' @? '$.**{1 to last}.b ? ( @ > 0)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": {"b": 1}}' @? '$.**{1 to 2}.b ? ( @ > 0)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**.b ? ( @ > 0)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{0}.b ? ( @ > 0)';
 ?column? 
----------
 f
(1 row)

select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{1}.b ? ( @ > 0)';
 ?column? 
----------
 f
(1 row)

select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{0 to last}.b ? ( @ > 0)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{1 to last}.b ? ( @ > 0)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{1 to 2}.b ? ( @ > 0)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{2 to 3}.b ? ( @ > 0)';
 ?column? 
----------
 t
(1 row)

select jsonb_path_query('{"g": {"x": 2}}', '$.g ? (exists (@.x))');
 jsonb_path_query 
------------------
 {"x": 2}
(1 row)

select jsonb_path_query('{"g": {"x": 2}}', '$.g ? (exists (@.y))');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{"g": {"x": 2}}', '$.g ? (exists (@.x ? (@ >= 2) ))');
 jsonb_path_query 
------------------
 {"x": 2}
(1 row)

select jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'lax $.g ? (exists (@.x))');
 jsonb_path_query 
------------------
 {"x": 2}
(1 row)

select jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'lax $.g ? (exists (@.x + "3"))');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'lax $.g ? ((exists (@.x + "3")) is unknown)');
 jsonb_path_query 
------------------
 {"x": 2}
 {"y": 3}
(2 rows)

select jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g[*] ? (exists (@.x))');
 jsonb_path_query 
------------------
 {"x": 2}
(1 row)

select jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g[*] ? ((exists (@.x)) is unknown)');
 jsonb_path_query 
------------------
 {"y": 3}
(1 row)

select jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g ? (exists (@[*].x))');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g ? ((exists (@[*].x)) is unknown)');
   jsonb_path_query   
----------------------
 [{"x": 2}, {"y": 3}]
(1 row)

--test ternary logic
select
	x, y,
	jsonb_path_query(
		'[true, false, null]',
		'$[*] ? (@ == true  &&  ($x == true && $y == true) ||
				 @ == false && !($x == true && $y == true) ||
				 @ == null  &&  ($x == true && $y == true) is unknown)',
		jsonb_build_object('x', x, 'y', y)
	) as "x && y"
from
	(values (jsonb 'true'), ('false'), ('"null"')) x(x),
	(values (jsonb 'true'), ('false'), ('"null"')) y(y);
   x    |   y    | x && y 
--------+--------+--------
 true   | true   | true
 true   | false  | false
 true   | "null" | null
 false  | true   | false
 false  | false  | false
 false  | "null" | false
 "null" | true   | null
 "null" | false  | false
 "null" | "null" | null
(9 rows)

select
	x, y,
	jsonb_path_query(
		'[true, false, null]',
		'$[*] ? (@ == true  &&  ($x == true || $y == true) ||
				 @ == false && !($x == true || $y == true) ||
				 @ == null  &&  ($x == true || $y == true) is unknown)',
		jsonb_build_object('x', x, 'y', y)
	) as "x || y"
from
	(values (jsonb 'true'), ('false'), ('"null"')) x(x),
	(values (jsonb 'true'), ('false'), ('"null"')) y(y);
   x    |   y    | x || y 
--------+--------+--------
 true   | true   | true
 true   | false  | true
 true   | "null" | true
 false  | true   | true
 false  | false  | false
 false  | "null" | null
 "null" | true   | true
 "null" | false  | null
 "null" | "null" | null
(9 rows)

select jsonb '{"a": 1, "b":1}' @? '$ ? (@.a == @.b)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"c": {"a": 1, "b":1}}' @? '$ ? (@.a == @.b)';
 ?column? 
----------
 f
(1 row)

select jsonb '{"c": {"a": 1, "b":1}}' @? '$.c ? (@.a == @.b)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"c": {"a": 1, "b":1}}' @? '$.c ? ($.c.a == @.b)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"c": {"a": 1, "b":1}}' @? '$.* ? (@.a == @.b)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": 1, "b":1}' @? '$.** ? (@.a == @.b)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"c": {"a": 1, "b":1}}' @? '$.** ? (@.a == @.b)';
 ?column? 
----------
 t
(1 row)

select jsonb_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == 1 + 1)');
 jsonb_path_query 
------------------
 {"a": 2, "b": 1}
(1 row)

select jsonb_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == (1 + 1))');
 jsonb_path_query 
------------------
 {"a": 2, "b": 1}
(1 row)

select jsonb_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == @.b + 1)');
 jsonb_path_query 
------------------
 {"a": 2, "b": 1}
(1 row)

select jsonb_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == (@.b + 1))');
 jsonb_path_query 
------------------
 {"a": 2, "b": 1}
(1 row)

select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == - 1)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == -1)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == -@.b)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == - @.b)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"c": {"a": 0, "b":1}}' @? '$.** ? (@.a == 1 - @.b)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"c": {"a": 2, "b":1}}' @? '$.** ? (@.a == 1 - - @.b)';
 ?column? 
----------
 t
(1 row)

select jsonb '{"c": {"a": 0, "b":1}}' @? '$.** ? (@.a == 1 - +@.b)';
 ?column? 
----------
 t
(1 row)

select jsonb '[1,2,3]' @? '$ ? (+@[*] > +2)';
 ?column? 
----------
 t
(1 row)

select jsonb '[1,2,3]' @? '$ ? (+@[*] > +3)';
 ?column? 
----------
 f
(1 row)

select jsonb '[1,2,3]' @? '$ ? (-@[*] < -2)';
 ?column? 
----------
 t
(1 row)

select jsonb '[1,2,3]' @? '$ ? (-@[*] < -3)';
 ?column? 
----------
 f
(1 row)

select jsonb '1' @? '$ ? ($ > 0)';
 ?column? 
----------
 t
(1 row)

-- arithmetic errors
select jsonb_path_query('[1,2,0,3]', '$[*] ? (2 / @ > 0)');
 jsonb_path_query 
------------------
 1
 2
 3
(3 rows)

select jsonb_path_query('[1,2,0,3]', '$[*] ? ((2 / @ > 0) is unknown)');
 jsonb_path_query 
------------------
 0
(1 row)

select jsonb_path_query('0', '1 / $');
ERROR:  division by zero
select jsonb_path_query('0', '1 / $ + 2');
ERROR:  division by zero
select jsonb_path_query('0', '-(3 + 1 % $)');
ERROR:  division by zero
select jsonb_path_query('1', '$ + "2"');
ERROR:  right operand of jsonpath operator + is not a single numeric value
select jsonb_path_query('[1, 2]', '3 * $');
ERROR:  right operand of jsonpath operator * is not a single numeric value
select jsonb_path_query('"a"', '-$');
ERROR:  operand of unary jsonpath operator - is not a numeric value
select jsonb_path_query('[1,"2",3]', '+$');
ERROR:  operand of unary jsonpath operator + is not a numeric value
select jsonb_path_query('1', '$ + "2"', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[1, 2]', '3 * $', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('"a"', '-$', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[1,"2",3]', '+$', silent => true);
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb '["1",2,0,3]' @? '-$[*]';
 ?column? 
----------
 t
(1 row)

select jsonb '[1,"2",0,3]' @? '-$[*]';
 ?column? 
----------
 t
(1 row)

select jsonb '["1",2,0,3]' @? 'strict -$[*]';
 ?column? 
----------
 
(1 row)

select jsonb '[1,"2",0,3]' @? 'strict -$[*]';
 ?column? 
----------
 
(1 row)

-- unwrapping of operator arguments in lax mode
select jsonb_path_query('{"a": [2]}', 'lax $.a * 3');
 jsonb_path_query 
------------------
 6
(1 row)

select jsonb_path_query('{"a": [2]}', 'lax $.a + 3');
 jsonb_path_query 
------------------
 5
(1 row)

select jsonb_path_query('{"a": [2, 3, 4]}', 'lax -$.a');
 jsonb_path_query 
------------------
 -2
 -3
 -4
(3 rows)

-- should fail
select jsonb_path_query('{"a": [1, 2]}', 'lax $.a * 3');
ERROR:  left operand of jsonpath operator * is not a single numeric value
select jsonb_path_query('{"a": [1, 2]}', 'lax $.a * 3', silent => true);
 jsonb_path_query 
------------------
(0 rows)

-- any key on arrays with and without unwrapping.
select jsonb_path_query('{"a": [1,2,3], "b": [3,4,5]}', '$.*');
 jsonb_path_query 
------------------
 [1, 2, 3]
 [3, 4, 5]
(2 rows)

select jsonb_path_query('[1,2,3]', '$.*');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[1,2,3,{"b": [3,4,5]}]', 'lax $.*');
 jsonb_path_query 
------------------
 [3, 4, 5]
(1 row)

select jsonb_path_query('[1,2,3,{"b": [3,4,5]}]', 'strict $.*');
ERROR:  jsonpath wildcard member accessor can only be applied to an object
select jsonb_path_query('[1,2,3,{"b": [3,4,5]}]', 'strict $.*', NULL, true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb '{"a": [1,2,3], "b": [3,4,5]}' @? '$.*';
 ?column? 
----------
 t
(1 row)

select jsonb '[1,2,3]' @? '$.*';
 ?column? 
----------
 f
(1 row)

select jsonb '[1,2,3,{"b": [3,4,5]}]' @? 'lax $.*';
 ?column? 
----------
 t
(1 row)

select jsonb '[1,2,3,{"b": [3,4,5]}]' @? 'strict $.*';
 ?column? 
----------
 
(1 row)

-- extension: boolean expressions
select jsonb_path_query('2', '$ > 1');
 jsonb_path_query 
------------------
 true
(1 row)

select jsonb_path_query('2', '$ <= 1');
 jsonb_path_query 
------------------
 false
(1 row)

select jsonb_path_query('2', '$ == "2"');
 jsonb_path_query 
------------------
 null
(1 row)

select jsonb '2' @? '$ == "2"';
 ?column? 
----------
 t
(1 row)

select jsonb '2' @@ '$ > 1';
 ?column? 
----------
 t
(1 row)

select jsonb '2' @@ '$ <= 1';
 ?column? 
----------
 f
(1 row)

select jsonb '2' @@ '$ == "2"';
 ?column? 
----------
 
(1 row)

select jsonb '2' @@ '1';
 ?column? 
----------
 
(1 row)

select jsonb '{}' @@ '$';
 ?column? 
----------
 
(1 row)

select jsonb '[]' @@ '$';
 ?column? 
----------
 
(1 row)

select jsonb '[1,2,3]' @@ '$[*]';
 ?column? 
----------
 
(1 row)

select jsonb '[]' @@ '$[*]';
 ?column? 
----------
 
(1 row)

select jsonb_path_match('[[1, true], [2, false]]', 'strict $[*] ? (@[0] > $x) [1]', '{"x": 1}');
 jsonb_path_match 
------------------
 f
(1 row)

select jsonb_path_match('[[1, true], [2, false]]', 'strict $[*] ? (@[0] < $x) [1]', '{"x": 2}');
 jsonb_path_match 
------------------
 t
(1 row)

select jsonb_path_match('[{"a": 1}, {"a": 2}, 3]', 'lax exists($[*].a)', silent => false);
 jsonb_path_match 
------------------
 t
(1 row)

select jsonb_path_match('[{"a": 1}, {"a": 2}, 3]', 'lax exists($[*].a)', silent => true);
 jsonb_path_match 
------------------
 t
(1 row)

select jsonb_path_match('[{"a": 1}, {"a": 2}, 3]', 'strict exists($[*].a)', silent => false);
 jsonb_path_match 
------------------
 
(1 row)

select jsonb_path_match('[{"a": 1}, {"a": 2}, 3]', 'strict exists($[*].a)', silent => true);
 jsonb_path_match 
------------------
 
(1 row)

select jsonb_path_query('[null,1,true,"a",[],{}]', '$.type()');
 jsonb_path_query 
------------------
 "array"
(1 row)

select jsonb_path_query('[null,1,true,"a",[],{}]', 'lax $.type()');
 jsonb_path_query 
------------------
 "array"
(1 row)

select jsonb_path_query('[null,1,true,"a",[],{}]', '$[*].type()');
 jsonb_path_query 
------------------
 "null"
 "number"
 "boolean"
 "string"
 "array"
 "object"
(6 rows)

select jsonb_path_query('null', 'null.type()');
 jsonb_path_query 
------------------
 "null"
(1 row)

select jsonb_path_query('null', 'true.type()');
 jsonb_path_query 
------------------
 "boolean"
(1 row)

select jsonb_path_query('null', '(123).type()');
 jsonb_path_query 
------------------
 "number"
(1 row)

select jsonb_path_query('null', '"123".type()');
 jsonb_path_query 
------------------
 "string"
(1 row)

select jsonb_path_query('{"a": 2}', '($.a - 5).abs() + 10');
 jsonb_path_query 
------------------
 13
(1 row)

select jsonb_path_query('{"a": 2.5}', '-($.a * $.a).floor() % 4.3');
 jsonb_path_query 
------------------
 -1.7
(1 row)

select jsonb_path_query('[1, 2, 3]', '($[*] > 2) ? (@ == true)');
 jsonb_path_query 
------------------
 true
(1 row)

select jsonb_path_query('[1, 2, 3]', '($[*] > 3).type()');
 jsonb_path_query 
------------------
 "boolean"
(1 row)

select jsonb_path_query('[1, 2, 3]', '($[*].a > 3).type()');
 jsonb_path_query 
------------------
 "boolean"
(1 row)

select jsonb_path_query('[1, 2, 3]', 'strict ($[*].a > 3).type()');
 jsonb_path_query 
------------------
 "null"
(1 row)

select jsonb_path_query('[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]', 'strict $[*].size()');
ERROR:  jsonpath item method .size() can only be applied to an array
select jsonb_path_query('[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]', 'strict $[*].size()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]', 'lax $[*].size()');
 jsonb_path_query 
------------------
 1
 1
 1
 1
 0
 1
 3
 1
 1
(9 rows)

select jsonb_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].abs()');
 jsonb_path_query 
------------------
 0
 1
 2
 3.4
 5.6
(5 rows)

select jsonb_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].floor()');
 jsonb_path_query 
------------------
 0
 1
 -2
 -4
 5
(5 rows)

select jsonb_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].ceiling()');
 jsonb_path_query 
------------------
 0
 1
 -2
 -3
 6
(5 rows)

select jsonb_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].ceiling().abs()');
 jsonb_path_query 
------------------
 0
 1
 2
 3
 6
(5 rows)

select jsonb_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].ceiling().abs().type()');
 jsonb_path_query 
------------------
 "number"
 "number"
 "number"
 "number"
 "number"
(5 rows)

select jsonb_path_query('[{},1]', '$[*].keyvalue()');
ERROR:  jsonpath item method .keyvalue() can only be applied to an object
select jsonb_path_query('[{},1]', '$[*].keyvalue()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', '$.keyvalue()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{"a": 1, "b": [1, 2], "c": {"a": "bbb"}}', '$.keyvalue()');
               jsonb_path_query               
----------------------------------------------
 {"id": 0, "key": "a", "value": 1}
 {"id": 0, "key": "b", "value": [1, 2]}
 {"id": 0, "key": "c", "value": {"a": "bbb"}}
(3 rows)

select jsonb_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', '$[*].keyvalue()');
               jsonb_path_query                
-----------------------------------------------
 {"id": 12, "key": "a", "value": 1}
 {"id": 12, "key": "b", "value": [1, 2]}
 {"id": 72, "key": "c", "value": {"a": "bbb"}}
(3 rows)

select jsonb_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', 'strict $.keyvalue()');
ERROR:  jsonpath item method .keyvalue() can only be applied to an object
select jsonb_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', 'lax $.keyvalue()');
               jsonb_path_query                
-----------------------------------------------
 {"id": 12, "key": "a", "value": 1}
 {"id": 12, "key": "b", "value": [1, 2]}
 {"id": 72, "key": "c", "value": {"a": "bbb"}}
(3 rows)

select jsonb_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', 'strict $.keyvalue().a');
ERROR:  jsonpath item method .keyvalue() can only be applied to an object
select jsonb '{"a": 1, "b": [1, 2]}' @? 'lax $.keyvalue()';
 ?column? 
----------
 t
(1 row)

select jsonb '{"a": 1, "b": [1, 2]}' @? 'lax $.keyvalue().key';
 ?column? 
----------
 t
(1 row)

select jsonb_path_query('null', '$.double()');
ERROR:  jsonpath item method .double() can only be applied to a string or numeric value
select jsonb_path_query('true', '$.double()');
ERROR:  jsonpath item method .double() can only be applied to a string or numeric value
select jsonb_path_query('null', '$.double()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('true', '$.double()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', '$.double()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.double()');
ERROR:  jsonpath item method .double() can only be applied to a string or numeric value
select jsonb_path_query('{}', '$.double()');
ERROR:  jsonpath item method .double() can only be applied to a string or numeric value
select jsonb_path_query('[]', 'strict $.double()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', '$.double()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1.23', '$.double()');
 jsonb_path_query 
------------------
 1.23
(1 row)

select jsonb_path_query('"1.23"', '$.double()');
 jsonb_path_query 
------------------
 1.23
(1 row)

select jsonb_path_query('"1.23aaa"', '$.double()');
ERROR:  argument "1.23aaa" of jsonpath item method .double() is invalid for type double precision
select jsonb_path_query('1e1000', '$.double()');
ERROR:  argument "10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" of jsonpath item method .double() is invalid for type double precision
select jsonb_path_query('"nan"', '$.double()');
ERROR:  NaN or Infinity is not allowed for jsonpath item method .double()
select jsonb_path_query('"NaN"', '$.double()');
ERROR:  NaN or Infinity is not allowed for jsonpath item method .double()
select jsonb_path_query('"inf"', '$.double()');
ERROR:  NaN or Infinity is not allowed for jsonpath item method .double()
select jsonb_path_query('"-inf"', '$.double()');
ERROR:  NaN or Infinity is not allowed for jsonpath item method .double()
select jsonb_path_query('"inf"', '$.double()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('"-inf"', '$.double()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', '$.abs()');
ERROR:  jsonpath item method .abs() can only be applied to a numeric value
select jsonb_path_query('true', '$.floor()');
ERROR:  jsonpath item method .floor() can only be applied to a numeric value
select jsonb_path_query('"1.2"', '$.ceiling()');
ERROR:  jsonpath item method .ceiling() can only be applied to a numeric value
select jsonb_path_query('{}', '$.abs()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('true', '$.floor()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('"1.2"', '$.ceiling()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('["", "a", "abc", "abcabc"]', '$[*] ? (@ starts with "abc")');
 jsonb_path_query 
------------------
 "abc"
 "abcabc"
(2 rows)

select jsonb_path_query('["", "a", "abc", "abcabc"]', 'strict $ ? (@[*] starts with "abc")');
      jsonb_path_query      
----------------------------
 ["", "a", "abc", "abcabc"]
(1 row)

select jsonb_path_query('["", "a", "abd", "abdabc"]', 'strict $ ? (@[*] starts with "abc")');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('["abc", "abcabc", null, 1]', 'strict $ ? (@[*] starts with "abc")');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('["abc", "abcabc", null, 1]', 'strict $ ? ((@[*] starts with "abc") is unknown)');
      jsonb_path_query      
----------------------------
 ["abc", "abcabc", null, 1]
(1 row)

select jsonb_path_query('[[null, 1, "abc", "abcabc"]]', 'lax $ ? (@[*] starts with "abc")');
      jsonb_path_query      
----------------------------
 [null, 1, "abc", "abcabc"]
(1 row)

select jsonb_path_query('[[null, 1, "abd", "abdabc"]]', 'lax $ ? ((@[*] starts with "abc") is unknown)');
      jsonb_path_query      
----------------------------
 [null, 1, "abd", "abdabc"]
(1 row)

select jsonb_path_query('[null, 1, "abd", "abdabc"]', 'lax $[*] ? ((@ starts with "abc") is unknown)');
 jsonb_path_query 
------------------
 null
 1
(2 rows)

select jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "babc", "adc\nabc", "ab\nadc"]', 'lax $[*] ? (@ like_regex "^ab.*c")');
 jsonb_path_query 
------------------
 "abc"
 "abdacb"
(2 rows)

select jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "babc", "adc\nabc", "ab\nadc"]', 'lax $[*] ? (@ like_regex "^ab.*c" flag "i")');
 jsonb_path_query 
------------------
 "abc"
 "aBdC"
 "abdacb"
(3 rows)

select jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "babc", "adc\nabc", "ab\nadc"]', 'lax $[*] ? (@ like_regex "^ab.*c" flag "m")');
 jsonb_path_query 
------------------
 "abc"
 "abdacb"
 "adc\nabc"
(3 rows)

select jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "babc", "adc\nabc", "ab\nadc"]', 'lax $[*] ? (@ like_regex "^ab.*c" flag "s")');
 jsonb_path_query 
------------------
 "abc"
 "abdacb"
 "ab\nadc"
(3 rows)

select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "a\\b" flag "q")');
 jsonb_path_query 
------------------
 "a\\b"
 "^a\\b$"
(2 rows)

select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "a\\b" flag "")');
 jsonb_path_query 
------------------
 "a\b"
(1 row)

select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\b$" flag "q")');
 jsonb_path_query 
------------------
 "^a\\b$"
(1 row)

select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\B$" flag "q")');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\B$" flag "iq")');
 jsonb_path_query 
------------------
 "^a\\b$"
(1 row)

select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\b$" flag "")');
 jsonb_path_query 
------------------
 "a\b"
(1 row)

select jsonb_path_query('null', '$.datetime()');
ERROR:  jsonpath item method .datetime() can only be applied to a string
select jsonb_path_query('true', '$.datetime()');
ERROR:  jsonpath item method .datetime() can only be applied to a string
select jsonb_path_query('1', '$.datetime()');
ERROR:  jsonpath item method .datetime() can only be applied to a string
select jsonb_path_query('[]', '$.datetime()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.datetime()');
ERROR:  jsonpath item method .datetime() can only be applied to a string
select jsonb_path_query('{}', '$.datetime()');
ERROR:  jsonpath item method .datetime() can only be applied to a string
select jsonb_path_query('"bogus"', '$.datetime()');
ERROR:  datetime format is not recognized: "bogus"
HINT:  Use a datetime template argument to specify the input data format.
select jsonb_path_query('"12:34"', '$.datetime("aaa")');
ERROR:  invalid datetime format separator: "a"
select jsonb_path_query('"aaaa"', '$.datetime("HH24")');
ERROR:  invalid value "aa" for "HH24"
DETAIL:  Value must be an integer.
select jsonb '"10-03-2017"' @? '$.datetime("dd-mm-yyyy")';
 ?column? 
----------
 t
(1 row)

select jsonb_path_query('"10-03-2017"', '$.datetime("dd-mm-yyyy")');
 jsonb_path_query 
------------------
 "2017-03-10"
(1 row)

select jsonb_path_query('"10-03-2017"', '$.datetime("dd-mm-yyyy").type()');
 jsonb_path_query 
------------------
 "date"
(1 row)

select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy")');
ERROR:  trailing characters remain in input string after datetime format
select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy").type()');
ERROR:  trailing characters remain in input string after datetime format
select jsonb_path_query('"10-03-2017 12:34"', '       $.datetime("dd-mm-yyyy HH24:MI").type()');
       jsonb_path_query        
-------------------------------
 "timestamp without time zone"
(1 row)

select jsonb_path_query('"10-03-2017 12:34 +05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM").type()');
      jsonb_path_query      
----------------------------
 "timestamp with time zone"
(1 row)

select jsonb_path_query('"12:34:56"', '$.datetime("HH24:MI:SS").type()');
     jsonb_path_query     
--------------------------
 "time without time zone"
(1 row)

select jsonb_path_query('"12:34:56 +05:20"', '$.datetime("HH24:MI:SS TZH:TZM").type()');
   jsonb_path_query    
-----------------------
 "time with time zone"
(1 row)

select jsonb_path_query('"10-03-2017T12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")');
   jsonb_path_query    
-----------------------
 "2017-03-10T12:34:56"
(1 row)

select jsonb_path_query('"10-03-2017t12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")');
ERROR:  unmatched format character "T"
select jsonb_path_query('"10-03-2017 12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")');
ERROR:  unmatched format character "T"
-- Test .bigint()
select jsonb_path_query('null', '$.bigint()');
ERROR:  jsonpath item method .bigint() can only be applied to a string or numeric value
select jsonb_path_query('true', '$.bigint()');
ERROR:  jsonpath item method .bigint() can only be applied to a string or numeric value
select jsonb_path_query('null', '$.bigint()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('true', '$.bigint()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', '$.bigint()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.bigint()');
ERROR:  jsonpath item method .bigint() can only be applied to a string or numeric value
select jsonb_path_query('{}', '$.bigint()');
ERROR:  jsonpath item method .bigint() can only be applied to a string or numeric value
select jsonb_path_query('[]', 'strict $.bigint()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', '$.bigint()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('"1.23"', '$.bigint()');
ERROR:  argument "1.23" of jsonpath item method .bigint() is invalid for type bigint
select jsonb_path_query('"1.23aaa"', '$.bigint()');
ERROR:  argument "1.23aaa" of jsonpath item method .bigint() is invalid for type bigint
select jsonb_path_query('1e1000', '$.bigint()');
ERROR:  argument "10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" of jsonpath item method .bigint() is invalid for type bigint
select jsonb_path_query('"nan"', '$.bigint()');
ERROR:  argument "nan" of jsonpath item method .bigint() is invalid for type bigint
select jsonb_path_query('"NaN"', '$.bigint()');
ERROR:  argument "NaN" of jsonpath item method .bigint() is invalid for type bigint
select jsonb_path_query('"inf"', '$.bigint()');
ERROR:  argument "inf" of jsonpath item method .bigint() is invalid for type bigint
select jsonb_path_query('"-inf"', '$.bigint()');
ERROR:  argument "-inf" of jsonpath item method .bigint() is invalid for type bigint
select jsonb_path_query('"inf"', '$.bigint()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('"-inf"', '$.bigint()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('123', '$.bigint()');
 jsonb_path_query 
------------------
 123
(1 row)

select jsonb_path_query('"123"', '$.bigint()');
 jsonb_path_query 
------------------
 123
(1 row)

select jsonb_path_query('1.23', '$.bigint()');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('1.83', '$.bigint()');
 jsonb_path_query 
------------------
 2
(1 row)

select jsonb_path_query('1234567890123', '$.bigint()');
 jsonb_path_query 
------------------
 1234567890123
(1 row)

select jsonb_path_query('"1234567890123"', '$.bigint()');
 jsonb_path_query 
------------------
 1234567890123
(1 row)

select jsonb_path_query('12345678901234567890', '$.bigint()');
ERROR:  argument "12345678901234567890" of jsonpath item method .bigint() is invalid for type bigint
select jsonb_path_query('"12345678901234567890"', '$.bigint()');
ERROR:  argument "12345678901234567890" of jsonpath item method .bigint() is invalid for type bigint
select jsonb_path_query('"+123"', '$.bigint()');
 jsonb_path_query 
------------------
 123
(1 row)

select jsonb_path_query('-123', '$.bigint()');
 jsonb_path_query 
------------------
 -123
(1 row)

select jsonb_path_query('"-123"', '$.bigint()');
 jsonb_path_query 
------------------
 -123
(1 row)

select jsonb_path_query('123', '$.bigint() * 2');
 jsonb_path_query 
------------------
 246
(1 row)

-- Test .boolean()
select jsonb_path_query('null', '$.boolean()');
ERROR:  jsonpath item method .boolean() can only be applied to a boolean, string, or numeric value
select jsonb_path_query('null', '$.boolean()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', '$.boolean()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.boolean()');
ERROR:  jsonpath item method .boolean() can only be applied to a boolean, string, or numeric value
select jsonb_path_query('{}', '$.boolean()');
ERROR:  jsonpath item method .boolean() can only be applied to a boolean, string, or numeric value
select jsonb_path_query('[]', 'strict $.boolean()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', '$.boolean()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1.23', '$.boolean()');
ERROR:  argument "1.23" of jsonpath item method .boolean() is invalid for type boolean
select jsonb_path_query('"1.23"', '$.boolean()');
ERROR:  argument "1.23" of jsonpath item method .boolean() is invalid for type boolean
select jsonb_path_query('"1.23aaa"', '$.boolean()');
ERROR:  argument "1.23aaa" of jsonpath item method .boolean() is invalid for type boolean
select jsonb_path_query('1e1000', '$.boolean()');
ERROR:  argument "10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" of jsonpath item method .boolean() is invalid for type boolean
select jsonb_path_query('"nan"', '$.boolean()');
ERROR:  argument "nan" of jsonpath item method .boolean() is invalid for type boolean
select jsonb_path_query('"NaN"', '$.boolean()');
ERROR:  argument "NaN" of jsonpath item method .boolean() is invalid for type boolean
select jsonb_path_query('"inf"', '$.boolean()');
ERROR:  argument "inf" of jsonpath item method .boolean() is invalid for type boolean
select jsonb_path_query('"-inf"', '$.boolean()');
ERROR:  argument "-inf" of jsonpath item method .boolean() is invalid for type boolean
select jsonb_path_query('"inf"', '$.boolean()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('"-inf"', '$.boolean()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('"100"', '$.boolean()');
ERROR:  argument "100" of jsonpath item method .boolean() is invalid for type boolean
select jsonb_path_query('true', '$.boolean()');
 jsonb_path_query 
------------------
 true
(1 row)

select jsonb_path_query('false', '$.boolean()');
 jsonb_path_query 
------------------
 false
(1 row)

select jsonb_path_query('1', '$.boolean()');
 jsonb_path_query 
------------------
 true
(1 row)

select jsonb_path_query('0', '$.boolean()');
 jsonb_path_query 
------------------
 false
(1 row)

select jsonb_path_query('-1', '$.boolean()');
 jsonb_path_query 
------------------
 true
(1 row)

select jsonb_path_query('100', '$.boolean()');
 jsonb_path_query 
------------------
 true
(1 row)

select jsonb_path_query('"1"', '$.boolean()');
 jsonb_path_query 
------------------
 true
(1 row)

select jsonb_path_query('"0"', '$.boolean()');
 jsonb_path_query 
------------------
 false
(1 row)

select jsonb_path_query('"true"', '$.boolean()');
 jsonb_path_query 
------------------
 true
(1 row)

select jsonb_path_query('"false"', '$.boolean()');
 jsonb_path_query 
------------------
 false
(1 row)

select jsonb_path_query('"TRUE"', '$.boolean()');
 jsonb_path_query 
------------------
 true
(1 row)

select jsonb_path_query('"FALSE"', '$.boolean()');
 jsonb_path_query 
------------------
 false
(1 row)

select jsonb_path_query('"yes"', '$.boolean()');
 jsonb_path_query 
------------------
 true
(1 row)

select jsonb_path_query('"NO"', '$.boolean()');
 jsonb_path_query 
------------------
 false
(1 row)

select jsonb_path_query('"T"', '$.boolean()');
 jsonb_path_query 
------------------
 true
(1 row)

select jsonb_path_query('"f"', '$.boolean()');
 jsonb_path_query 
------------------
 false
(1 row)

select jsonb_path_query('"y"', '$.boolean()');
 jsonb_path_query 
------------------
 true
(1 row)

select jsonb_path_query('"N"', '$.boolean()');
 jsonb_path_query 
------------------
 false
(1 row)

select jsonb_path_query('true', '$.boolean().type()');
 jsonb_path_query 
------------------
 "boolean"
(1 row)

select jsonb_path_query('123', '$.boolean().type()');
 jsonb_path_query 
------------------
 "boolean"
(1 row)

select jsonb_path_query('"Yes"', '$.boolean().type()');
 jsonb_path_query 
------------------
 "boolean"
(1 row)

select jsonb_path_query_array('[1, "yes", false]', '$[*].boolean()');
 jsonb_path_query_array 
------------------------
 [true, true, false]
(1 row)

-- Test .date()
select jsonb_path_query('null', '$.date()');
ERROR:  jsonpath item method .date() can only be applied to a string
select jsonb_path_query('true', '$.date()');
ERROR:  jsonpath item method .date() can only be applied to a string
select jsonb_path_query('1', '$.date()');
ERROR:  jsonpath item method .date() can only be applied to a string
select jsonb_path_query('[]', '$.date()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.date()');
ERROR:  jsonpath item method .date() can only be applied to a string
select jsonb_path_query('{}', '$.date()');
ERROR:  jsonpath item method .date() can only be applied to a string
select jsonb_path_query('"bogus"', '$.date()');
ERROR:  date format is not recognized: "bogus"
select jsonb '"2023-08-15"' @? '$.date()';
 ?column? 
----------
 t
(1 row)

select jsonb_path_query('"2023-08-15"', '$.date()');
 jsonb_path_query 
------------------
 "2023-08-15"
(1 row)

select jsonb_path_query('"2023-08-15"', '$.date().type()');
 jsonb_path_query 
------------------
 "date"
(1 row)

select jsonb_path_query('"12:34:56"', '$.date()');
ERROR:  date format is not recognized: "12:34:56"
select jsonb_path_query('"12:34:56 +05:30"', '$.date()');
ERROR:  date format is not recognized: "12:34:56 +05:30"
select jsonb_path_query('"2023-08-15 12:34:56"', '$.date()');
 jsonb_path_query 
------------------
 "2023-08-15"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.date()');
ERROR:  cannot convert value from timestamptz to date without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.date()'); -- should work
 jsonb_path_query_tz 
---------------------
 "2023-08-15"
(1 row)

select jsonb_path_query('"2023-08-15"', '$.date(2)');
ERROR:  syntax error at or near "2" of jsonpath input
LINE 1: select jsonb_path_query('"2023-08-15"', '$.date(2)');
                                                ^
-- Test .decimal()
select jsonb_path_query('null', '$.decimal()');
ERROR:  jsonpath item method .decimal() can only be applied to a string or numeric value
select jsonb_path_query('true', '$.decimal()');
ERROR:  jsonpath item method .decimal() can only be applied to a string or numeric value
select jsonb_path_query('null', '$.decimal()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('true', '$.decimal()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', '$.decimal()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.decimal()');
ERROR:  jsonpath item method .decimal() can only be applied to a string or numeric value
select jsonb_path_query('{}', '$.decimal()');
ERROR:  jsonpath item method .decimal() can only be applied to a string or numeric value
select jsonb_path_query('[]', 'strict $.decimal()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', '$.decimal()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1.23', '$.decimal()');
 jsonb_path_query 
------------------
 1.23
(1 row)

select jsonb_path_query('"1.23"', '$.decimal()');
 jsonb_path_query 
------------------
 1.23
(1 row)

select jsonb_path_query('"1.23aaa"', '$.decimal()');
ERROR:  argument "1.23aaa" of jsonpath item method .decimal() is invalid for type numeric
select jsonb_path_query('1e1000', '$.decimal()');
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             jsonb_path_query                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
(1 row)

select jsonb_path_query('"nan"', '$.decimal()');
ERROR:  NaN or Infinity is not allowed for jsonpath item method .decimal()
select jsonb_path_query('"NaN"', '$.decimal()');
ERROR:  NaN or Infinity is not allowed for jsonpath item method .decimal()
select jsonb_path_query('"inf"', '$.decimal()');
ERROR:  NaN or Infinity is not allowed for jsonpath item method .decimal()
select jsonb_path_query('"-inf"', '$.decimal()');
ERROR:  NaN or Infinity is not allowed for jsonpath item method .decimal()
select jsonb_path_query('"inf"', '$.decimal()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('"-inf"', '$.decimal()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('123', '$.decimal()');
 jsonb_path_query 
------------------
 123
(1 row)

select jsonb_path_query('"123"', '$.decimal()');
 jsonb_path_query 
------------------
 123
(1 row)

select jsonb_path_query('12345678901234567890', '$.decimal()');
   jsonb_path_query   
----------------------
 12345678901234567890
(1 row)

select jsonb_path_query('"12345678901234567890"', '$.decimal()');
   jsonb_path_query   
----------------------
 12345678901234567890
(1 row)

select jsonb_path_query('"+12.3"', '$.decimal()');
 jsonb_path_query 
------------------
 12.3
(1 row)

select jsonb_path_query('-12.3', '$.decimal()');
 jsonb_path_query 
------------------
 -12.3
(1 row)

select jsonb_path_query('"-12.3"', '$.decimal()');
 jsonb_path_query 
------------------
 -12.3
(1 row)

select jsonb_path_query('12.3', '$.decimal() * 2');
 jsonb_path_query 
------------------
 24.6
(1 row)

select jsonb_path_query('12345.678', '$.decimal(6, 1)');
 jsonb_path_query 
------------------
 12345.7
(1 row)

select jsonb_path_query('12345.678', '$.decimal(6, 2)');
ERROR:  argument "12345.678" of jsonpath item method .decimal() is invalid for type numeric
select jsonb_path_query('1234.5678', '$.decimal(6, 2)');
 jsonb_path_query 
------------------
 1234.57
(1 row)

select jsonb_path_query('12345.678', '$.decimal(4, 6)');
ERROR:  argument "12345.678" of jsonpath item method .decimal() is invalid for type numeric
select jsonb_path_query('12345.678', '$.decimal(0, 6)');
ERROR:  NUMERIC precision 0 must be between 1 and 1000
select jsonb_path_query('12345.678', '$.decimal(1001, 6)');
ERROR:  NUMERIC precision 1001 must be between 1 and 1000
select jsonb_path_query('1234.5678', '$.decimal(+6, +2)');
 jsonb_path_query 
------------------
 1234.57
(1 row)

select jsonb_path_query('1234.5678', '$.decimal(+6, -2)');
 jsonb_path_query 
------------------
 1200
(1 row)

select jsonb_path_query('1234.5678', '$.decimal(-6, +2)');
ERROR:  NUMERIC precision -6 must be between 1 and 1000
select jsonb_path_query('1234.5678', '$.decimal(6, -1001)');
ERROR:  NUMERIC scale -1001 must be between -1000 and 1000
select jsonb_path_query('1234.5678', '$.decimal(6, 1001)');
ERROR:  NUMERIC scale 1001 must be between -1000 and 1000
select jsonb_path_query('-1234.5678', '$.decimal(+6, -2)');
 jsonb_path_query 
------------------
 -1200
(1 row)

select jsonb_path_query('0.0123456', '$.decimal(1,2)');
 jsonb_path_query 
------------------
 0.01
(1 row)

select jsonb_path_query('0.0012345', '$.decimal(2,4)');
 jsonb_path_query 
------------------
 0.0012
(1 row)

select jsonb_path_query('-0.00123456', '$.decimal(2,-4)');
 jsonb_path_query 
------------------
 0
(1 row)

select jsonb_path_query('12.3', '$.decimal(12345678901,1)');
ERROR:  precision of jsonpath item method .decimal() is out of range for type integer
select jsonb_path_query('12.3', '$.decimal(1,12345678901)');
ERROR:  scale of jsonpath item method .decimal() is out of range for type integer
-- An out-of-range precision or scale does not fail in silent mode.
select jsonb_path_query('12345.678', '$.decimal(0, 6)', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('12345.678', '$.decimal(1001, 6)', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1234.5678', '$.decimal(-6, +2)', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1234.5678', '$.decimal(6, -1001)', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1234.5678', '$.decimal(6, 1001)', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select '1234.5678'::jsonb @? '$.decimal(0)';
 ?column? 
----------
 
(1 row)

-- Test .integer()
select jsonb_path_query('null', '$.integer()');
ERROR:  jsonpath item method .integer() can only be applied to a string or numeric value
select jsonb_path_query('true', '$.integer()');
ERROR:  jsonpath item method .integer() can only be applied to a string or numeric value
select jsonb_path_query('null', '$.integer()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('true', '$.integer()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', '$.integer()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.integer()');
ERROR:  jsonpath item method .integer() can only be applied to a string or numeric value
select jsonb_path_query('{}', '$.integer()');
ERROR:  jsonpath item method .integer() can only be applied to a string or numeric value
select jsonb_path_query('[]', 'strict $.integer()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', '$.integer()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('"1.23"', '$.integer()');
ERROR:  argument "1.23" of jsonpath item method .integer() is invalid for type integer
select jsonb_path_query('"1.23aaa"', '$.integer()');
ERROR:  argument "1.23aaa" of jsonpath item method .integer() is invalid for type integer
select jsonb_path_query('1e1000', '$.integer()');
ERROR:  argument "10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" of jsonpath item method .integer() is invalid for type integer
select jsonb_path_query('"nan"', '$.integer()');
ERROR:  argument "nan" of jsonpath item method .integer() is invalid for type integer
select jsonb_path_query('"NaN"', '$.integer()');
ERROR:  argument "NaN" of jsonpath item method .integer() is invalid for type integer
select jsonb_path_query('"inf"', '$.integer()');
ERROR:  argument "inf" of jsonpath item method .integer() is invalid for type integer
select jsonb_path_query('"-inf"', '$.integer()');
ERROR:  argument "-inf" of jsonpath item method .integer() is invalid for type integer
select jsonb_path_query('"inf"', '$.integer()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('"-inf"', '$.integer()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('123', '$.integer()');
 jsonb_path_query 
------------------
 123
(1 row)

select jsonb_path_query('"123"', '$.integer()');
 jsonb_path_query 
------------------
 123
(1 row)

select jsonb_path_query('1.23', '$.integer()');
 jsonb_path_query 
------------------
 1
(1 row)

select jsonb_path_query('1.83', '$.integer()');
 jsonb_path_query 
------------------
 2
(1 row)

select jsonb_path_query('12345678901', '$.integer()');
ERROR:  argument "12345678901" of jsonpath item method .integer() is invalid for type integer
select jsonb_path_query('"12345678901"', '$.integer()');
ERROR:  argument "12345678901" of jsonpath item method .integer() is invalid for type integer
select jsonb_path_query('"+123"', '$.integer()');
 jsonb_path_query 
------------------
 123
(1 row)

select jsonb_path_query('-123', '$.integer()');
 jsonb_path_query 
------------------
 -123
(1 row)

select jsonb_path_query('"-123"', '$.integer()');
 jsonb_path_query 
------------------
 -123
(1 row)

select jsonb_path_query('123', '$.integer() * 2');
 jsonb_path_query 
------------------
 246
(1 row)

-- Test .number()
select jsonb_path_query('null', '$.number()');
ERROR:  jsonpath item method .number() can only be applied to a string or numeric value
select jsonb_path_query('true', '$.number()');
ERROR:  jsonpath item method .number() can only be applied to a string or numeric value
select jsonb_path_query('null', '$.number()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('true', '$.number()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', '$.number()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.number()');
ERROR:  jsonpath item method .number() can only be applied to a string or numeric value
select jsonb_path_query('{}', '$.number()');
ERROR:  jsonpath item method .number() can only be applied to a string or numeric value
select jsonb_path_query('[]', 'strict $.number()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', '$.number()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1.23', '$.number()');
 jsonb_path_query 
------------------
 1.23
(1 row)

select jsonb_path_query('"1.23"', '$.number()');
 jsonb_path_query 
------------------
 1.23
(1 row)

select jsonb_path_query('"1.23aaa"', '$.number()');
ERROR:  argument "1.23aaa" of jsonpath item method .number() is invalid for type numeric
select jsonb_path_query('1e1000', '$.number()');
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             jsonb_path_query                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
(1 row)

select jsonb_path_query('"nan"', '$.number()');
ERROR:  NaN or Infinity is not allowed for jsonpath item method .number()
select jsonb_path_query('"NaN"', '$.number()');
ERROR:  NaN or Infinity is not allowed for jsonpath item method .number()
select jsonb_path_query('"inf"', '$.number()');
ERROR:  NaN or Infinity is not allowed for jsonpath item method .number()
select jsonb_path_query('"-inf"', '$.number()');
ERROR:  NaN or Infinity is not allowed for jsonpath item method .number()
select jsonb_path_query('"inf"', '$.number()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('"-inf"', '$.number()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('123', '$.number()');
 jsonb_path_query 
------------------
 123
(1 row)

select jsonb_path_query('"123"', '$.number()');
 jsonb_path_query 
------------------
 123
(1 row)

select jsonb_path_query('12345678901234567890', '$.number()');
   jsonb_path_query   
----------------------
 12345678901234567890
(1 row)

select jsonb_path_query('"12345678901234567890"', '$.number()');
   jsonb_path_query   
----------------------
 12345678901234567890
(1 row)

select jsonb_path_query('"+12.3"', '$.number()');
 jsonb_path_query 
------------------
 12.3
(1 row)

select jsonb_path_query('-12.3', '$.number()');
 jsonb_path_query 
------------------
 -12.3
(1 row)

select jsonb_path_query('"-12.3"', '$.number()');
 jsonb_path_query 
------------------
 -12.3
(1 row)

select jsonb_path_query('12.3', '$.number() * 2');
 jsonb_path_query 
------------------
 24.6
(1 row)

-- Test .string()
select jsonb_path_query('null', '$.string()');
ERROR:  jsonpath item method .string() can only be applied to a boolean, string, numeric, or datetime value
select jsonb_path_query('null', '$.string()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', '$.string()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.string()');
ERROR:  jsonpath item method .string() can only be applied to a boolean, string, numeric, or datetime value
select jsonb_path_query('{}', '$.string()');
ERROR:  jsonpath item method .string() can only be applied to a boolean, string, numeric, or datetime value
select jsonb_path_query('[]', 'strict $.string()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', '$.string()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1.23', '$.string()');
 jsonb_path_query 
------------------
 "1.23"
(1 row)

select jsonb_path_query('"1.23"', '$.string()');
 jsonb_path_query 
------------------
 "1.23"
(1 row)

select jsonb_path_query('"1.23aaa"', '$.string()');
 jsonb_path_query 
------------------
 "1.23aaa"
(1 row)

select jsonb_path_query('1234', '$.string()');
 jsonb_path_query 
------------------
 "1234"
(1 row)

select jsonb_path_query('true', '$.string()');
 jsonb_path_query 
------------------
 "true"
(1 row)

select jsonb_path_query('1234', '$.string().type()');
 jsonb_path_query 
------------------
 "string"
(1 row)

select jsonb_path_query('[2, true]', '$.string()');
 jsonb_path_query 
------------------
 "2"
 "true"
(2 rows)

select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string()');
  jsonb_path_query_array  
--------------------------
 ["1.23", "yes", "false"]
(1 row)

select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string().type()');
     jsonb_path_query_array     
--------------------------------
 ["string", "string", "string"]
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
ERROR:  cannot convert value from timestamptz to timestamp without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
  jsonb_path_query_tz  
-----------------------
 "2023-08-15T00:04:56"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()'); -- should work
     jsonb_path_query_tz     
-----------------------------
 "2023-08-15T12:34:56-07:00"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
      jsonb_path_query       
-----------------------------
 "2023-08-15T12:34:56+05:30"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
   jsonb_path_query    
-----------------------
 "2023-08-15T12:34:56"
(1 row)

select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
 jsonb_path_query 
------------------
 "12:34:56+05:30"
(1 row)

-- this timetz usage will absorb the UTC offset of the current timezone setting
begin;
set local timezone = 'UTC-10';
select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
 jsonb_path_query_tz 
---------------------
 "12:34:56+10:00"
(1 row)

rollback;
select jsonb_path_query('"12:34:56"', '$.time().string()');
 jsonb_path_query 
------------------
 "12:34:56"
(1 row)

select jsonb_path_query('"2023-08-15"', '$.date().string()');
 jsonb_path_query 
------------------
 "2023-08-15"
(1 row)

-- .string() does not react to timezone or datestyle
begin;
set local timezone = 'UTC';
set local datestyle = 'German';
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
      jsonb_path_query       
-----------------------------
 "2023-08-15T12:34:56+05:30"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
   jsonb_path_query    
-----------------------
 "2023-08-15T12:34:56"
(1 row)

rollback;
-- test .ltrim()
select jsonb_path_query('"   hello   "', '$.ltrim(" ")');
 jsonb_path_query 
------------------
 "hello   "
(1 row)

select jsonb_path_query('"   hello   "', '$.ltrim()');
 jsonb_path_query 
------------------
 "hello   "
(1 row)

select jsonb_path_query('"zzzytest"', '$.ltrim("xyz")');
 jsonb_path_query 
------------------
 "test"
(1 row)

select jsonb_path_query('null', '$.ltrim()');
ERROR:  jsonpath item method .ltrim() can only be applied to a string
select jsonb_path_query('null', '$.ltrim()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', '$.ltrim()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.ltrim()');
ERROR:  jsonpath item method .ltrim() can only be applied to a string
select jsonb_path_query('{}', '$.ltrim()');
ERROR:  jsonpath item method .ltrim() can only be applied to a string
select jsonb_path_query('[]', 'strict $.ltrim()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', '$.ltrim()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1.23', '$.ltrim()');
ERROR:  jsonpath item method .ltrim() can only be applied to a string
select jsonb_path_query('"1.23"', '$.ltrim()');
 jsonb_path_query 
------------------
 "1.23"
(1 row)

select jsonb_path_query('"1.23aaa"', '$.ltrim()');
 jsonb_path_query 
------------------
 "1.23aaa"
(1 row)

select jsonb_path_query('1234', '$.ltrim()');
ERROR:  jsonpath item method .ltrim() can only be applied to a string
select jsonb_path_query('true', '$.ltrim()');
ERROR:  jsonpath item method .ltrim() can only be applied to a string
select jsonb_path_query('1234', '$.ltrim().type()');
ERROR:  jsonpath item method .ltrim() can only be applied to a string
select jsonb_path_query('[2, true]', '$.ltrim()');
ERROR:  jsonpath item method .ltrim() can only be applied to a string
select jsonb_path_query_array('["  maybe  ", "  yes", "  no"]', '$[*].ltrim()');
  jsonb_path_query_array  
--------------------------
 ["maybe  ", "yes", "no"]
(1 row)

select jsonb_path_query_array('["  maybe  ", "  yes", "  no"]', '$[*].ltrim().type()');
     jsonb_path_query_array     
--------------------------------
 ["string", "string", "string"]
(1 row)

-- test .rtrim()
select jsonb_path_query('"   hello   "', '$.rtrim(" ")');
 jsonb_path_query 
------------------
 "   hello"
(1 row)

select jsonb_path_query('"testxxzx"', '$.rtrim("xyz")');
 jsonb_path_query 
------------------
 "test"
(1 row)

select jsonb_path_query('"   hello   "', '$.rtrim()');
 jsonb_path_query 
------------------
 "   hello"
(1 row)

-- test .btrim()
select jsonb_path_query('"   hello   "', '$.btrim(" ")');
 jsonb_path_query 
------------------
 "hello"
(1 row)

select jsonb_path_query('"xyxtrimyyx"', '$.btrim("xyz")');
 jsonb_path_query 
------------------
 "trim"
(1 row)

select jsonb_path_query('"   hello   "', '$.btrim()');
 jsonb_path_query 
------------------
 "hello"
(1 row)

-- test .lower()
select jsonb_path_query('null', '$.lower()');
ERROR:  jsonpath item method .lower() can only be applied to a string
select jsonb_path_query('null', '$.lower()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', '$.lower()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.lower()');
ERROR:  jsonpath item method .lower() can only be applied to a string
select jsonb_path_query('{}', '$.lower()');
ERROR:  jsonpath item method .lower() can only be applied to a string
select jsonb_path_query('[]', 'strict $.lower()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', '$.lower()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1.23', '$.lower()');
ERROR:  jsonpath item method .lower() can only be applied to a string
select jsonb_path_query('"1.23"', '$.lower()');
 jsonb_path_query 
------------------
 "1.23"
(1 row)

select jsonb_path_query('"1.23aaa"', '$.lower()');
 jsonb_path_query 
------------------
 "1.23aaa"
(1 row)

select jsonb_path_query('1234', '$.lower()');
ERROR:  jsonpath item method .lower() can only be applied to a string
select jsonb_path_query('true', '$.lower()');
ERROR:  jsonpath item method .lower() can only be applied to a string
select jsonb_path_query('1234', '$.lower().type()');
ERROR:  jsonpath item method .lower() can only be applied to a string
select jsonb_path_query('[2, true]', '$.lower()');
ERROR:  jsonpath item method .lower() can only be applied to a string
select jsonb_path_query_array('["maybe", "yes", "no"]', '$[*].lower()');
 jsonb_path_query_array 
------------------------
 ["maybe", "yes", "no"]
(1 row)

select jsonb_path_query_array('["maybe", "yes", "no"]', '$[*].lower().type()');
     jsonb_path_query_array     
--------------------------------
 ["string", "string", "string"]
(1 row)

-- test .upper()
select jsonb_path_query('null', '$.upper()');
ERROR:  jsonpath item method .upper() can only be applied to a string
select jsonb_path_query('null', '$.upper()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', '$.upper()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.upper()');
ERROR:  jsonpath item method .upper() can only be applied to a string
select jsonb_path_query('{}', '$.upper()');
ERROR:  jsonpath item method .upper() can only be applied to a string
select jsonb_path_query('[]', 'strict $.upper()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', '$.upper()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1.23', '$.upper()');
ERROR:  jsonpath item method .upper() can only be applied to a string
select jsonb_path_query('"1.23"', '$.upper()');
 jsonb_path_query 
------------------
 "1.23"
(1 row)

select jsonb_path_query('"1.23aaa"', '$.upper()');
 jsonb_path_query 
------------------
 "1.23AAA"
(1 row)

select jsonb_path_query('1234', '$.upper()');
ERROR:  jsonpath item method .upper() can only be applied to a string
select jsonb_path_query('true', '$.upper()');
ERROR:  jsonpath item method .upper() can only be applied to a string
select jsonb_path_query('1234', '$.upper().type()');
ERROR:  jsonpath item method .upper() can only be applied to a string
select jsonb_path_query('[2, true]', '$.upper()');
ERROR:  jsonpath item method .upper() can only be applied to a string
select jsonb_path_query_array('["maybe", "yes", "no"]', '$[*].upper()');
 jsonb_path_query_array 
------------------------
 ["MAYBE", "YES", "NO"]
(1 row)

select jsonb_path_query_array('["maybe", "yes", "no"]', '$[*].upper().type()');
     jsonb_path_query_array     
--------------------------------
 ["string", "string", "string"]
(1 row)

-- test .initcap()
select jsonb_path_query('null', '$.initcap()');
ERROR:  jsonpath item method .initcap() can only be applied to a string
select jsonb_path_query('null', '$.initcap()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', '$.initcap()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.initcap()');
ERROR:  jsonpath item method .initcap() can only be applied to a string
select jsonb_path_query('{}', '$.initcap()');
ERROR:  jsonpath item method .initcap() can only be applied to a string
select jsonb_path_query('[]', 'strict $.initcap()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', '$.initcap()', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1.23', '$.initcap()');
ERROR:  jsonpath item method .initcap() can only be applied to a string
select jsonb_path_query('"1.23"', '$.initcap()');
 jsonb_path_query 
------------------
 "1.23"
(1 row)

select jsonb_path_query('"1.23aaa"', '$.initcap()');
 jsonb_path_query 
------------------
 "1.23aaa"
(1 row)

select jsonb_path_query('1234', '$.initcap()');
ERROR:  jsonpath item method .initcap() can only be applied to a string
select jsonb_path_query('true', '$.initcap()');
ERROR:  jsonpath item method .initcap() can only be applied to a string
select jsonb_path_query('1234', '$.initcap().type()');
ERROR:  jsonpath item method .initcap() can only be applied to a string
select jsonb_path_query('[2, true]', '$.initcap()');
ERROR:  jsonpath item method .initcap() can only be applied to a string
select jsonb_path_query('["maybe yes", "probably no"]', '$.initcap()');
 jsonb_path_query 
------------------
 "Maybe Yes"
 "Probably No"
(2 rows)

-- Test .replace()
select jsonb_path_query('null', '$.replace("x", "bye")');
ERROR:  jsonpath item method .replace() can only be applied to a string
select jsonb_path_query('null', '$.replace("x", "bye")', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('["x", "y", "z"]', '$.replace("x", "bye")');
 jsonb_path_query 
------------------
 "bye"
 "y"
 "z"
(3 rows)

select jsonb_path_query('{}', '$.replace("x", "bye")');
ERROR:  jsonpath item method .replace() can only be applied to a string
select jsonb_path_query('[]', 'strict $.replace("x", "bye")', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('{}', '$.replace("x", "bye")', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('1.23', '$.replace("x", "bye")');
ERROR:  jsonpath item method .replace() can only be applied to a string
select jsonb_path_query('"hello world"', '$.replace("hello","bye")');
 jsonb_path_query 
------------------
 "bye world"
(1 row)

select jsonb_path_query('"hello world"', '$.replace("hello","bye") starts with "bye"');
 jsonb_path_query 
------------------
 true
(1 row)

-- Test .split_part()
select jsonb_path_query('"abc~@~def~@~ghi"', '$.split_part("~@~", 2)');
 jsonb_path_query 
------------------
 "def"
(1 row)

select jsonb_path_query('"abc,def,ghi,jkl"', '$.split_part(",", -2)');
 jsonb_path_query 
------------------
 "ghi"
(1 row)

select jsonb_path_query('"a,b"', '$.split_part(",", 0)');
ERROR:  field position of jsonpath item method .split_part() must not be zero
select jsonb_path_query('"a,b"', '$.split_part(",", 0)', silent => true);
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('"a,b"', '$.split_part(",", 2147483648)');
ERROR:  field position of jsonpath item method .split_part() is out of range for type integer
select jsonb_path_query('"a,b"', '$.split_part(",", 2147483648)', silent => true);
 jsonb_path_query 
------------------
(0 rows)

-- Test string methods play nicely together
select jsonb_path_query('"hello world"', '$.replace("hello","bye").upper()');
 jsonb_path_query 
------------------
 "BYE WORLD"
(1 row)

select jsonb_path_query('"hElLo WorlD"', '$.lower().upper().lower().replace("hello","bye")');
 jsonb_path_query 
------------------
 "bye world"
(1 row)

select jsonb_path_query('"hElLo WorlD"', '$.upper().lower().upper().replace("HELLO", "BYE")');
 jsonb_path_query 
------------------
 "BYE WORLD"
(1 row)

select jsonb_path_query('"hElLo WorlD"', '$.lower().upper().lower().replace("hello","bye") starts with "bye"');
 jsonb_path_query 
------------------
 true
(1 row)

select jsonb_path_query('"   hElLo WorlD "', '$.btrim().lower().upper().lower().replace("hello","bye") starts with "bye"');
 jsonb_path_query 
------------------
 true
(1 row)

-- Test .time()
select jsonb_path_query('null', '$.time()');
ERROR:  jsonpath item method .time() can only be applied to a string
select jsonb_path_query('true', '$.time()');
ERROR:  jsonpath item method .time() can only be applied to a string
select jsonb_path_query('1', '$.time()');
ERROR:  jsonpath item method .time() can only be applied to a string
select jsonb_path_query('[]', '$.time()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.time()');
ERROR:  jsonpath item method .time() can only be applied to a string
select jsonb_path_query('{}', '$.time()');
ERROR:  jsonpath item method .time() can only be applied to a string
select jsonb_path_query('"bogus"', '$.time()');
ERROR:  time format is not recognized: "bogus"
select jsonb '"12:34:56"' @? '$.time()';
 ?column? 
----------
 t
(1 row)

select jsonb_path_query('"12:34:56"', '$.time()');
 jsonb_path_query 
------------------
 "12:34:56"
(1 row)

select jsonb_path_query('"12:34:56"', '$.time().type()');
     jsonb_path_query     
--------------------------
 "time without time zone"
(1 row)

select jsonb_path_query('"2023-08-15"', '$.time()');
ERROR:  time format is not recognized: "2023-08-15"
select jsonb_path_query('"12:34:56 +05:30"', '$.time()');
ERROR:  cannot convert value from timetz to time without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz('"12:34:56 +05:30"', '$.time()'); -- should work
 jsonb_path_query_tz 
---------------------
 "12:34:56"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56"', '$.time()');
 jsonb_path_query 
------------------
 "12:34:56"
(1 row)

select jsonb_path_query('"12:34:56.789"', '$.time(-1)');
ERROR:  syntax error at or near "-" of jsonpath input
LINE 1: select jsonb_path_query('"12:34:56.789"', '$.time(-1)');
                                                  ^
select jsonb_path_query('"12:34:56.789"', '$.time(2.0)');
ERROR:  syntax error at or near "2.0" of jsonpath input
LINE 1: select jsonb_path_query('"12:34:56.789"', '$.time(2.0)');
                                                  ^
select jsonb_path_query('"12:34:56.789"', '$.time(12345678901)');
ERROR:  time precision of jsonpath item method .time() is out of range for type integer
select jsonb_path_query('"12:34:56.789"', '$.time(0)');
 jsonb_path_query 
------------------
 "12:34:57"
(1 row)

select jsonb_path_query('"12:34:56.789"', '$.time(2)');
 jsonb_path_query 
------------------
 "12:34:56.79"
(1 row)

select jsonb_path_query('"12:34:56.789"', '$.time(5)');
 jsonb_path_query 
------------------
 "12:34:56.789"
(1 row)

select jsonb_path_query('"12:34:56.789"', '$.time(10)');
WARNING:  TIME(10) precision reduced to maximum allowed, 6
 jsonb_path_query 
------------------
 "12:34:56.789"
(1 row)

select jsonb_path_query('"12:34:56.789012"', '$.time(8)');
WARNING:  TIME(8) precision reduced to maximum allowed, 6
 jsonb_path_query  
-------------------
 "12:34:56.789012"
(1 row)

-- Test .time_tz()
select jsonb_path_query('null', '$.time_tz()');
ERROR:  jsonpath item method .time_tz() can only be applied to a string
select jsonb_path_query('true', '$.time_tz()');
ERROR:  jsonpath item method .time_tz() can only be applied to a string
select jsonb_path_query('1', '$.time_tz()');
ERROR:  jsonpath item method .time_tz() can only be applied to a string
select jsonb_path_query('[]', '$.time_tz()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.time_tz()');
ERROR:  jsonpath item method .time_tz() can only be applied to a string
select jsonb_path_query('{}', '$.time_tz()');
ERROR:  jsonpath item method .time_tz() can only be applied to a string
select jsonb_path_query('"bogus"', '$.time_tz()');
ERROR:  time_tz format is not recognized: "bogus"
select jsonb '"12:34:56 +05:30"' @? '$.time_tz()';
 ?column? 
----------
 t
(1 row)

select jsonb_path_query('"12:34:56 +05:30"', '$.time_tz()');
 jsonb_path_query 
------------------
 "12:34:56+05:30"
(1 row)

select jsonb_path_query('"12:34:56 +05:30"', '$.time_tz().type()');
   jsonb_path_query    
-----------------------
 "time with time zone"
(1 row)

select jsonb_path_query('"2023-08-15"', '$.time_tz()');
ERROR:  time_tz format is not recognized: "2023-08-15"
select jsonb_path_query('"2023-08-15 12:34:56"', '$.time_tz()');
ERROR:  time_tz format is not recognized: "2023-08-15 12:34:56"
select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(-1)');
ERROR:  syntax error at or near "-" of jsonpath input
LINE 1: select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(...
                                                         ^
select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(2.0)');
ERROR:  syntax error at or near "2.0" of jsonpath input
LINE 1: select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(...
                                                         ^
select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(12345678901)');
ERROR:  time precision of jsonpath item method .time_tz() is out of range for type integer
select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(0)');
 jsonb_path_query 
------------------
 "12:34:57+05:30"
(1 row)

select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(2)');
  jsonb_path_query   
---------------------
 "12:34:56.79+05:30"
(1 row)

select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(5)');
   jsonb_path_query   
----------------------
 "12:34:56.789+05:30"
(1 row)

select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(10)');
WARNING:  TIME(10) WITH TIME ZONE precision reduced to maximum allowed, 6
   jsonb_path_query   
----------------------
 "12:34:56.789+05:30"
(1 row)

select jsonb_path_query('"12:34:56.789012 +05:30"', '$.time_tz(8)');
WARNING:  TIME(8) WITH TIME ZONE precision reduced to maximum allowed, 6
    jsonb_path_query     
-------------------------
 "12:34:56.789012+05:30"
(1 row)

-- Test .timestamp()
select jsonb_path_query('null', '$.timestamp()');
ERROR:  jsonpath item method .timestamp() can only be applied to a string
select jsonb_path_query('true', '$.timestamp()');
ERROR:  jsonpath item method .timestamp() can only be applied to a string
select jsonb_path_query('1', '$.timestamp()');
ERROR:  jsonpath item method .timestamp() can only be applied to a string
select jsonb_path_query('[]', '$.timestamp()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.timestamp()');
ERROR:  jsonpath item method .timestamp() can only be applied to a string
select jsonb_path_query('{}', '$.timestamp()');
ERROR:  jsonpath item method .timestamp() can only be applied to a string
select jsonb_path_query('"bogus"', '$.timestamp()');
ERROR:  timestamp format is not recognized: "bogus"
select jsonb '"2023-08-15 12:34:56"' @? '$.timestamp()';
 ?column? 
----------
 t
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp()');
   jsonb_path_query    
-----------------------
 "2023-08-15T12:34:56"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().type()');
       jsonb_path_query        
-------------------------------
 "timestamp without time zone"
(1 row)

select jsonb_path_query('"2023-08-15"', '$.timestamp()');
   jsonb_path_query    
-----------------------
 "2023-08-15T00:00:00"
(1 row)

select jsonb_path_query('"12:34:56"', '$.timestamp()');
ERROR:  timestamp format is not recognized: "12:34:56"
select jsonb_path_query('"12:34:56 +05:30"', '$.timestamp()');
ERROR:  timestamp format is not recognized: "12:34:56 +05:30"
select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(-1)');
ERROR:  syntax error at or near "-" of jsonpath input
LINE 1: ...ect jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timesta...
                                                             ^
select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(2.0)');
ERROR:  syntax error at or near "2.0" of jsonpath input
LINE 1: ...ect jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timesta...
                                                             ^
select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(12345678901)');
ERROR:  time precision of jsonpath item method .timestamp() is out of range for type integer
select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(0)');
   jsonb_path_query    
-----------------------
 "2023-08-15T12:34:57"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(2)');
     jsonb_path_query     
--------------------------
 "2023-08-15T12:34:56.79"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(5)');
     jsonb_path_query      
---------------------------
 "2023-08-15T12:34:56.789"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(10)');
WARNING:  TIMESTAMP(10) precision reduced to maximum allowed, 6
     jsonb_path_query      
---------------------------
 "2023-08-15T12:34:56.789"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56.789012"', '$.timestamp(8)');
WARNING:  TIMESTAMP(8) precision reduced to maximum allowed, 6
       jsonb_path_query       
------------------------------
 "2023-08-15T12:34:56.789012"
(1 row)

-- Test .timestamp_tz()
select jsonb_path_query('null', '$.timestamp_tz()');
ERROR:  jsonpath item method .timestamp_tz() can only be applied to a string
select jsonb_path_query('true', '$.timestamp_tz()');
ERROR:  jsonpath item method .timestamp_tz() can only be applied to a string
select jsonb_path_query('1', '$.timestamp_tz()');
ERROR:  jsonpath item method .timestamp_tz() can only be applied to a string
select jsonb_path_query('[]', '$.timestamp_tz()');
 jsonb_path_query 
------------------
(0 rows)

select jsonb_path_query('[]', 'strict $.timestamp_tz()');
ERROR:  jsonpath item method .timestamp_tz() can only be applied to a string
select jsonb_path_query('{}', '$.timestamp_tz()');
ERROR:  jsonpath item method .timestamp_tz() can only be applied to a string
select jsonb_path_query('"bogus"', '$.timestamp_tz()');
ERROR:  timestamp_tz format is not recognized: "bogus"
select jsonb '"2023-08-15 12:34:56 +05:30"' @? '$.timestamp_tz()';
 ?column? 
----------
 t
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()');
      jsonb_path_query       
-----------------------------
 "2023-08-15T12:34:56+05:30"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz().type()');
      jsonb_path_query      
----------------------------
 "timestamp with time zone"
(1 row)

select jsonb_path_query('"2023-08-15"', '$.timestamp_tz()');
ERROR:  cannot convert value from date to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15"', '$.timestamp_tz()'); -- should work
     jsonb_path_query_tz     
-----------------------------
 "2023-08-15T00:00:00-07:00"
(1 row)

select jsonb_path_query('"12:34:56"', '$.timestamp_tz()');
ERROR:  timestamp_tz format is not recognized: "12:34:56"
select jsonb_path_query('"12:34:56 +05:30"', '$.timestamp_tz()');
ERROR:  timestamp_tz format is not recognized: "12:34:56 +05:30"
select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(-1)');
ERROR:  syntax error at or near "-" of jsonpath input
LINE 1: ...nb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timesta...
                                                             ^
select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(2.0)');
ERROR:  syntax error at or near "2.0" of jsonpath input
LINE 1: ...nb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timesta...
                                                             ^
select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(12345678901)');
ERROR:  time precision of jsonpath item method .timestamp_tz() is out of range for type integer
select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(0)');
      jsonb_path_query       
-----------------------------
 "2023-08-15T12:34:57+05:30"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(2)');
        jsonb_path_query        
--------------------------------
 "2023-08-15T12:34:56.79+05:30"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(5)');
        jsonb_path_query         
---------------------------------
 "2023-08-15T12:34:56.789+05:30"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(10)');
WARNING:  TIMESTAMP(10) WITH TIME ZONE precision reduced to maximum allowed, 6
        jsonb_path_query         
---------------------------------
 "2023-08-15T12:34:56.789+05:30"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56.789012 +05:30"', '$.timestamp_tz(8)');
WARNING:  TIMESTAMP(8) WITH TIME ZONE precision reduced to maximum allowed, 6
          jsonb_path_query          
------------------------------------
 "2023-08-15T12:34:56.789012+05:30"
(1 row)

set time zone '+00';
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()');
ERROR:  cannot convert value from timestamptz to time without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.time()'); -- should work
 jsonb_path_query_tz 
---------------------
 "07:04:56"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()');
 jsonb_path_query 
------------------
 "07:04:56+00:00"
(1 row)

select jsonb_path_query('"12:34:56"', '$.time_tz()');
ERROR:  cannot convert value from time to timetz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz('"12:34:56"', '$.time_tz()'); -- should work
 jsonb_path_query_tz 
---------------------
 "12:34:56+00:00"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()');
ERROR:  cannot convert value from timestamptz to timestamp without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); -- should work
  jsonb_path_query_tz  
-----------------------
 "2023-08-15T07:04:56"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz()');
ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()'); -- should work
     jsonb_path_query_tz     
-----------------------------
 "2023-08-15T12:34:56+00:00"
(1 row)

select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")');
   jsonb_path_query    
-----------------------
 "2017-03-10T12:34:00"
(1 row)

select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH")');
ERROR:  input string is too short for datetime format
select jsonb_path_query('"10-03-2017 12:34 +05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")');
      jsonb_path_query       
-----------------------------
 "2017-03-10T12:34:00+05:00"
(1 row)

select jsonb_path_query('"10-03-2017 12:34 -05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")');
      jsonb_path_query       
-----------------------------
 "2017-03-10T12:34:00-05:00"
(1 row)

select jsonb_path_query('"10-03-2017 12:34 +05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")');
      jsonb_path_query       
-----------------------------
 "2017-03-10T12:34:00+05:20"
(1 row)

select jsonb_path_query('"10-03-2017 12:34 -05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")');
      jsonb_path_query       
-----------------------------
 "2017-03-10T12:34:00-05:20"
(1 row)

select jsonb_path_query('"12:34"', '$.datetime("HH24:MI")');
 jsonb_path_query 
------------------
 "12:34:00"
(1 row)

select jsonb_path_query('"12:34"', '$.datetime("HH24:MI TZH")');
ERROR:  input string is too short for datetime format
select jsonb_path_query('"12:34 +05"', '$.datetime("HH24:MI TZH")');
 jsonb_path_query 
------------------
 "12:34:00+05:00"
(1 row)

select jsonb_path_query('"12:34 -05"', '$.datetime("HH24:MI TZH")');
 jsonb_path_query 
------------------
 "12:34:00-05:00"
(1 row)

select jsonb_path_query('"12:34 +05:20"', '$.datetime("HH24:MI TZH:TZM")');
 jsonb_path_query 
------------------
 "12:34:00+05:20"
(1 row)

select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")');
 jsonb_path_query 
------------------
 "12:34:00-05:20"
(1 row)

set time zone '+10';
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()');
ERROR:  cannot convert value from timestamptz to time without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.time()'); -- should work
 jsonb_path_query_tz 
---------------------
 "17:04:56"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()');
 jsonb_path_query 
------------------
 "17:04:56+10:00"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()');
ERROR:  cannot convert value from timestamptz to timestamp without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); -- should work
  jsonb_path_query_tz  
-----------------------
 "2023-08-15T17:04:56"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz()');
ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()'); -- should work
     jsonb_path_query_tz     
-----------------------------
 "2023-08-15T12:34:56+10:00"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()');
      jsonb_path_query       
-----------------------------
 "2023-08-15T12:34:56+05:30"
(1 row)

select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")');
   jsonb_path_query    
-----------------------
 "2017-03-10T12:34:00"
(1 row)

select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH")');
ERROR:  input string is too short for datetime format
select jsonb_path_query('"10-03-2017 12:34 +05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")');
      jsonb_path_query       
-----------------------------
 "2017-03-10T12:34:00+05:00"
(1 row)

select jsonb_path_query('"10-03-2017 12:34 -05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")');
      jsonb_path_query       
-----------------------------
 "2017-03-10T12:34:00-05:00"
(1 row)

select jsonb_path_query('"10-03-2017 12:34 +05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")');
      jsonb_path_query       
-----------------------------
 "2017-03-10T12:34:00+05:20"
(1 row)

select jsonb_path_query('"10-03-2017 12:34 -05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")');
      jsonb_path_query       
-----------------------------
 "2017-03-10T12:34:00-05:20"
(1 row)

select jsonb_path_query('"12:34"', '$.datetime("HH24:MI")');
 jsonb_path_query 
------------------
 "12:34:00"
(1 row)

select jsonb_path_query('"12:34"', '$.datetime("HH24:MI TZH")');
ERROR:  input string is too short for datetime format
select jsonb_path_query('"12:34 +05"', '$.datetime("HH24:MI TZH")');
 jsonb_path_query 
------------------
 "12:34:00+05:00"
(1 row)

select jsonb_path_query('"12:34 -05"', '$.datetime("HH24:MI TZH")');
 jsonb_path_query 
------------------
 "12:34:00-05:00"
(1 row)

select jsonb_path_query('"12:34 +05:20"', '$.datetime("HH24:MI TZH:TZM")');
 jsonb_path_query 
------------------
 "12:34:00+05:20"
(1 row)

select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")');
 jsonb_path_query 
------------------
 "12:34:00-05:20"
(1 row)

set time zone default;
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()');
ERROR:  cannot convert value from timestamptz to time without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.time()'); -- should work
 jsonb_path_query_tz 
---------------------
 "00:04:56"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()');
 jsonb_path_query 
------------------
 "00:04:56-07:00"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()');
ERROR:  cannot convert value from timestamptz to timestamp without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); -- should work
  jsonb_path_query_tz  
-----------------------
 "2023-08-15T00:04:56"
(1 row)

select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()');
      jsonb_path_query       
-----------------------------
 "2023-08-15T12:34:56+05:30"
(1 row)

select jsonb_path_query('"2017-03-10"', '$.datetime().type()');
 jsonb_path_query 
------------------
 "date"
(1 row)

select jsonb_path_query('"2017-03-10"', '$.datetime()');
 jsonb_path_query 
------------------
 "2017-03-10"
(1 row)

select jsonb_path_query('"2017-03-10 12:34:56"', '$.datetime().type()');
       jsonb_path_query        
-------------------------------
 "timestamp without time zone"
(1 row)

select jsonb_path_query('"2017-03-10 12:34:56"', '$.datetime()');
   jsonb_path_query    
-----------------------
 "2017-03-10T12:34:56"
(1 row)

select jsonb_path_query('"2017-03-10 12:34:56+3"', '$.datetime().type()');
      jsonb_path_query      
----------------------------
 "timestamp with time zone"
(1 row)

select jsonb_path_query('"2017-03-10 12:34:56+3"', '$.datetime()');
      jsonb_path_query       
-----------------------------
 "2017-03-10T12:34:56+03:00"
(1 row)

select jsonb_path_query('"2017-03-10 12:34:56+3:10"', '$.datetime().type()');
      jsonb_path_query      
----------------------------
 "timestamp with time zone"
(1 row)

select jsonb_path_query('"2017-03-10 12:34:56+3:10"', '$.datetime()');
      jsonb_path_query       
-----------------------------
 "2017-03-10T12:34:56+03:10"
(1 row)

select jsonb_path_query('"2017-03-10T12:34:56+3:10"', '$.datetime()');
      jsonb_path_query       
-----------------------------
 "2017-03-10T12:34:56+03:10"
(1 row)

select jsonb_path_query('"2017-03-10t12:34:56+3:10"', '$.datetime()');
ERROR:  datetime format is not recognized: "2017-03-10t12:34:56+3:10"
HINT:  Use a datetime template argument to specify the input data format.
select jsonb_path_query('"2017-03-10 12:34:56.789+3:10"', '$.datetime()');
        jsonb_path_query         
---------------------------------
 "2017-03-10T12:34:56.789+03:10"
(1 row)

select jsonb_path_query('"2017-03-10T12:34:56.789+3:10"', '$.datetime()');
        jsonb_path_query         
---------------------------------
 "2017-03-10T12:34:56.789+03:10"
(1 row)

select jsonb_path_query('"2017-03-10t12:34:56.789+3:10"', '$.datetime()');
ERROR:  datetime format is not recognized: "2017-03-10t12:34:56.789+3:10"
HINT:  Use a datetime template argument to specify the input data format.
select jsonb_path_query('"2017-03-10T12:34:56.789EST"', '$.datetime()');
        jsonb_path_query         
---------------------------------
 "2017-03-10T12:34:56.789-05:00"
(1 row)

select jsonb_path_query('"2017-03-10T12:34:56.789Z"', '$.datetime()');
        jsonb_path_query         
---------------------------------
 "2017-03-10T12:34:56.789+00:00"
(1 row)

select jsonb_path_query('"12:34:56"', '$.datetime().type()');
     jsonb_path_query     
--------------------------
 "time without time zone"
(1 row)

select jsonb_path_query('"12:34:56"', '$.datetime()');
 jsonb_path_query 
------------------
 "12:34:56"
(1 row)

select jsonb_path_query('"12:34:56+3"', '$.datetime().type()');
   jsonb_path_query    
-----------------------
 "time with time zone"
(1 row)

select jsonb_path_query('"12:34:56+3"', '$.datetime()');
 jsonb_path_query 
------------------
 "12:34:56+03:00"
(1 row)

select jsonb_path_query('"12:34:56+3:10"', '$.datetime().type()');
   jsonb_path_query    
-----------------------
 "time with time zone"
(1 row)

select jsonb_path_query('"12:34:56+3:10"', '$.datetime()');
 jsonb_path_query 
------------------
 "12:34:56+03:10"
(1 row)

set time zone '+00';
-- date comparison
select jsonb_path_query(
	'["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"]',
	'$[*].datetime() ? (@ == "10.03.2017".datetime("dd.mm.yyyy"))');
ERROR:  cannot convert value from date to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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"]',
	'$[*].datetime() ? (@ >= "10.03.2017".datetime("dd.mm.yyyy"))');
ERROR:  cannot convert value from date to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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"]',
	'$[*].datetime() ? (@ <  "10.03.2017".datetime("dd.mm.yyyy"))');
ERROR:  cannot convert value from date to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ == "10.03.2017".datetime("dd.mm.yyyy"))');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10"
 "2017-03-10T00:00:00"
 "2017-03-10T03:00:00+03:00"
(3 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ >= "10.03.2017".datetime("dd.mm.yyyy"))');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10"
 "2017-03-11"
 "2017-03-10T00:00:00"
 "2017-03-10T12:34:56"
 "2017-03-10T03:00:00+03:00"
(5 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ <  "10.03.2017".datetime("dd.mm.yyyy"))');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-09"
 "2017-03-10T01:02:03+04:00"
(2 rows)

select jsonb_path_query_tz(
	'["2017-03-10", "2017-03-11", "2017-03-09", "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"]',
	'$[*].datetime() ? (@ == "2017-03-10".date())');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10"
 "2017-03-10T00:00:00"
 "2017-03-10T03:00:00+03:00"
(3 rows)

select jsonb_path_query_tz(
	'["2017-03-10", "2017-03-11", "2017-03-09", "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"]',
	'$[*].datetime() ? (@ >= "2017-03-10".date())');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10"
 "2017-03-11"
 "2017-03-10T00:00:00"
 "2017-03-10T12:34:56"
 "2017-03-10T03:00:00+03:00"
(5 rows)

select jsonb_path_query_tz(
	'["2017-03-10", "2017-03-11", "2017-03-09", "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"]',
	'$[*].datetime() ? (@ <  "2017-03-10".date())');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-09"
 "2017-03-10T01:02:03+04:00"
(2 rows)

select jsonb_path_query(
	'["2017-03-10", "2017-03-11", "2017-03-09", "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"]',
	'$[*].date() ? (@ == "2017-03-10".date())');
ERROR:  cannot convert value from timestamptz to date without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["2017-03-10", "2017-03-11", "2017-03-09", "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"]',
	'$[*].date() ? (@ >= "2017-03-10".date())');
ERROR:  cannot convert value from timestamptz to date without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["2017-03-10", "2017-03-11", "2017-03-09", "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"]',
	'$[*].date() ? (@ <  "2017-03-10".date())');
ERROR:  cannot convert value from timestamptz to date without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz(
	'["2017-03-10", "2017-03-11", "2017-03-09", "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"]',
	'$[*].date() ? (@ == "2017-03-10".date())');
 jsonb_path_query_tz 
---------------------
 "2017-03-10"
 "2017-03-10"
 "2017-03-10"
 "2017-03-10"
(4 rows)

select jsonb_path_query_tz(
	'["2017-03-10", "2017-03-11", "2017-03-09", "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"]',
	'$[*].date() ? (@ >= "2017-03-10".date())');
 jsonb_path_query_tz 
---------------------
 "2017-03-10"
 "2017-03-11"
 "2017-03-10"
 "2017-03-10"
 "2017-03-10"
(5 rows)

select jsonb_path_query_tz(
	'["2017-03-10", "2017-03-11", "2017-03-09", "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"]',
	'$[*].date() ? (@ <  "2017-03-10".date())');
 jsonb_path_query_tz 
---------------------
 "2017-03-09"
 "2017-03-09"
(2 rows)

-- time comparison
select jsonb_path_query(
	'["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"]',
	'$[*].datetime() ? (@ == "12:35".datetime("HH24:MI"))');
ERROR:  cannot convert value from time to timetz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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"]',
	'$[*].datetime() ? (@ >= "12:35".datetime("HH24:MI"))');
ERROR:  cannot convert value from time to timetz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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"]',
	'$[*].datetime() ? (@ <  "12:35".datetime("HH24:MI"))');
ERROR:  cannot convert value from time to timetz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ == "12:35".datetime("HH24:MI"))');
 jsonb_path_query_tz 
---------------------
 "12:35:00"
 "12:35:00+00:00"
(2 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ >= "12:35".datetime("HH24:MI"))');
 jsonb_path_query_tz 
---------------------
 "12:35:00"
 "12:36:00"
 "12:35:00+00:00"
(3 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ <  "12:35".datetime("HH24:MI"))');
 jsonb_path_query_tz 
---------------------
 "12:34:00"
 "12:35:00+01:00"
 "13:35:00+01:00"
(3 rows)

select jsonb_path_query_tz(
	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
	'$[*].datetime() ? (@ == "12:35:00".time())');
 jsonb_path_query_tz 
---------------------
 "12:35:00"
 "12:35:00+00:00"
(2 rows)

select jsonb_path_query_tz(
	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
	'$[*].datetime() ? (@ >= "12:35:00".time())');
 jsonb_path_query_tz 
---------------------
 "12:35:00"
 "12:36:00"
 "12:35:00+00:00"
(3 rows)

select jsonb_path_query_tz(
	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
	'$[*].datetime() ? (@ <  "12:35:00".time())');
 jsonb_path_query_tz 
---------------------
 "12:34:00"
 "12:35:00+01:00"
 "13:35:00+01:00"
(3 rows)

select jsonb_path_query(
	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
	'$[*].time() ? (@ == "12:35:00".time())');
ERROR:  cannot convert value from timetz to time without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
	'$[*].time() ? (@ >= "12:35:00".time())');
ERROR:  cannot convert value from timetz to time without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
	'$[*].time() ? (@ <  "12:35:00".time())');
ERROR:  cannot convert value from timetz to time without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["12:34:00.123", "12:35:00.123", "12:36:00.1123", "12:35:00.1123+00", "12:35:00.123+01", "13:35:00.123+01", "2017-03-10 12:35:00.1", "2017-03-10 12:35:00.123+01"]',
	'$[*].time(2) ? (@ >= "12:35:00.123".time(2))');
ERROR:  cannot convert value from timetz to time without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz(
	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
	'$[*].time() ? (@ == "12:35:00".time())');
 jsonb_path_query_tz 
---------------------
 "12:35:00"
 "12:35:00"
 "12:35:00"
 "12:35:00"
(4 rows)

select jsonb_path_query_tz(
	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
	'$[*].time() ? (@ >= "12:35:00".time())');
 jsonb_path_query_tz 
---------------------
 "12:35:00"
 "12:36:00"
 "12:35:00"
 "12:35:00"
 "13:35:00"
 "12:35:00"
(6 rows)

select jsonb_path_query_tz(
	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
	'$[*].time() ? (@ <  "12:35:00".time())');
 jsonb_path_query_tz 
---------------------
 "12:34:00"
 "11:35:00"
(2 rows)

select jsonb_path_query_tz(
	'["12:34:00.123", "12:35:00.123", "12:36:00.1123", "12:35:00.1123+00", "12:35:00.123+01", "13:35:00.123+01", "2017-03-10 12:35:00.1", "2017-03-10 12:35:00.123+01"]',
	'$[*].time(2) ? (@ >= "12:35:00.123".time(2))');
 jsonb_path_query_tz 
---------------------
 "12:35:00.12"
 "12:36:00.11"
 "12:35:00.12"
 "13:35:00.12"
(4 rows)

-- timetz comparison
select jsonb_path_query(
	'["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"]',
	'$[*].datetime() ? (@ == "12:35 +1".datetime("HH24:MI TZH"))');
ERROR:  cannot convert value from time to timetz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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"]',
	'$[*].datetime() ? (@ >= "12:35 +1".datetime("HH24:MI TZH"))');
ERROR:  cannot convert value from time to timetz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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"]',
	'$[*].datetime() ? (@ <  "12:35 +1".datetime("HH24:MI TZH"))');
ERROR:  cannot convert value from time to timetz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ == "12:35 +1".datetime("HH24:MI TZH"))');
 jsonb_path_query_tz 
---------------------
 "12:35:00+01:00"
(1 row)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ >= "12:35 +1".datetime("HH24:MI TZH"))');
 jsonb_path_query_tz 
---------------------
 "12:35:00+01:00"
 "12:36:00+01:00"
 "12:35:00-02:00"
 "11:35:00"
 "12:35:00"
(5 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ <  "12:35 +1".datetime("HH24:MI TZH"))');
 jsonb_path_query_tz 
---------------------
 "12:34:00+01:00"
 "12:35:00+02:00"
 "10:35:00"
(3 rows)

select jsonb_path_query_tz(
	'["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 12:35:00 +1"]',
	'$[*].datetime() ? (@ == "12:35:00 +1".time_tz())');
 jsonb_path_query_tz 
---------------------
 "12:35:00+01:00"
(1 row)

select jsonb_path_query_tz(
	'["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 12:35:00 +1"]',
	'$[*].datetime() ? (@ >= "12:35:00 +1".time_tz())');
 jsonb_path_query_tz 
---------------------
 "12:35:00+01:00"
 "12:36:00+01:00"
 "12:35:00-02:00"
 "11:35:00"
 "12:35:00"
(5 rows)

select jsonb_path_query_tz(
	'["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 12:35:00 +1"]',
	'$[*].datetime() ? (@ <  "12:35:00 +1".time_tz())');
 jsonb_path_query_tz 
---------------------
 "12:34:00+01:00"
 "12:35:00+02:00"
 "10:35:00"
(3 rows)

select jsonb_path_query(
	'["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 12:35:00 +1"]',
	'$[*].time_tz() ? (@ == "12:35:00 +1".time_tz())');
ERROR:  cannot convert value from time to timetz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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 12:35:00 +1"]',
	'$[*].time_tz() ? (@ >= "12:35:00 +1".time_tz())');
ERROR:  cannot convert value from time to timetz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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 12:35:00 +1"]',
	'$[*].time_tz() ? (@ <  "12:35:00 +1".time_tz())');
ERROR:  cannot convert value from time to timetz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["12:34:00.123+01", "12:35:00.123+01", "12:36:00.1123+01", "12:35:00.1123+02", "12:35:00.123-02", "10:35:00.123", "11:35:00.1", "12:35:00.123", "2017-03-10 12:35:00.123 +1"]',
	'$[*].time_tz(2) ? (@ >= "12:35:00.123 +1".time_tz(2))');
ERROR:  cannot convert value from time to timetz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz(
	'["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 12:35:00 +1"]',
	'$[*].time_tz() ? (@ == "12:35:00 +1".time_tz())');
 jsonb_path_query_tz 
---------------------
 "12:35:00+01:00"
(1 row)

select jsonb_path_query_tz(
	'["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 12:35:00 +1"]',
	'$[*].time_tz() ? (@ >= "12:35:00 +1".time_tz())');
 jsonb_path_query_tz 
---------------------
 "12:35:00+01:00"
 "12:36:00+01:00"
 "12:35:00-02:00"
 "11:35:00+00:00"
 "12:35:00+00:00"
 "11:35:00+00:00"
(6 rows)

select jsonb_path_query_tz(
	'["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 12:35:00 +1"]',
	'$[*].time_tz() ? (@ <  "12:35:00 +1".time_tz())');
 jsonb_path_query_tz 
---------------------
 "12:34:00+01:00"
 "12:35:00+02:00"
 "10:35:00+00:00"
(3 rows)

select jsonb_path_query_tz(
	'["12:34:00.123+01", "12:35:00.123+01", "12:36:00.1123+01", "12:35:00.1123+02", "12:35:00.123-02", "10:35:00.123", "11:35:00.1", "12:35:00.123", "2017-03-10 12:35:00.123 +1"]',
	'$[*].time_tz(2) ? (@ >= "12:35:00.123 +1".time_tz(2))');
 jsonb_path_query_tz 
---------------------
 "12:35:00.12+01:00"
 "12:36:00.11+01:00"
 "12:35:00.12-02:00"
 "12:35:00.12+00:00"
 "11:35:00.12+00:00"
(5 rows)

-- timestamp comparison
select jsonb_path_query(
	'["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"]',
	'$[*].datetime() ? (@ == "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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"]',
	'$[*].datetime() ? (@ >= "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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"]',
	'$[*].datetime() ? (@ < "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ == "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10T12:35:00"
 "2017-03-10T13:35:00+01:00"
(2 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ >= "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10T12:35:00"
 "2017-03-10T12:36:00"
 "2017-03-10T13:35:00+01:00"
 "2017-03-10T12:35:00-01:00"
 "2017-03-11"
(5 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ < "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10T12:34:00"
 "2017-03-10T12:35:00+01:00"
 "2017-03-10"
(3 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ == "2017-03-10 12:35:00".timestamp())');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10T12:35:00"
 "2017-03-10T13:35:00+01:00"
(2 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ >= "2017-03-10 12:35:00".timestamp())');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10T12:35:00"
 "2017-03-10T12:36:00"
 "2017-03-10T13:35:00+01:00"
 "2017-03-10T12:35:00-01:00"
 "2017-03-11"
(5 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ < "2017-03-10 12:35:00".timestamp())');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10T12:34:00"
 "2017-03-10T12:35:00+01:00"
 "2017-03-10"
(3 rows)

select jsonb_path_query(
	'["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"]',
	'$[*].timestamp() ? (@ == "2017-03-10 12:35:00".timestamp())');
ERROR:  cannot convert value from timestamptz to timestamp without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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"]',
	'$[*].timestamp() ? (@ >= "2017-03-10 12:35:00".timestamp())');
ERROR:  cannot convert value from timestamptz to timestamp without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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"]',
	'$[*].timestamp() ? (@ < "2017-03-10 12:35:00".timestamp())');
ERROR:  cannot convert value from timestamptz to timestamp without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["2017-03-10 12:34:00.123", "2017-03-10 12:35:00.123", "2017-03-10 12:36:00.1123", "2017-03-10 12:35:00.1123+01", "2017-03-10 13:35:00.123+01", "2017-03-10 12:35:00.1-01", "2017-03-10", "2017-03-11"]',
	'$[*].timestamp(2) ? (@ >= "2017-03-10 12:35:00.123".timestamp(2))');
ERROR:  cannot convert value from timestamptz to timestamp without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz(
	'["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"]',
	'$[*].timestamp() ? (@ == "2017-03-10 12:35:00".timestamp())');
  jsonb_path_query_tz  
-----------------------
 "2017-03-10T12:35:00"
 "2017-03-10T12:35:00"
(2 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].timestamp() ? (@ >= "2017-03-10 12:35:00".timestamp())');
  jsonb_path_query_tz  
-----------------------
 "2017-03-10T12:35:00"
 "2017-03-10T12:36:00"
 "2017-03-10T12:35:00"
 "2017-03-10T13:35:00"
 "2017-03-11T00:00:00"
(5 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].timestamp() ? (@ < "2017-03-10 12:35:00".timestamp())');
  jsonb_path_query_tz  
-----------------------
 "2017-03-10T12:34:00"
 "2017-03-10T11:35:00"
 "2017-03-10T00:00:00"
(3 rows)

select jsonb_path_query_tz(
	'["2017-03-10 12:34:00.123", "2017-03-10 12:35:00.123", "2017-03-10 12:36:00.1123", "2017-03-10 12:35:00.1123+01", "2017-03-10 13:35:00.123+01", "2017-03-10 12:35:00.1-01", "2017-03-10", "2017-03-11"]',
	'$[*].timestamp(2) ? (@ >= "2017-03-10 12:35:00.123".timestamp(2))');
   jsonb_path_query_tz    
--------------------------
 "2017-03-10T12:35:00.12"
 "2017-03-10T12:36:00.11"
 "2017-03-10T12:35:00.12"
 "2017-03-10T13:35:00.1"
 "2017-03-11T00:00:00"
(5 rows)

-- timestamptz comparison
select jsonb_path_query(
	'["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"]',
	'$[*].datetime() ? (@ == "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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"]',
	'$[*].datetime() ? (@ >= "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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"]',
	'$[*].datetime() ? (@ < "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ == "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10T12:35:00+01:00"
 "2017-03-10T11:35:00"
(2 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ >= "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10T12:35:00+01:00"
 "2017-03-10T12:36:00+01:00"
 "2017-03-10T12:35:00-02:00"
 "2017-03-10T11:35:00"
 "2017-03-10T12:35:00"
 "2017-03-11"
(6 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ < "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10T12:34:00+01:00"
 "2017-03-10T12:35:00+02:00"
 "2017-03-10T10:35:00"
 "2017-03-10"
(4 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ == "2017-03-10 12:35:00 +1".timestamp_tz())');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10T12:35:00+01:00"
 "2017-03-10T11:35:00"
(2 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ >= "2017-03-10 12:35:00 +1".timestamp_tz())');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10T12:35:00+01:00"
 "2017-03-10T12:36:00+01:00"
 "2017-03-10T12:35:00-02:00"
 "2017-03-10T11:35:00"
 "2017-03-10T12:35:00"
 "2017-03-11"
(6 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].datetime() ? (@ < "2017-03-10 12:35:00 +1".timestamp_tz())');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10T12:34:00+01:00"
 "2017-03-10T12:35:00+02:00"
 "2017-03-10T10:35:00"
 "2017-03-10"
(4 rows)

select jsonb_path_query(
	'["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"]',
	'$[*].timestamp_tz() ? (@ == "2017-03-10 12:35:00 +1".timestamp_tz())');
ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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"]',
	'$[*].timestamp_tz() ? (@ >= "2017-03-10 12:35:00 +1".timestamp_tz())');
ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["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"]',
	'$[*].timestamp_tz() ? (@ < "2017-03-10 12:35:00 +1".timestamp_tz())');
ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query(
	'["2017-03-10 12:34:00.123+01", "2017-03-10 12:35:00.123+01", "2017-03-10 12:36:00.1123+01", "2017-03-10 12:35:00.1123+02", "2017-03-10 12:35:00.123-02", "2017-03-10 10:35:00.123", "2017-03-10 11:35:00.1", "2017-03-10 12:35:00.123", "2017-03-10", "2017-03-11"]',
	'$[*].timestamp_tz(2) ? (@ >= "2017-03-10 12:35:00.123 +1".timestamp_tz(2))');
ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.
select jsonb_path_query_tz(
	'["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"]',
	'$[*].timestamp_tz() ? (@ == "2017-03-10 12:35:00 +1".timestamp_tz())');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10T12:35:00+01:00"
 "2017-03-10T11:35:00+00:00"
(2 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].timestamp_tz() ? (@ >= "2017-03-10 12:35:00 +1".timestamp_tz())');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10T12:35:00+01:00"
 "2017-03-10T12:36:00+01:00"
 "2017-03-10T12:35:00-02:00"
 "2017-03-10T11:35:00+00:00"
 "2017-03-10T12:35:00+00:00"
 "2017-03-11T00:00:00+00:00"
(6 rows)

select jsonb_path_query_tz(
	'["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"]',
	'$[*].timestamp_tz() ? (@ < "2017-03-10 12:35:00 +1".timestamp_tz())');
     jsonb_path_query_tz     
-----------------------------
 "2017-03-10T12:34:00+01:00"
 "2017-03-10T12:35:00+02:00"
 "2017-03-10T10:35:00+00:00"
 "2017-03-10T00:00:00+00:00"
(4 rows)

select jsonb_path_query_tz(
	'["2017-03-10 12:34:00.123+01", "2017-03-10 12:35:00.123+01", "2017-03-10 12:36:00.1123+01", "2017-03-10 12:35:00.1123+02", "2017-03-10 12:35:00.123-02", "2017-03-10 10:35:00.123", "2017-03-10 11:35:00.1", "2017-03-10 12:35:00.123", "2017-03-10", "2017-03-11"]',
	'$[*].timestamp_tz(2) ? (@ >= "2017-03-10 12:35:00.123 +1".timestamp_tz(2))');
      jsonb_path_query_tz       
--------------------------------
 "2017-03-10T12:35:00.12+01:00"
 "2017-03-10T12:36:00.11+01:00"
 "2017-03-10T12:35:00.12-02:00"
 "2017-03-10T12:35:00.12+00:00"
 "2017-03-11T00:00:00+00:00"
(5 rows)

-- overflow during comparison
select jsonb_path_query('"1000000-01-01"', '$.datetime() > "2020-01-01 12:00:00".datetime()'::jsonpath);
 jsonb_path_query 
------------------
 true
(1 row)

set time zone default;
-- jsonpath operators
SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*]');
 jsonb_path_query 
------------------
 {"a": 1}
 {"a": 2}
(2 rows)

SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*] ? (@.a > 10)');
 jsonb_path_query 
------------------
(0 rows)

SELECT jsonb_path_query('[{"a": 1}]', '$undefined_var');
ERROR:  could not find jsonpath variable "undefined_var"
SELECT jsonb_path_query('[{"a": 1}]', 'false');
 jsonb_path_query 
------------------
 false
(1 row)

SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a');
ERROR:  JSON object does not contain key "a"
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a');
 jsonb_path_query_array 
------------------------
 [1, 2]
(1 row)

SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ == 1)');
 jsonb_path_query_array 
------------------------
 [1]
(1 row)

SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 10)');
 jsonb_path_query_array 
------------------------
 []
(1 row)

SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 1, "max": 4}');
 jsonb_path_query_array 
------------------------
 [2, 3]
(1 row)

SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 3, "max": 4}');
 jsonb_path_query_array 
------------------------
 []
(1 row)

SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a');
ERROR:  JSON object does not contain key "a"
SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a', silent => true);
 jsonb_path_query_first 
------------------------
 1
(1 row)

SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a');
 jsonb_path_query_first 
------------------------
 1
(1 row)

SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ == 1)');
 jsonb_path_query_first 
------------------------
 1
(1 row)

SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 10)');
 jsonb_path_query_first 
------------------------
 
(1 row)

SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 1, "max": 4}');
 jsonb_path_query_first 
------------------------
 2
(1 row)

SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 3, "max": 4}');
 jsonb_path_query_first 
------------------------
 
(1 row)

SELECT jsonb_path_query_first('[{"a": 1}]', '$undefined_var');
ERROR:  could not find jsonpath variable "undefined_var"
SELECT jsonb_path_query_first('[{"a": 1}]', 'false');
 jsonb_path_query_first 
------------------------
 false
(1 row)

SELECT jsonb '[{"a": 1}, {"a": 2}]' @? '$[*].a ? (@ > 1)';
 ?column? 
----------
 t
(1 row)

SELECT jsonb '[{"a": 1}, {"a": 2}]' @? '$[*] ? (@.a > 2)';
 ?column? 
----------
 f
(1 row)

SELECT jsonb_path_exists('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 1)');
 jsonb_path_exists 
-------------------
 t
(1 row)

SELECT jsonb_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.a > $min && @.a < $max)', vars => '{"min": 1, "max": 4}');
 jsonb_path_exists 
-------------------
 t
(1 row)

SELECT jsonb_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.a > $min && @.a < $max)', vars => '{"min": 3, "max": 4}');
 jsonb_path_exists 
-------------------
 f
(1 row)

SELECT jsonb_path_exists('[{"a": 1}]', '$undefined_var');
ERROR:  could not find jsonpath variable "undefined_var"
SELECT jsonb_path_exists('[{"a": 1}]', 'false');
 jsonb_path_exists 
-------------------
 t
(1 row)

SELECT jsonb_path_match('true', '$', silent => false);
 jsonb_path_match 
------------------
 t
(1 row)

SELECT jsonb_path_match('false', '$', silent => false);
 jsonb_path_match 
------------------
 f
(1 row)

SELECT jsonb_path_match('null', '$', silent => false);
 jsonb_path_match 
------------------
 
(1 row)

SELECT jsonb_path_match('1', '$', silent => true);
 jsonb_path_match 
------------------
 
(1 row)

SELECT jsonb_path_match('1', '$', silent => false);
ERROR:  single boolean result is expected
SELECT jsonb_path_match('"a"', '$', silent => false);
ERROR:  single boolean result is expected
SELECT jsonb_path_match('{}', '$', silent => false);
ERROR:  single boolean result is expected
SELECT jsonb_path_match('[true]', '$', silent => false);
ERROR:  single boolean result is expected
SELECT jsonb_path_match('{}', 'lax $.a', silent => false);
ERROR:  single boolean result is expected
SELECT jsonb_path_match('{}', 'strict $.a', silent => false);
ERROR:  JSON object does not contain key "a"
SELECT jsonb_path_match('{}', 'strict $.a', silent => true);
 jsonb_path_match 
------------------
 
(1 row)

SELECT jsonb_path_match('[true, true]', '$[*]', silent => false);
ERROR:  single boolean result is expected
SELECT jsonb '[{"a": 1}, {"a": 2}]' @@ '$[*].a > 1';
 ?column? 
----------
 t
(1 row)

SELECT jsonb '[{"a": 1}, {"a": 2}]' @@ '$[*].a > 2';
 ?column? 
----------
 f
(1 row)

SELECT jsonb_path_match('[{"a": 1}, {"a": 2}]', '$[*].a > 1');
 jsonb_path_match 
------------------
 t
(1 row)

SELECT jsonb_path_match('[{"a": 1}]', '$undefined_var');
ERROR:  could not find jsonpath variable "undefined_var"
SELECT jsonb_path_match('[{"a": 1}]', 'false');
 jsonb_path_match 
------------------
 f
(1 row)

-- test string comparison (Unicode codepoint collation)
WITH str(j, num) AS
(
	SELECT jsonb_build_object('s', s), num
	FROM unnest('{"", "a", "ab", "abc", "abcd", "b", "A", "AB", "ABC", "ABc", "ABcD", "B"}'::text[]) WITH ORDINALITY AS a(s, num)
)
SELECT
	s1.j, s2.j,
	jsonb_path_query_first(s1.j, '$.s < $s', vars => s2.j) lt,
	jsonb_path_query_first(s1.j, '$.s <= $s', vars => s2.j) le,
	jsonb_path_query_first(s1.j, '$.s == $s', vars => s2.j) eq,
	jsonb_path_query_first(s1.j, '$.s >= $s', vars => s2.j) ge,
	jsonb_path_query_first(s1.j, '$.s > $s', vars => s2.j) gt
FROM str s1, str s2
ORDER BY s1.num, s2.num;
       j       |       j       |  lt   |  le   |  eq   |  ge   |  gt   
---------------+---------------+-------+-------+-------+-------+-------
 {"s": ""}     | {"s": ""}     | false | true  | true  | true  | false
 {"s": ""}     | {"s": "a"}    | true  | true  | false | false | false
 {"s": ""}     | {"s": "ab"}   | true  | true  | false | false | false
 {"s": ""}     | {"s": "abc"}  | true  | true  | false | false | false
 {"s": ""}     | {"s": "abcd"} | true  | true  | false | false | false
 {"s": ""}     | {"s": "b"}    | true  | true  | false | false | false
 {"s": ""}     | {"s": "A"}    | true  | true  | false | false | false
 {"s": ""}     | {"s": "AB"}   | true  | true  | false | false | false
 {"s": ""}     | {"s": "ABC"}  | true  | true  | false | false | false
 {"s": ""}     | {"s": "ABc"}  | true  | true  | false | false | false
 {"s": ""}     | {"s": "ABcD"} | true  | true  | false | false | false
 {"s": ""}     | {"s": "B"}    | true  | true  | false | false | false
 {"s": "a"}    | {"s": ""}     | false | false | false | true  | true
 {"s": "a"}    | {"s": "a"}    | false | true  | true  | true  | false
 {"s": "a"}    | {"s": "ab"}   | true  | true  | false | false | false
 {"s": "a"}    | {"s": "abc"}  | true  | true  | false | false | false
 {"s": "a"}    | {"s": "abcd"} | true  | true  | false | false | false
 {"s": "a"}    | {"s": "b"}    | true  | true  | false | false | false
 {"s": "a"}    | {"s": "A"}    | false | false | false | true  | true
 {"s": "a"}    | {"s": "AB"}   | false | false | false | true  | true
 {"s": "a"}    | {"s": "ABC"}  | false | false | false | true  | true
 {"s": "a"}    | {"s": "ABc"}  | false | false | false | true  | true
 {"s": "a"}    | {"s": "ABcD"} | false | false | false | true  | true
 {"s": "a"}    | {"s": "B"}    | false | false | false | true  | true
 {"s": "ab"}   | {"s": ""}     | false | false | false | true  | true
 {"s": "ab"}   | {"s": "a"}    | false | false | false | true  | true
 {"s": "ab"}   | {"s": "ab"}   | false | true  | true  | true  | false
 {"s": "ab"}   | {"s": "abc"}  | true  | true  | false | false | false
 {"s": "ab"}   | {"s": "abcd"} | true  | true  | false | false | false
 {"s": "ab"}   | {"s": "b"}    | true  | true  | false | false | false
 {"s": "ab"}   | {"s": "A"}    | false | false | false | true  | true
 {"s": "ab"}   | {"s": "AB"}   | false | false | false | true  | true
 {"s": "ab"}   | {"s": "ABC"}  | false | false | false | true  | true
 {"s": "ab"}   | {"s": "ABc"}  | false | false | false | true  | true
 {"s": "ab"}   | {"s": "ABcD"} | false | false | false | true  | true
 {"s": "ab"}   | {"s": "B"}    | false | false | false | true  | true
 {"s": "abc"}  | {"s": ""}     | false | false | false | true  | true
 {"s": "abc"}  | {"s": "a"}    | false | false | false | true  | true
 {"s": "abc"}  | {"s": "ab"}   | false | false | false | true  | true
 {"s": "abc"}  | {"s": "abc"}  | false | true  | true  | true  | false
 {"s": "abc"}  | {"s": "abcd"} | true  | true  | false | false | false
 {"s": "abc"}  | {"s": "b"}    | true  | true  | false | false | false
 {"s": "abc"}  | {"s": "A"}    | false | false | false | true  | true
 {"s": "abc"}  | {"s": "AB"}   | false | false | false | true  | true
 {"s": "abc"}  | {"s": "ABC"}  | false | false | false | true  | true
 {"s": "abc"}  | {"s": "ABc"}  | false | false | false | true  | true
 {"s": "abc"}  | {"s": "ABcD"} | false | false | false | true  | true
 {"s": "abc"}  | {"s": "B"}    | false | false | false | true  | true
 {"s": "abcd"} | {"s": ""}     | false | false | false | true  | true
 {"s": "abcd"} | {"s": "a"}    | false | false | false | true  | true
 {"s": "abcd"} | {"s": "ab"}   | false | false | false | true  | true
 {"s": "abcd"} | {"s": "abc"}  | false | false | false | true  | true
 {"s": "abcd"} | {"s": "abcd"} | false | true  | true  | true  | false
 {"s": "abcd"} | {"s": "b"}    | true  | true  | false | false | false
 {"s": "abcd"} | {"s": "A"}    | false | false | false | true  | true
 {"s": "abcd"} | {"s": "AB"}   | false | false | false | true  | true
 {"s": "abcd"} | {"s": "ABC"}  | false | false | false | true  | true
 {"s": "abcd"} | {"s": "ABc"}  | false | false | false | true  | true
 {"s": "abcd"} | {"s": "ABcD"} | false | false | false | true  | true
 {"s": "abcd"} | {"s": "B"}    | false | false | false | true  | true
 {"s": "b"}    | {"s": ""}     | false | false | false | true  | true
 {"s": "b"}    | {"s": "a"}    | false | false | false | true  | true
 {"s": "b"}    | {"s": "ab"}   | false | false | false | true  | true
 {"s": "b"}    | {"s": "abc"}  | false | false | false | true  | true
 {"s": "b"}    | {"s": "abcd"} | false | false | false | true  | true
 {"s": "b"}    | {"s": "b"}    | false | true  | true  | true  | false
 {"s": "b"}    | {"s": "A"}    | false | false | false | true  | true
 {"s": "b"}    | {"s": "AB"}   | false | false | false | true  | true
 {"s": "b"}    | {"s": "ABC"}  | false | false | false | true  | true
 {"s": "b"}    | {"s": "ABc"}  | false | false | false | true  | true
 {"s": "b"}    | {"s": "ABcD"} | false | false | false | true  | true
 {"s": "b"}    | {"s": "B"}    | false | false | false | true  | true
 {"s": "A"}    | {"s": ""}     | false | false | false | true  | true
 {"s": "A"}    | {"s": "a"}    | true  | true  | false | false | false
 {"s": "A"}    | {"s": "ab"}   | true  | true  | false | false | false
 {"s": "A"}    | {"s": "abc"}  | true  | true  | false | false | false
 {"s": "A"}    | {"s": "abcd"} | true  | true  | false | false | false
 {"s": "A"}    | {"s": "b"}    | true  | true  | false | false | false
 {"s": "A"}    | {"s": "A"}    | false | true  | true  | true  | false
 {"s": "A"}    | {"s": "AB"}   | true  | true  | false | false | false
 {"s": "A"}    | {"s": "ABC"}  | true  | true  | false | false | false
 {"s": "A"}    | {"s": "ABc"}  | true  | true  | false | false | false
 {"s": "A"}    | {"s": "ABcD"} | true  | true  | false | false | false
 {"s": "A"}    | {"s": "B"}    | true  | true  | false | false | false
 {"s": "AB"}   | {"s": ""}     | false | false | false | true  | true
 {"s": "AB"}   | {"s": "a"}    | true  | true  | false | false | false
 {"s": "AB"}   | {"s": "ab"}   | true  | true  | false | false | false
 {"s": "AB"}   | {"s": "abc"}  | true  | true  | false | false | false
 {"s": "AB"}   | {"s": "abcd"} | true  | true  | false | false | false
 {"s": "AB"}   | {"s": "b"}    | true  | true  | false | false | false
 {"s": "AB"}   | {"s": "A"}    | false | false | false | true  | true
 {"s": "AB"}   | {"s": "AB"}   | false | true  | true  | true  | false
 {"s": "AB"}   | {"s": "ABC"}  | true  | true  | false | false | false
 {"s": "AB"}   | {"s": "ABc"}  | true  | true  | false | false | false
 {"s": "AB"}   | {"s": "ABcD"} | true  | true  | false | false | false
 {"s": "AB"}   | {"s": "B"}    | true  | true  | false | false | false
 {"s": "ABC"}  | {"s": ""}     | false | false | false | true  | true
 {"s": "ABC"}  | {"s": "a"}    | true  | true  | false | false | false
 {"s": "ABC"}  | {"s": "ab"}   | true  | true  | false | false | false
 {"s": "ABC"}  | {"s": "abc"}  | true  | true  | false | false | false
 {"s": "ABC"}  | {"s": "abcd"} | true  | true  | false | false | false
 {"s": "ABC"}  | {"s": "b"}    | true  | true  | false | false | false
 {"s": "ABC"}  | {"s": "A"}    | false | false | false | true  | true
 {"s": "ABC"}  | {"s": "AB"}   | false | false | false | true  | true
 {"s": "ABC"}  | {"s": "ABC"}  | false | true  | true  | true  | false
 {"s": "ABC"}  | {"s": "ABc"}  | true  | true  | false | false | false
 {"s": "ABC"}  | {"s": "ABcD"} | true  | true  | false | false | false
 {"s": "ABC"}  | {"s": "B"}    | true  | true  | false | false | false
 {"s": "ABc"}  | {"s": ""}     | false | false | false | true  | true
 {"s": "ABc"}  | {"s": "a"}    | true  | true  | false | false | false
 {"s": "ABc"}  | {"s": "ab"}   | true  | true  | false | false | false
 {"s": "ABc"}  | {"s": "abc"}  | true  | true  | false | false | false
 {"s": "ABc"}  | {"s": "abcd"} | true  | true  | false | false | false
 {"s": "ABc"}  | {"s": "b"}    | true  | true  | false | false | false
 {"s": "ABc"}  | {"s": "A"}    | false | false | false | true  | true
 {"s": "ABc"}  | {"s": "AB"}   | false | false | false | true  | true
 {"s": "ABc"}  | {"s": "ABC"}  | false | false | false | true  | true
 {"s": "ABc"}  | {"s": "ABc"}  | false | true  | true  | true  | false
 {"s": "ABc"}  | {"s": "ABcD"} | true  | true  | false | false | false
 {"s": "ABc"}  | {"s": "B"}    | true  | true  | false | false | false
 {"s": "ABcD"} | {"s": ""}     | false | false | false | true  | true
 {"s": "ABcD"} | {"s": "a"}    | true  | true  | false | false | false
 {"s": "ABcD"} | {"s": "ab"}   | true  | true  | false | false | false
 {"s": "ABcD"} | {"s": "abc"}  | true  | true  | false | false | false
 {"s": "ABcD"} | {"s": "abcd"} | true  | true  | false | false | false
 {"s": "ABcD"} | {"s": "b"}    | true  | true  | false | false | false
 {"s": "ABcD"} | {"s": "A"}    | false | false | false | true  | true
 {"s": "ABcD"} | {"s": "AB"}   | false | false | false | true  | true
 {"s": "ABcD"} | {"s": "ABC"}  | false | false | false | true  | true
 {"s": "ABcD"} | {"s": "ABc"}  | false | false | false | true  | true
 {"s": "ABcD"} | {"s": "ABcD"} | false | true  | true  | true  | false
 {"s": "ABcD"} | {"s": "B"}    | true  | true  | false | false | false
 {"s": "B"}    | {"s": ""}     | false | false | false | true  | true
 {"s": "B"}    | {"s": "a"}    | true  | true  | false | false | false
 {"s": "B"}    | {"s": "ab"}   | true  | true  | false | false | false
 {"s": "B"}    | {"s": "abc"}  | true  | true  | false | false | false
 {"s": "B"}    | {"s": "abcd"} | true  | true  | false | false | false
 {"s": "B"}    | {"s": "b"}    | true  | true  | false | false | false
 {"s": "B"}    | {"s": "A"}    | false | false | false | true  | true
 {"s": "B"}    | {"s": "AB"}   | false | false | false | true  | true
 {"s": "B"}    | {"s": "ABC"}  | false | false | false | true  | true
 {"s": "B"}    | {"s": "ABc"}  | false | false | false | true  | true
 {"s": "B"}    | {"s": "ABcD"} | false | false | false | true  | true
 {"s": "B"}    | {"s": "B"}    | false | true  | true  | true  | false
(144 rows)