Hi. The regression test was very verbose; I removed some of it. Also polished function ExecEvalJsonIsPredicate a little bit.
-- jian https://www.enterprisedb.com/
From f057b9434e0f0f8d7fd8f468776759b39db67550 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Fri, 13 Mar 2026 11:51:26 +0800 Subject: [PATCH v3 1/1] IS JSON predicate work with domain type Allow IS JSON predicate to work with domains based on type TEXT, JSON, JSONB, and BYTEA. Discussion: https://postgr.es/m/CACJufxEk34DnJFG72CRsPPT4tsJL9arobX0tNPsn7yH28J=z...@mail.gmail.com Commitfest: https://commitfest.postgresql.org/patch/6237 --- src/backend/executor/execExprInterp.c | 9 +- src/backend/nodes/makefuncs.c | 3 +- src/backend/parser/gram.y | 8 +- src/backend/parser/parse_expr.c | 7 +- src/include/nodes/makefuncs.h | 2 +- src/include/nodes/primnodes.h | 1 + src/test/regress/expected/sqljson.out | 190 ++++++++++++++++++++++++++ src/test/regress/sql/sqljson.sql | 82 +++++++++++ 8 files changed, 287 insertions(+), 15 deletions(-) diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index 61ff5ddc74c..34bb2b6690a 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -4740,7 +4740,6 @@ ExecEvalJsonIsPredicate(ExprState *state, ExprEvalStep *op) { JsonIsPredicate *pred = op->d.is_json.pred; Datum js = *op->resvalue; - Oid exprtype; bool res; if (*op->resnull) @@ -4749,9 +4748,7 @@ ExecEvalJsonIsPredicate(ExprState *state, ExprEvalStep *op) return; } - exprtype = exprType(pred->expr); - - if (exprtype == TEXTOID || exprtype == JSONOID) + if (pred->resultBaseType == TEXTOID || pred->resultBaseType == JSONOID) { text *json = DatumGetTextP(js); @@ -4784,10 +4781,10 @@ ExecEvalJsonIsPredicate(ExprState *state, ExprEvalStep *op) * Do full parsing pass only for uniqueness check or for JSON text * validation. */ - if (res && (pred->unique_keys || exprtype == TEXTOID)) + if (res && (pred->unique_keys || pred->resultBaseType == TEXTOID)) res = json_validate(json, pred->unique_keys, false); } - else if (exprtype == JSONBOID) + else if (pred->resultBaseType == JSONBOID) { if (pred->item_type == JS_TYPE_ANY) res = true; diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index 2caec621d73..adc2f0c8dc2 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -984,7 +984,7 @@ makeJsonKeyValue(Node *key, Node *value) */ Node * makeJsonIsPredicate(Node *expr, JsonFormat *format, JsonValueType item_type, - bool unique_keys, int location) + bool unique_keys, Oid resultBaseType, int location) { JsonIsPredicate *n = makeNode(JsonIsPredicate); @@ -992,6 +992,7 @@ makeJsonIsPredicate(Node *expr, JsonFormat *format, JsonValueType item_type, n->format = format; n->item_type = item_type; n->unique_keys = unique_keys; + n->resultBaseType = resultBaseType; n->location = location; return (Node *) n; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index f01f5734fe9..0fe4ca081a7 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -15700,7 +15700,7 @@ a_expr: c_expr { $$ = $1; } { JsonFormat *format = makeJsonFormat(JS_FORMAT_DEFAULT, JS_ENC_DEFAULT, -1); - $$ = makeJsonIsPredicate($1, format, $3, $4, @1); + $$ = makeJsonIsPredicate($1, format, $3, $4, InvalidOid, @1); } /* * Required by SQL/JSON, but there are conflicts @@ -15709,7 +15709,7 @@ a_expr: c_expr { $$ = $1; } IS json_predicate_type_constraint json_key_uniqueness_constraint_opt %prec IS { - $$ = makeJsonIsPredicate($1, $2, $4, $5, @1); + $$ = makeJsonIsPredicate($1, $2, $4, $5, InvalidOid, @1); } */ | a_expr IS NOT @@ -15718,7 +15718,7 @@ a_expr: c_expr { $$ = $1; } { JsonFormat *format = makeJsonFormat(JS_FORMAT_DEFAULT, JS_ENC_DEFAULT, -1); - $$ = makeNotExpr(makeJsonIsPredicate($1, format, $4, $5, @1), @1); + $$ = makeNotExpr(makeJsonIsPredicate($1, format, $4, $5, InvalidOid, @1), @1); } /* * Required by SQL/JSON, but there are conflicts @@ -15728,7 +15728,7 @@ a_expr: c_expr { $$ = $1; } json_predicate_type_constraint json_key_uniqueness_constraint_opt %prec IS { - $$ = makeNotExpr(makeJsonIsPredicate($1, $2, $5, $6, @1), @1); + $$ = makeNotExpr(makeJsonIsPredicate($1, $2, $5, $6, InvalidOid, @1), @1); } */ | DEFAULT diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 96991cae764..2571ef06062 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -4066,7 +4066,7 @@ transformJsonParseArg(ParseState *pstate, Node *jsexpr, JsonFormat *format, Node *raw_expr = transformExprRecurse(pstate, jsexpr); Node *expr = raw_expr; - *exprtype = exprType(expr); + *exprtype = getBaseType(exprType(expr)); /* prepare input document */ if (*exprtype == BYTEAOID) @@ -4121,11 +4121,12 @@ transformJsonIsPredicate(ParseState *pstate, JsonIsPredicate *pred) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("cannot use type %s in IS JSON predicate", - format_type_be(exprtype)))); + format_type_be(exprtype))), + parser_errposition(pstate, exprLocation(expr))); /* This intentionally(?) drops the format clause. */ return makeJsonIsPredicate(expr, NULL, pred->item_type, - pred->unique_keys, pred->location); + pred->unique_keys, exprtype, pred->location); } /* diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h index 982ec25ae14..bae81793c4b 100644 --- a/src/include/nodes/makefuncs.h +++ b/src/include/nodes/makefuncs.h @@ -117,7 +117,7 @@ extern JsonValueExpr *makeJsonValueExpr(Expr *raw_expr, Expr *formatted_expr, extern Node *makeJsonKeyValue(Node *key, Node *value); extern Node *makeJsonIsPredicate(Node *expr, JsonFormat *format, JsonValueType item_type, bool unique_keys, - int location); + Oid resultBaseType, int location); extern JsonBehavior *makeJsonBehavior(JsonBehaviorType btype, Node *expr, int location); extern JsonTablePath *makeJsonTablePath(Const *pathvalue, char *pathname); diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 384df50c80a..2d6888e4188 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1762,6 +1762,7 @@ typedef struct JsonIsPredicate JsonFormat *format; /* FORMAT clause, if specified */ JsonValueType item_type; /* JSON item type */ bool unique_keys; /* check key uniqueness? */ + Oid resultBaseType; /* base type of the subject expression */ ParseLoc location; /* token location, or -1 if unknown */ } JsonIsPredicate; diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out index c7b9e575445..4887951469b 100644 --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -1148,6 +1148,80 @@ SELECT NULL::bytea IS JSON; SELECT NULL::int IS JSON; ERROR: cannot use type integer in IS JSON predicate +LINE 1: SELECT NULL::int IS JSON; + ^ +CREATE DOMAIN jd1 AS json CHECK ((VALUE ->'a')::text <> '3'); +CREATE DOMAIN jd2 AS jsonb CHECK ((VALUE ->'a') = '1'::jsonb); +CREATE DOMAIN jd3 AS text CHECK (VALUE <> 'a'); +CREATE DOMAIN jd4 AS bytea CHECK (VALUE <> '\x61'); +CREATE DOMAIN jd5 AS date CHECK (VALUE <> NULL); +SELECT NULL::jd1 IS JSON; + ?column? +---------- + +(1 row) + +SELECT NULL::jd1 IS NOT JSON; + ?column? +---------- + +(1 row) + +SELECT NULL::jd2 IS JSON; + ?column? +---------- + +(1 row) + +SELECT NULL::jd3 IS JSON; + ?column? +---------- + +(1 row) + +SELECT NULL::jd4 IS JSON; + ?column? +---------- + +(1 row) + +SELECT NULL::jd4 IS NOT JSON; + ?column? +---------- + +(1 row) + +SELECT NULL::jd5 IS JSON; -- error +ERROR: cannot use type date in IS JSON predicate +LINE 1: SELECT NULL::jd5 IS JSON; + ^ +SELECT NULL::jd5 IS JSON WITH UNIQUE KEYS; -- error +ERROR: cannot use type date in IS JSON predicate +LINE 1: SELECT NULL::jd5 IS JSON WITH UNIQUE KEYS; + ^ +SELECT a::jd2 IS JSON WITH UNIQUE KEYS as col1 FROM (VALUES('{"a": 1, "a": 2}')) s(a); -- error +ERROR: value for domain jd2 violates check constraint "jd2_check" +CREATE VIEW domain_isjson AS +WITH cte(a) as (VALUES('{"a": 1, "a": 2}')) +SELECT a::jd1 IS JSON WITH UNIQUE KEYS as jd1, + a::jd3 IS JSON WITH UNIQUE KEYS as jd3, + a::jd4 IS JSON WITH UNIQUE KEYS as jd4 +FROM cte; +\sv domain_isjson +CREATE OR REPLACE VIEW public.domain_isjson AS + WITH cte(a) AS ( + VALUES ('{"a": 1, "a": 2}'::text) + ) + SELECT a::jd1 IS JSON WITH UNIQUE KEYS AS jd1, + a::jd3 IS JSON WITH UNIQUE KEYS AS jd3, + a::jd4 IS JSON WITH UNIQUE KEYS AS jd4 + FROM cte +SELECT * FROM domain_isjson; + jd1 | jd3 | jd4 +-----+-----+----- + f | f | f +(1 row) + SELECT '' IS JSON; ?column? ---------- @@ -1206,6 +1280,38 @@ FROM ["a",] | f | t | f | f | f | f | f | f (16 rows) +SELECT + js, + js IS JSON "IS JSON", + js IS NOT JSON "IS NOT JSON", + js IS JSON VALUE "IS VALUE", + js IS JSON OBJECT "IS OBJECT", + js IS JSON ARRAY "IS ARRAY", + js IS JSON SCALAR "IS SCALAR", + js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE", + js IS JSON WITH UNIQUE KEYS "WITH UNIQUE" +FROM + (SELECT js::jd3 FROM test_is_json ) foo(js); + js | IS JSON | IS NOT JSON | IS VALUE | IS OBJECT | IS ARRAY | IS SCALAR | WITHOUT UNIQUE | WITH UNIQUE +-----------------------------------------------+---------+-------------+----------+-----------+----------+-----------+----------------+------------- + | | | | | | | | + | f | t | f | f | f | f | f | f + 123 | t | f | t | f | f | t | t | t + "aaa " | t | f | t | f | f | t | t | t + true | t | f | t | f | f | t | t | t + null | t | f | t | f | f | t | t | t + [] | t | f | t | f | t | f | t | t + [1, "2", {}] | t | f | t | f | t | f | t | t + {} | t | f | t | t | f | f | t | t + { "a": 1, "b": null } | t | f | t | t | f | f | t | t + { "a": 1, "a": null } | t | f | t | t | f | f | t | f + { "a": 1, "b": [{ "a": 1 }, { "a": 2 }] } | t | f | t | t | f | f | t | t + { "a": 1, "b": [{ "a": 1, "b": 0, "a": 2 }] } | t | f | t | t | f | f | t | f + aaa | f | t | f | f | f | f | f | f + {a:1} | f | t | f | f | f | f | f | f + ["a",] | f | t | f | f | f | f | f | f +(16 rows) + SELECT js, js IS JSON "IS JSON", @@ -1233,6 +1339,33 @@ FROM { "a": 1, "b": [{ "a": 1, "b": 0, "a": 2 }] } | t | f | t | t | f | f | t | f (11 rows) +SELECT + js, + js IS JSON "IS JSON", + js IS NOT JSON "IS NOT JSON", + js IS JSON VALUE "IS VALUE", + js IS JSON OBJECT "IS OBJECT", + js IS JSON ARRAY "IS ARRAY", + js IS JSON SCALAR "IS SCALAR", + js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE", + js IS JSON WITH UNIQUE KEYS "WITH UNIQUE" +FROM + (SELECT js::jd1 FROM test_is_json WHERE js IS JSON) foo(js); + js | IS JSON | IS NOT JSON | IS VALUE | IS OBJECT | IS ARRAY | IS SCALAR | WITHOUT UNIQUE | WITH UNIQUE +-----------------------------------------------+---------+-------------+----------+-----------+----------+-----------+----------------+------------- + 123 | t | f | t | f | f | t | t | t + "aaa " | t | f | t | f | f | t | t | t + true | t | f | t | f | f | t | t | t + null | t | f | t | f | f | t | t | t + [] | t | f | t | f | t | f | t | t + [1, "2", {}] | t | f | t | f | t | f | t | t + {} | t | f | t | t | f | f | t | t + { "a": 1, "b": null } | t | f | t | t | f | f | t | t + { "a": 1, "a": null } | t | f | t | t | f | f | t | f + { "a": 1, "b": [{ "a": 1 }, { "a": 2 }] } | t | f | t | t | f | f | t | t + { "a": 1, "b": [{ "a": 1, "b": 0, "a": 2 }] } | t | f | t | t | f | f | t | f +(11 rows) + SELECT js0, js IS JSON "IS JSON", @@ -1260,6 +1393,33 @@ FROM { "a": 1, "b": [{ "a": 1, "b": 0, "a": 2 }] } | t | f | t | t | f | f | t | f (11 rows) +SELECT + js0, + js IS JSON "IS JSON", + js IS NOT JSON "IS NOT JSON", + js IS JSON VALUE "IS VALUE", + js IS JSON OBJECT "IS OBJECT", + js IS JSON ARRAY "IS ARRAY", + js IS JSON SCALAR "IS SCALAR", + js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE", + js IS JSON WITH UNIQUE KEYS "WITH UNIQUE" +FROM + (SELECT js, js::jd4 FROM test_is_json WHERE js IS JSON) foo(js0, js); + js0 | IS JSON | IS NOT JSON | IS VALUE | IS OBJECT | IS ARRAY | IS SCALAR | WITHOUT UNIQUE | WITH UNIQUE +-----------------------------------------------+---------+-------------+----------+-----------+----------+-----------+----------------+------------- + 123 | t | f | t | f | f | t | t | t + "aaa " | t | f | t | f | f | t | t | t + true | t | f | t | f | f | t | t | t + null | t | f | t | f | f | t | t | t + [] | t | f | t | f | t | f | t | t + [1, "2", {}] | t | f | t | f | t | f | t | t + {} | t | f | t | t | f | f | t | t + { "a": 1, "b": null } | t | f | t | t | f | f | t | t + { "a": 1, "a": null } | t | f | t | t | f | f | t | f + { "a": 1, "b": [{ "a": 1 }, { "a": 2 }] } | t | f | t | t | f | f | t | t + { "a": 1, "b": [{ "a": 1, "b": 0, "a": 2 }] } | t | f | t | t | f | f | t | f +(11 rows) + SELECT js, js IS JSON "IS JSON", @@ -1287,6 +1447,36 @@ FROM {"a": 1, "b": [{"a": 2, "b": 0}]} | t | f | t | t | f | f | t | t (11 rows) +SELECT js::jd2 FROM test_is_json WHERE js IS JSON; -- error +ERROR: value for domain jd2 violates check constraint "jd2_check" +ALTER DOMAIN jd2 DROP CONSTRAINT jd2_check; +SELECT + js, + js IS JSON "IS JSON", + js IS NOT JSON "IS NOT JSON", + js IS JSON VALUE "IS VALUE", + js IS JSON OBJECT "IS OBJECT", + js IS JSON ARRAY "IS ARRAY", + js IS JSON SCALAR "IS SCALAR", + js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE", + js IS JSON WITH UNIQUE KEYS "WITH UNIQUE" +FROM + (SELECT js::jd2 FROM test_is_json WHERE js IS JSON) foo(js); + js | IS JSON | IS NOT JSON | IS VALUE | IS OBJECT | IS ARRAY | IS SCALAR | WITHOUT UNIQUE | WITH UNIQUE +-------------------------------------+---------+-------------+----------+-----------+----------+-----------+----------------+------------- + 123 | t | f | t | f | f | t | t | t + "aaa " | t | f | t | f | f | t | t | t + true | t | f | t | f | f | t | t | t + null | t | f | t | f | f | t | t | t + [] | t | f | t | f | t | f | t | t + [1, "2", {}] | t | f | t | f | t | f | t | t + {} | t | f | t | t | f | f | t | t + {"a": 1, "b": null} | t | f | t | t | f | f | t | t + {"a": null} | t | f | t | t | f | f | t | t + {"a": 1, "b": [{"a": 1}, {"a": 2}]} | t | f | t | t | f | f | t | t + {"a": 1, "b": [{"a": 2, "b": 0}]} | t | f | t | t | f | f | t | t +(11 rows) + -- Test IS JSON deparsing EXPLAIN (VERBOSE, COSTS OFF) SELECT '1' IS JSON AS "any", ('1' || i) IS JSON SCALAR AS "scalar", '[]' IS NOT JSON ARRAY AS "array", '{}' IS JSON OBJECT WITH UNIQUE AS "object" FROM generate_series(1, 3) i; diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql index 343d344d270..bd5ce53f272 100644 --- a/src/test/regress/sql/sqljson.sql +++ b/src/test/regress/sql/sqljson.sql @@ -395,6 +395,33 @@ SELECT NULL::text IS JSON; SELECT NULL::bytea IS JSON; SELECT NULL::int IS JSON; +CREATE DOMAIN jd1 AS json CHECK ((VALUE ->'a')::text <> '3'); +CREATE DOMAIN jd2 AS jsonb CHECK ((VALUE ->'a') = '1'::jsonb); +CREATE DOMAIN jd3 AS text CHECK (VALUE <> 'a'); +CREATE DOMAIN jd4 AS bytea CHECK (VALUE <> '\x61'); +CREATE DOMAIN jd5 AS date CHECK (VALUE <> NULL); + +SELECT NULL::jd1 IS JSON; +SELECT NULL::jd1 IS NOT JSON; +SELECT NULL::jd2 IS JSON; +SELECT NULL::jd3 IS JSON; +SELECT NULL::jd4 IS JSON; +SELECT NULL::jd4 IS NOT JSON; +SELECT NULL::jd5 IS JSON; -- error +SELECT NULL::jd5 IS JSON WITH UNIQUE KEYS; -- error + +SELECT a::jd2 IS JSON WITH UNIQUE KEYS as col1 FROM (VALUES('{"a": 1, "a": 2}')) s(a); -- error + +CREATE VIEW domain_isjson AS +WITH cte(a) as (VALUES('{"a": 1, "a": 2}')) +SELECT a::jd1 IS JSON WITH UNIQUE KEYS as jd1, + a::jd3 IS JSON WITH UNIQUE KEYS as jd3, + a::jd4 IS JSON WITH UNIQUE KEYS as jd4 +FROM cte; + +\sv domain_isjson +SELECT * FROM domain_isjson; + SELECT '' IS JSON; SELECT bytea '\x00' IS JSON; @@ -432,6 +459,19 @@ SELECT FROM test_is_json; +SELECT + js, + js IS JSON "IS JSON", + js IS NOT JSON "IS NOT JSON", + js IS JSON VALUE "IS VALUE", + js IS JSON OBJECT "IS OBJECT", + js IS JSON ARRAY "IS ARRAY", + js IS JSON SCALAR "IS SCALAR", + js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE", + js IS JSON WITH UNIQUE KEYS "WITH UNIQUE" +FROM + (SELECT js::jd3 FROM test_is_json ) foo(js); + SELECT js, js IS JSON "IS JSON", @@ -445,6 +485,19 @@ SELECT FROM (SELECT js::json FROM test_is_json WHERE js IS JSON) foo(js); +SELECT + js, + js IS JSON "IS JSON", + js IS NOT JSON "IS NOT JSON", + js IS JSON VALUE "IS VALUE", + js IS JSON OBJECT "IS OBJECT", + js IS JSON ARRAY "IS ARRAY", + js IS JSON SCALAR "IS SCALAR", + js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE", + js IS JSON WITH UNIQUE KEYS "WITH UNIQUE" +FROM + (SELECT js::jd1 FROM test_is_json WHERE js IS JSON) foo(js); + SELECT js0, js IS JSON "IS JSON", @@ -458,6 +511,19 @@ SELECT FROM (SELECT js, js::bytea FROM test_is_json WHERE js IS JSON) foo(js0, js); +SELECT + js0, + js IS JSON "IS JSON", + js IS NOT JSON "IS NOT JSON", + js IS JSON VALUE "IS VALUE", + js IS JSON OBJECT "IS OBJECT", + js IS JSON ARRAY "IS ARRAY", + js IS JSON SCALAR "IS SCALAR", + js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE", + js IS JSON WITH UNIQUE KEYS "WITH UNIQUE" +FROM + (SELECT js, js::jd4 FROM test_is_json WHERE js IS JSON) foo(js0, js); + SELECT js, js IS JSON "IS JSON", @@ -471,6 +537,22 @@ SELECT FROM (SELECT js::jsonb FROM test_is_json WHERE js IS JSON) foo(js); +SELECT js::jd2 FROM test_is_json WHERE js IS JSON; -- error +ALTER DOMAIN jd2 DROP CONSTRAINT jd2_check; + +SELECT + js, + js IS JSON "IS JSON", + js IS NOT JSON "IS NOT JSON", + js IS JSON VALUE "IS VALUE", + js IS JSON OBJECT "IS OBJECT", + js IS JSON ARRAY "IS ARRAY", + js IS JSON SCALAR "IS SCALAR", + js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE", + js IS JSON WITH UNIQUE KEYS "WITH UNIQUE" +FROM + (SELECT js::jd2 FROM test_is_json WHERE js IS JSON) foo(js); + -- Test IS JSON deparsing EXPLAIN (VERBOSE, COSTS OFF) SELECT '1' IS JSON AS "any", ('1' || i) IS JSON SCALAR AS "scalar", '[]' IS NOT JSON ARRAY AS "array", '{}' IS JSON OBJECT WITH UNIQUE AS "object" FROM generate_series(1, 3) i; -- 2.34.1
