SQL/JSON query functions

This introduces the SQL/JSON functions for querying JSON data using
jsonpath expressions. The functions are:

JSON_EXISTS()
JSON_QUERY()
JSON_VALUE()

All of these functions only operate on jsonb. The workaround for now is
to cast the argument to jsonb.

JSON_EXISTS() tests if the jsonpath expression applied to the jsonb
value yields any values. JSON_VALUE() must return a single value, and an
error occurs if it tries to return multiple values. JSON_QUERY() must
return a json object or array, and there are various WRAPPER options for
handling scalar or multi-value results. Both these functions have
options for handling EMPTY and ERROR conditions.

Nikita Glukhov

Reviewers have included (in no particular order) Andres Freund, Alexander
Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu,
Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby.

Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru

1 files changed, 317 insertions, 0 deletions

diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql
new file mode 100644
index 00000000000..00a067a06a4
--- /dev/null
+++ b/src/test/regress/sql/jsonb_sqljson.sql
@@ -0,0 +1,317 @@
+-- JSON_EXISTS
+
+SELECT JSON_EXISTS(NULL::jsonb, '$');
+
+SELECT JSON_EXISTS(jsonb '[]', '$');
+SELECT JSON_EXISTS(JSON_OBJECT(RETURNING jsonb), '$');
+
+SELECT JSON_EXISTS(jsonb '1', '$');
+SELECT JSON_EXISTS(jsonb 'null', '$');
+SELECT JSON_EXISTS(jsonb '[]', '$');
+
+SELECT JSON_EXISTS(jsonb '1', '$.a');
+SELECT JSON_EXISTS(jsonb '1', 'strict $.a');
+SELECT JSON_EXISTS(jsonb '1', 'strict $.a' ERROR ON ERROR);
+SELECT JSON_EXISTS(jsonb 'null', '$.a');
+SELECT JSON_EXISTS(jsonb '[]', '$.a');
+SELECT JSON_EXISTS(jsonb '[1, "aaa", {"a": 1}]', 'strict $.a');
+SELECT JSON_EXISTS(jsonb '[1, "aaa", {"a": 1}]', 'lax $.a');
+SELECT JSON_EXISTS(jsonb '{}', '$.a');
+SELECT JSON_EXISTS(jsonb '{"b": 1, "a": 2}', '$.a');
+
+SELECT JSON_EXISTS(jsonb '1', '$.a.b');
+SELECT JSON_EXISTS(jsonb '{"a": {"b": 1}}', '$.a.b');
+SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.a.b');
+
+SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING 1 AS x);
+SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING '1' AS x);
+SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 2 AS y);
+SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 1 AS y);
+
+-- extension: boolean expressions
+SELECT JSON_EXISTS(jsonb '1', '$ > 2');
+SELECT JSON_EXISTS(jsonb '1', '$.a > 2' ERROR ON ERROR);
+
+-- extension: RETURNING clause
+SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING bool);
+SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING bool);
+SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING int);
+SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING int);
+SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING text);
+SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING text);
+SELECT JSON_EXISTS(jsonb '1', 'strict $[1]' RETURNING text FALSE ON ERROR);
+SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb);
+SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4);
+
+
+-- JSON_VALUE
+
+SELECT JSON_VALUE(NULL::jsonb, '$');
+
+SELECT JSON_VALUE(jsonb 'null', '$');
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING int);
+
+SELECT JSON_VALUE(jsonb 'true', '$');
+SELECT JSON_VALUE(jsonb 'true', '$' RETURNING bool);
+
+SELECT JSON_VALUE(jsonb '123', '$');
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING int) + 234;
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING text);
+/* jsonb bytea ??? */
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING bytea ERROR ON ERROR);
+
+SELECT JSON_VALUE(jsonb '1.23', '$');
+SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING int);
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING numeric);
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING int ERROR ON ERROR);
+
+SELECT JSON_VALUE(jsonb '"aaa"', '$');
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING text);
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(5));
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(2));
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json);
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING jsonb);
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING jsonb ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '"\"aaa\""', '$' RETURNING json);
+SELECT JSON_VALUE(jsonb '"\"aaa\""', '$' RETURNING jsonb);
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING int);
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING int ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING int DEFAULT 111 ON ERROR);
+SELECT JSON_VALUE(jsonb '"123"', '$' RETURNING int) + 234;
+
+SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9;
+
+-- Test NULL checks execution in domain types
+CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL;
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR);
+
+SELECT JSON_VALUE(jsonb '[]', '$');
+SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '{}', '$');
+SELECT JSON_VALUE(jsonb '{}', '$' ERROR ON ERROR);
+
+SELECT JSON_VALUE(jsonb '1', '$.a');
+SELECT JSON_VALUE(jsonb '1', 'strict $.a' ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 'error' ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 2 ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT 2 ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' NULL ON EMPTY DEFAULT '2' ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY DEFAULT '3' ON ERROR);
+
+SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' DEFAULT '0' ON ERROR);
+SELECT JSON_VALUE(jsonb '[" "]', '$[*]' RETURNING int ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '[" "]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR);
+SELECT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR);
+
+SELECT
+	x,
+	JSON_VALUE(
+		jsonb '{"a": 1, "b": 2}',
+		'$.* ? (@ > $x)' PASSING x AS x
+		RETURNING int
+		DEFAULT -1 ON EMPTY
+		DEFAULT -2 ON ERROR
+	) y
+FROM
+	generate_series(0, 2) x;
+
+SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
+SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point);
+
+-- Test timestamptz passing and output
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING timestamptz);
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING timestamp);
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json);
+SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb);
+
+-- JSON_QUERY
+
+SELECT
+	JSON_QUERY(js, '$'),
+	JSON_QUERY(js, '$' WITHOUT WRAPPER),
+	JSON_QUERY(js, '$' WITH CONDITIONAL WRAPPER),
+	JSON_QUERY(js, '$' WITH UNCONDITIONAL ARRAY WRAPPER),
+	JSON_QUERY(js, '$' WITH ARRAY WRAPPER)
+FROM
+	(VALUES
+		(jsonb 'null'),
+		('12.3'),
+		('true'),
+		('"aaa"'),
+		('[1, null, "2"]'),
+		('{"a": 1, "b": [2]}')
+	) foo(js);
+
+SELECT
+	JSON_QUERY(js, 'strict $[*]') AS "unspec",
+	JSON_QUERY(js, 'strict $[*]' WITHOUT WRAPPER) AS "without",
+	JSON_QUERY(js, 'strict $[*]' WITH CONDITIONAL WRAPPER) AS "with cond",
+	JSON_QUERY(js, 'strict $[*]' WITH UNCONDITIONAL ARRAY WRAPPER) AS "with uncond",
+	JSON_QUERY(js, 'strict $[*]' WITH ARRAY WRAPPER) AS "with"
+FROM
+	(VALUES
+		(jsonb '1'),
+		('[]'),
+		('[null]'),
+		('[12.3]'),
+		('[true]'),
+		('["aaa"]'),
+		('[[1, 2, 3]]'),
+		('[{"a": 1, "b": [2]}]'),
+		('[1, "2", null, [3]]')
+	) foo(js);
+
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text KEEP QUOTES);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text KEEP QUOTES ON SCALAR STRING);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text OMIT QUOTES);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text OMIT QUOTES ON SCALAR STRING);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' OMIT QUOTES ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING json OMIT QUOTES ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING bytea FORMAT JSON OMIT QUOTES ERROR ON ERROR);
+
+-- QUOTES behavior should not be specified when WITH WRAPPER used:
+-- Should fail
+SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER OMIT QUOTES);
+SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER KEEP QUOTES);
+SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER KEEP QUOTES);
+SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER OMIT QUOTES);
+-- Should succeed
+SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER OMIT QUOTES);
+SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER KEEP QUOTES);
+
+SELECT JSON_QUERY(jsonb '[]', '$[*]');
+SELECT JSON_QUERY(jsonb '[]', '$[*]' NULL ON EMPTY);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ON EMPTY);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ARRAY ON EMPTY);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY OBJECT ON EMPTY);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY);
+
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY NULL ON ERROR);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY ARRAY ON ERROR);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY OBJECT ON ERROR);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR);
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' DEFAULT '"empty"' ON ERROR);
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING json);
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING json FORMAT JSON);
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING jsonb);
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING jsonb FORMAT JSON);
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text);
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(10));
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3));
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text FORMAT JSON);
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea);
+SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea FORMAT JSON);
+
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea EMPTY OBJECT ON ERROR);
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea FORMAT JSON EMPTY OBJECT ON ERROR);
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING json EMPTY OBJECT ON ERROR);
+SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING jsonb EMPTY OBJECT ON ERROR);
+
+SELECT
+	x, y,
+	JSON_QUERY(
+		jsonb '[1,2,3,4,5,null]',
+		'$[*] ? (@ >= $x && @ <= $y)'
+		PASSING x AS x, y AS y
+		WITH CONDITIONAL WRAPPER
+		EMPTY ARRAY ON EMPTY
+	) list
+FROM
+	generate_series(0, 4) x,
+	generate_series(0, 4) y;
+
+-- Extension: record types returning
+CREATE TYPE sqljsonb_rec AS (a int, t text, js json, jb jsonb, jsa json[]);
+CREATE TYPE sqljsonb_reca AS (reca sqljsonb_rec[]);
+
+SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}},  {"a": 2}]', '$[0]' RETURNING sqljsonb_rec);
+SELECT * FROM unnest((JSON_QUERY(jsonb '{"jsa":  [{"a": 1, "b": ["foo"]}, {"a": 2, "c": {}}, 123]}', '$' RETURNING sqljsonb_rec)).jsa);
+SELECT * FROM unnest((JSON_QUERY(jsonb '{"reca": [{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]}', '$' RETURNING sqljsonb_reca)).reca);
+
+-- Extension: array types returning
+SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' RETURNING int[] WITH WRAPPER);
+SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]', '$' RETURNING sqljsonb_rec[]));
+
+-- Extension: domain types returning
+SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb_int_not_null);
+SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null);
+
+-- Test timestamptz passing and output
+SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
+SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json);
+SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb);
+
+-- Test constraints
+
+CREATE TABLE test_jsonb_constraints (
+	js text,
+	i int,
+	x jsonb DEFAULT JSON_QUERY(jsonb '[1,2]', '$[*]' WITH WRAPPER)
+	CONSTRAINT test_jsonb_constraint1
+		CHECK (js IS JSON)
+	CONSTRAINT test_jsonb_constraint2
+		CHECK (JSON_EXISTS(js::jsonb, '$.a' PASSING i + 5 AS int, i::text AS txt, array[1,2,3] as arr))
+	CONSTRAINT test_jsonb_constraint3
+		CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int DEFAULT ('12' || i)::int ON EMPTY ERROR ON ERROR) > i)
+	CONSTRAINT test_jsonb_constraint4
+		CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < jsonb '[10]')
+	CONSTRAINT test_jsonb_constraint5
+		CHECK (JSON_QUERY(js::jsonb, '$.a' RETURNING char(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) >  'a' COLLATE "C")
+	CONSTRAINT test_jsonb_constraint6
+		CHECK (JSON_EXISTS(js::jsonb, 'strict $.a' RETURNING int TRUE ON ERROR) < 2)
+);
+
+\d test_jsonb_constraints
+
+SELECT check_clause
+FROM information_schema.check_constraints
+WHERE constraint_name LIKE 'test_jsonb_constraint%';
+
+SELECT pg_get_expr(adbin, adrelid) FROM pg_attrdef WHERE adrelid = 'test_jsonb_constraints'::regclass;
+
+INSERT INTO test_jsonb_constraints VALUES ('', 1);
+INSERT INTO test_jsonb_constraints VALUES ('1', 1);
+INSERT INTO test_jsonb_constraints VALUES ('[]');
+INSERT INTO test_jsonb_constraints VALUES ('{"b": 1}', 1);
+INSERT INTO test_jsonb_constraints VALUES ('{"a": 1}', 1);
+INSERT INTO test_jsonb_constraints VALUES ('{"a": 7}', 1);
+INSERT INTO test_jsonb_constraints VALUES ('{"a": 10}', 1);
+
+DROP TABLE test_jsonb_constraints;
+
+-- Test mutabilily od query functions
+CREATE TABLE test_jsonb_mutability(js jsonb);
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a[0]'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@ < $.datetime())'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime() < $.datetime())'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime() < $.datetime("HH:MI TZH"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI TZH") < $.datetime("HH:MI TZH"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI") < $.datetime("YY-MM-DD HH:MI"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI TZH") < $.datetime("YY-MM-DD HH:MI"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $x' PASSING '12:34'::timetz AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $y' PASSING '12:34'::timetz AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() < $x' PASSING '12:34'::timetz AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() < $x' PASSING '1234'::int AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() ? (@ == $x)' PASSING '12:34'::time AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("YY-MM-DD") ? (@ == $x)' PASSING '2020-07-14'::date AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x));
+DROP TABLE test_jsonb_mutability;