public inbox for [email protected]
help / color / mirror / Atom feedIS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT
7+ messages / 4 participants
[nested] [flat]
* IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT
@ 2025-11-18 15:43 jian he <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: jian he @ 2025-11-18 15:43 UTC (permalink / raw)
To: pgsql-hackers
hi.
src/backend/executor/execExpr.c
case T_JsonIsPredicate:
{
JsonIsPredicate *pred = (JsonIsPredicate *) node;
ExecInitExprRec((Expr *) pred->expr, state, resv, resnull);
scratch.opcode = EEOP_IS_JSON;
scratch.d.is_json.pred = pred;
ExprEvalPushStep(state, &scratch);
break;
}
gram.y:
a_expr IS json_predicate_type_constraint
the above shows the a_expr will be transformed and then evaluated.
The exprType type of a_expr as domain should work just fine.
The attached patch implements this, and it seems to be quite straightforward.
(extensive regress tests added)
CREATE DOMAIN jd1 AS JSON CHECK ((VALUE ->'a')::text <> '3');
CREATE DOMAIN jd2 AS JSONB CHECK ((VALUE ->'a') = '1'::jsonb);
CREATE DOMAIN jd4 AS bytea CHECK (VALUE <> '\x61');
SELECT NULL::jd1 IS JSON;
SELECT NULL::jd2 IS JSON;
SELECT NULL::jd4 IS JSON;
in the master, the above 3 IS JSON would return error,
with the attached patch, it will return NULL.
I checked the discussion links [1], but couldn’t find the reason domains aren’t
supported. I guess at that time, we didn't think about this issue.
[1] https://git.postgresql.org/cgit/postgresql.git/commit/?id=6ee30209a6f161d0a267a33f090c70c579c87c00
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v1-0001-IS-JSON-predicate-work-with-domain-type.patch (23.0K, 2-v1-0001-IS-JSON-predicate-work-with-domain-type.patch)
download | inline diff:
From 0f439aa7e737b04a69e0124ca2b535b7d16d7eb3 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 18 Nov 2025 23:40:47 +0800
Subject: [PATCH v1 1/1] IS JSON predicate work with domain type
IS JSON predicate work with domain base type is TEXT, JSON, JSONB, BYTEA.
discussion: https://postgr.es/m/
---
src/backend/executor/execExprInterp.c | 2 +
src/backend/nodes/makefuncs.c | 3 +-
src/backend/parser/gram.y | 8 +-
src/backend/parser/parse_expr.c | 17 +-
src/include/nodes/makefuncs.h | 2 +-
src/include/nodes/primnodes.h | 1 +
src/test/regress/expected/sqljson.out | 216 ++++++++++++++++++++++++++
src/test/regress/sql/sqljson.sql | 109 +++++++++++++
8 files changed, 346 insertions(+), 12 deletions(-)
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 0e1a74976f7..5580fc8e0e4 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4746,6 +4746,8 @@ ExecEvalJsonIsPredicate(ExprState *state, ExprEvalStep *op)
}
exprtype = exprType(pred->expr);
+ if (exprtype != pred->resultBaseType)
+ exprtype = pred->resultBaseType;
if (exprtype == TEXTOID || exprtype == JSONOID)
{
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index e2d9e9be41a..7e339e643a7 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 oid, 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 = oid;
n->location = location;
return (Node *) n;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c3a0a354a9c..fa70761c06f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -15522,7 +15522,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
@@ -15531,7 +15531,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
@@ -15540,7 +15540,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
@@ -15550,7 +15550,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 67fb2fb485d..57261583712 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4055,11 +4055,13 @@ transformJsonParseArg(ParseState *pstate, Node *jsexpr, JsonFormat *format,
{
Node *raw_expr = transformExprRecurse(pstate, jsexpr);
Node *expr = raw_expr;
+ Oid basetype;
*exprtype = exprType(expr);
+ basetype = getBaseType(*exprtype);
/* prepare input document */
- if (*exprtype == BYTEAOID)
+ if (basetype == BYTEAOID)
{
JsonValueExpr *jve;
@@ -4075,11 +4077,11 @@ transformJsonParseArg(ParseState *pstate, Node *jsexpr, JsonFormat *format,
char typcategory;
bool typispreferred;
- get_type_category_preferred(*exprtype, &typcategory, &typispreferred);
+ get_type_category_preferred(basetype, &typcategory, &typispreferred);
- if (*exprtype == UNKNOWNOID || typcategory == TYPCATEGORY_STRING)
+ if (basetype == UNKNOWNOID || typcategory == TYPCATEGORY_STRING)
{
- expr = coerce_to_target_type(pstate, (Node *) expr, *exprtype,
+ expr = coerce_to_target_type(pstate, (Node *) expr, basetype,
TEXTOID, -1,
COERCION_IMPLICIT,
COERCE_IMPLICIT_CAST, -1);
@@ -4103,11 +4105,14 @@ static Node *
transformJsonIsPredicate(ParseState *pstate, JsonIsPredicate *pred)
{
Oid exprtype;
+ Oid basetype;
+
Node *expr = transformJsonParseArg(pstate, pred->expr, pred->format,
&exprtype);
/* make resulting expression */
- if (exprtype != TEXTOID && exprtype != JSONOID && exprtype != JSONBOID)
+ basetype = getBaseType(exprtype);
+ if (basetype != TEXTOID && basetype != JSONOID && basetype != JSONBOID)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("cannot use type %s in IS JSON predicate",
@@ -4115,7 +4120,7 @@ transformJsonIsPredicate(ParseState *pstate, JsonIsPredicate *pred)
/* This intentionally(?) drops the format clause. */
return makeJsonIsPredicate(expr, NULL, pred->item_type,
- pred->unique_keys, pred->location);
+ pred->unique_keys, basetype, pred->location);
}
/*
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 5473ce9a288..449cd5b8e7e 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 oid, 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 1b4436f2ff6..6e30c1f8501 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1761,6 +1761,7 @@ typedef struct JsonIsPredicate
JsonFormat *format; /* FORMAT clause, if specified */
JsonValueType item_type; /* JSON item type */
bool unique_keys; /* check key uniqueness? */
+ Oid resultBaseType; /* result base type */
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..df1157c119d 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -1110,6 +1110,10 @@ CREATE OR REPLACE VIEW public.json_array_subquery_view AS
FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i)) q(a)) AS "json_array"
DROP VIEW json_array_subquery_view;
-- IS JSON predicate
+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');
SELECT NULL IS JSON;
?column?
----------
@@ -1148,6 +1152,124 @@ SELECT NULL::bytea IS JSON;
SELECT NULL::int IS JSON;
ERROR: cannot use type integer in IS JSON predicate
+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 '{"a": 1, "a": 2}'::jd1 IS JSON WITH UNIQUE KEYS;
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '{"a": 1, "a": 1}'::jd2 IS JSON WITH UNIQUE KEYS;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '{"a": 1, "a": 2}'::jd3 IS JSON WITH UNIQUE KEYS;
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '{"a": 1, "a": 2}'::jd4 IS JSON WITH UNIQUE KEYS;
+ ?column?
+----------
+ f
+(1 row)
+
+CREATE TEMP TABLE ts_domain(js text);
+INSERT INTO ts_domain VALUES ('{"a":3}'), ('{"a":"1"}'), ('a'), ('\x61');
+SELECT js::jd1 IS JSON FROM ts_domain;
+ERROR: value for domain jd1 violates check constraint "jd1_check"
+SELECT js::jd2 IS JSON FROM ts_domain;
+ERROR: value for domain jd2 violates check constraint "jd2_check"
+SELECT js::jd3 IS JSON FROM ts_domain;
+ERROR: value for domain jd3 violates check constraint "jd3_check"
+SELECT js::jd4 IS JSON FROM ts_domain;
+ERROR: value for domain jd4 violates check constraint "jd4_check"
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd1)) foo(js);
+ js | pg_typeof | object? | array? | array w. UK? | array w/o UK?
+--------------------------------+-----------+---------+--------+--------------+---------------
+ [{"a":"1"}, {"b":"2","b":"3"}] | jd1 | f | t | f | t
+(1 row)
+
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd2)) foo(js);
+ js | pg_typeof | object? | array? | array w. UK? | array w/o UK?
+--------------------------+-----------+---------+--------+--------------+---------------
+ [{"a": "1"}, {"b": "3"}] | jd2 | f | t | t | t
+(1 row)
+
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd3)) foo(js);
+ js | pg_typeof | object? | array? | array w. UK? | array w/o UK?
+--------------------------------+-----------+---------+--------+--------------+---------------
+ [{"a":"1"}, {"b":"2","b":"3"}] | jd3 | f | t | f | t
+(1 row)
+
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd4)) foo(js);
+ js | pg_typeof | object? | array? | array w. UK? | array w/o UK?
+----------------------------------------------------------------+-----------+---------+--------+--------------+---------------
+ \x5b7b2261223a2231227d2c207b2262223a2232222c2262223a2233227d5d | jd4 | f | t | f | t
+(1 row)
+
SELECT '' IS JSON;
?column?
----------
@@ -1206,6 +1328,33 @@ 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 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
js,
js IS JSON "IS JSON",
@@ -1233,6 +1382,46 @@ 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
+ 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);
+ERROR: value for domain jd2 violates check constraint "jd2_check"
SELECT
js0,
js IS JSON "IS JSON",
@@ -1260,6 +1449,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",
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 343d344d270..2b4f699667e 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -387,6 +387,11 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING
DROP VIEW json_array_subquery_view;
-- IS JSON predicate
+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');
+
SELECT NULL IS JSON;
SELECT NULL IS NOT JSON;
SELECT NULL::json IS JSON;
@@ -395,6 +400,58 @@ SELECT NULL::text IS JSON;
SELECT NULL::bytea IS JSON;
SELECT NULL::int IS JSON;
+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 '{"a": 1, "a": 2}'::jd1 IS JSON WITH UNIQUE KEYS;
+SELECT '{"a": 1, "a": 1}'::jd2 IS JSON WITH UNIQUE KEYS;
+SELECT '{"a": 1, "a": 2}'::jd3 IS JSON WITH UNIQUE KEYS;
+SELECT '{"a": 1, "a": 2}'::jd4 IS JSON WITH UNIQUE KEYS;
+
+CREATE TEMP TABLE ts_domain(js text);
+INSERT INTO ts_domain VALUES ('{"a":3}'), ('{"a":"1"}'), ('a'), ('\x61');
+
+SELECT js::jd1 IS JSON FROM ts_domain;
+SELECT js::jd2 IS JSON FROM ts_domain;
+SELECT js::jd3 IS JSON FROM ts_domain;
+SELECT js::jd4 IS JSON FROM ts_domain;
+
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd1)) foo(js);
+
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd2)) foo(js);
+
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd3)) foo(js);
+
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd4)) foo(js);
+
SELECT '' IS JSON;
SELECT bytea '\x00' IS JSON;
@@ -432,6 +489,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 WHERE js IS JSON) foo(js);
+
SELECT
js,
js IS JSON "IS JSON",
@@ -445,6 +515,32 @@ 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
+ 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);
+
SELECT
js0,
js IS JSON "IS JSON",
@@ -458,6 +554,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",
--
2.34.1
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT
@ 2025-11-18 17:01 Tom Lane <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2025-11-18 17:01 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: pgsql-hackers
jian he <[email protected]> writes:
> [ v1-0001-IS-JSON-predicate-work-with-domain-type.patch ]
This looks like a large patch with a small patch struggling to
get out of it. Why didn't you simply do
- *exprtype = exprType(expr);
+ *exprtype = getBaseType(exprType(expr));
in transformJsonParseArg?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT
@ 2025-12-02 15:03 jian he <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: jian he @ 2025-12-02 15:03 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
On Wed, Nov 19, 2025 at 1:01 AM Tom Lane <[email protected]> wrote:
>
> jian he <[email protected]> writes:
> > [ v1-0001-IS-JSON-predicate-work-with-domain-type.patch ]
>
> This looks like a large patch with a small patch struggling to
> get out of it. Why didn't you simply do
>
> - *exprtype = exprType(expr);
> + *exprtype = getBaseType(exprType(expr));
>
> in transformJsonParseArg?
>
yech.
While at it, I added parser_errposition to the transformJsonIsPredicate ereport.
errmsg("cannot use type %s in IS JSON predicate",
format_type_be(exprtype))
we don't need to worry about exprtype as InvalidOid, because
transformJsonParseArg (exprType(expr)) would fail already in that case.
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v2-0001-IS-JSON-predicate-work-with-domain-type.patch (22.9K, 2-v2-0001-IS-JSON-predicate-work-with-domain-type.patch)
download | inline diff:
From b2bda451b3275fa35fe9d2f270a9b489db0ed024 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 2 Dec 2025 23:00:22 +0800
Subject: [PATCH v2 1/1] IS JSON predicate work with domain type
IS JSON predicate work with domain base type is TEXT, JSON, JSONB, BYTEA.
https://commitfest.postgresql.org/patch/6237/
discussion: https://postgr.es/m/CACJufxEk34DnJFG72CRsPPT4tsJL9arobX0tNPsn7yH28J=zQg@mail.gmail.com
---
src/backend/executor/execExprInterp.c | 2 +
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 | 224 ++++++++++++++++++++++++++
src/test/regress/sql/sqljson.sql | 118 ++++++++++++++
8 files changed, 356 insertions(+), 9 deletions(-)
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 0e1a74976f7..5580fc8e0e4 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4746,6 +4746,8 @@ ExecEvalJsonIsPredicate(ExprState *state, ExprEvalStep *op)
}
exprtype = exprType(pred->expr);
+ if (exprtype != pred->resultBaseType)
+ exprtype = pred->resultBaseType;
if (exprtype == TEXTOID || exprtype == JSONOID)
{
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index e2d9e9be41a..18cfa6df4a7 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 c3a0a354a9c..fa70761c06f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -15522,7 +15522,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
@@ -15531,7 +15531,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
@@ -15540,7 +15540,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
@@ -15550,7 +15550,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 44fd1385f8c..b1acd16df4b 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4056,7 +4056,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)
@@ -4111,11 +4111,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 5473ce9a288..09a64bf5d05 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 1b4436f2ff6..b8ee6cf68f7 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1761,6 +1761,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 expr */
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..8ac12dc37a9 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -1110,6 +1110,11 @@ CREATE OR REPLACE VIEW public.json_array_subquery_view AS
FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i)) q(a)) AS "json_array"
DROP VIEW json_array_subquery_view;
-- IS JSON predicate
+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 IS JSON;
?column?
----------
@@ -1148,6 +1153,131 @@ 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;
+ ^
+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;
+ ^
+CREATE VIEW domain_isjson AS
+WITH cte(a) as (VALUES('{"a": 1, "a": 2}'))
+SELECT a::jd1 IS JSON WITH UNIQUE KEYS as col1,
+ a::jd3 IS JSON WITH UNIQUE KEYS as col2,
+ a::jd4 IS JSON WITH UNIQUE KEYS as col3
+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 col1,
+ a::jd3 IS JSON WITH UNIQUE KEYS AS col2,
+ a::jd4 IS JSON WITH UNIQUE KEYS AS col3
+ FROM cte
+SELECT * FROM domain_isjson;
+ col1 | col2 | col3
+------+------+------
+ f | f | f
+(1 row)
+
+CREATE TEMP TABLE ts_domain(js text);
+INSERT INTO ts_domain VALUES ('{"a":3}'), ('{"a":"1"}'), ('a'), ('\x61');
+SELECT js::jd1 IS JSON FROM ts_domain;
+ERROR: value for domain jd1 violates check constraint "jd1_check"
+SELECT js::jd2 IS JSON FROM ts_domain;
+ERROR: value for domain jd2 violates check constraint "jd2_check"
+SELECT js::jd3 IS JSON FROM ts_domain;
+ERROR: value for domain jd3 violates check constraint "jd3_check"
+SELECT js::jd4 IS JSON FROM ts_domain;
+ERROR: value for domain jd4 violates check constraint "jd4_check"
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd1)) foo(js);
+ js | pg_typeof | object? | array? | array w. UK? | array w/o UK?
+--------------------------------+-----------+---------+--------+--------------+---------------
+ [{"a":"1"}, {"b":"2","b":"3"}] | jd1 | f | t | f | t
+(1 row)
+
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd2)) foo(js);
+ js | pg_typeof | object? | array? | array w. UK? | array w/o UK?
+--------------------------+-----------+---------+--------+--------------+---------------
+ [{"a": "1"}, {"b": "3"}] | jd2 | f | t | t | t
+(1 row)
+
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd3)) foo(js);
+ js | pg_typeof | object? | array? | array w. UK? | array w/o UK?
+--------------------------------+-----------+---------+--------+--------------+---------------
+ [{"a":"1"}, {"b":"2","b":"3"}] | jd3 | f | t | f | t
+(1 row)
+
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd4)) foo(js);
+ js | pg_typeof | object? | array? | array w. UK? | array w/o UK?
+----------------------------------------------------------------+-----------+---------+--------+--------------+---------------
+ \x5b7b2261223a2231227d2c207b2262223a2232222c2262223a2233227d5d | jd4 | f | t | f | t
+(1 row)
+
SELECT '' IS JSON;
?column?
----------
@@ -1206,6 +1336,33 @@ 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 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
js,
js IS JSON "IS JSON",
@@ -1233,6 +1390,46 @@ 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
+ 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);
+ERROR: value for domain jd2 violates check constraint "jd2_check"
SELECT
js0,
js IS JSON "IS JSON",
@@ -1260,6 +1457,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",
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 343d344d270..88d6bc37333 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -387,6 +387,12 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING
DROP VIEW json_array_subquery_view;
-- IS JSON predicate
+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 IS JSON;
SELECT NULL IS NOT JSON;
SELECT NULL::json IS JSON;
@@ -395,6 +401,66 @@ SELECT NULL::text IS JSON;
SELECT NULL::bytea IS JSON;
SELECT NULL::int IS JSON;
+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
+
+CREATE VIEW domain_isjson AS
+WITH cte(a) as (VALUES('{"a": 1, "a": 2}'))
+SELECT a::jd1 IS JSON WITH UNIQUE KEYS as col1,
+ a::jd3 IS JSON WITH UNIQUE KEYS as col2,
+ a::jd4 IS JSON WITH UNIQUE KEYS as col3
+FROM cte;
+
+\sv domain_isjson
+SELECT * FROM domain_isjson;
+
+CREATE TEMP TABLE ts_domain(js text);
+INSERT INTO ts_domain VALUES ('{"a":3}'), ('{"a":"1"}'), ('a'), ('\x61');
+
+SELECT js::jd1 IS JSON FROM ts_domain;
+SELECT js::jd2 IS JSON FROM ts_domain;
+SELECT js::jd3 IS JSON FROM ts_domain;
+SELECT js::jd4 IS JSON FROM ts_domain;
+
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd1)) foo(js);
+
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd2)) foo(js);
+
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd3)) foo(js);
+
+SELECT js,
+ pg_typeof(js),
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?",
+ js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
+ js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
+FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]'::jd4)) foo(js);
+
SELECT '' IS JSON;
SELECT bytea '\x00' IS JSON;
@@ -432,6 +498,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 WHERE js IS JSON) foo(js);
+
SELECT
js,
js IS JSON "IS JSON",
@@ -445,6 +524,32 @@ 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
+ 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);
+
SELECT
js0,
js IS JSON "IS JSON",
@@ -458,6 +563,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",
--
2.34.1
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT
@ 2026-03-13 03:55 jian he <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: jian he @ 2026-03-13 03:55 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
Hi.
The regression test was very verbose; I removed some of it.
Also polished function ExecEvalJsonIsPredicate a little bit.
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v3-0001-IS-JSON-predicate-work-with-domain-type.patch (22.6K, 2-v3-0001-IS-JSON-predicate-work-with-domain-type.patch)
download | inline diff:
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=zQg@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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT
@ 2026-03-13 13:16 Kirill Reshke <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Kirill Reshke @ 2026-03-13 13:16 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: jian he <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Fri, 13 Mar 2026 at 17:30, Andrew Dunstan <[email protected]> wrote:
>
>
> On 2026-03-12 Th 11:55 PM, jian he wrote:
> > Hi.
> >
> > The regression test was very verbose; I removed some of it.
> > Also polished function ExecEvalJsonIsPredicate a little bit.
>
>
>
> Here's a v4. I changed resultBaseType to exprBaseType - I think it's
> clearer. I also trimmed the tests a bit more, and dropped the new
> objects after testing them. The error message now shows the domain name
> rather than the underlying base type. I think that's more useful.
>
Hi!
V4 looks good. The only thing that I cannot explain is removing the
`exprtype` variable inside ExecEvalJsonIsPredicate. We can just change
its declaration to
exprtype = pred->exprBaseType if i'm not mistaken?
Anyway, LGTM
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT
@ 2026-03-13 13:47 Andrew Dunstan <[email protected]>
parent: Kirill Reshke <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Andrew Dunstan @ 2026-03-13 13:47 UTC (permalink / raw)
To: Kirill Reshke <[email protected]>; +Cc: jian he <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On 2026-03-13 Fr 9:16 AM, Kirill Reshke wrote:
> On Fri, 13 Mar 2026 at 17:30, Andrew Dunstan <[email protected]> wrote:
>>
>> On 2026-03-12 Th 11:55 PM, jian he wrote:
>>> Hi.
>>>
>>> The regression test was very verbose; I removed some of it.
>>> Also polished function ExecEvalJsonIsPredicate a little bit.
>>
>>
>> Here's a v4. I changed resultBaseType to exprBaseType - I think it's
>> clearer. I also trimmed the tests a bit more, and dropped the new
>> objects after testing them. The error message now shows the domain name
>> rather than the underlying base type. I think that's more useful.
>>
>
> Hi!
> V4 looks good. The only thing that I cannot explain is removing the
> `exprtype` variable inside ExecEvalJsonIsPredicate. We can just change
> its declaration to
>
> exprtype = pred->exprBaseType if i'm not mistaken?
Yeah, we can do that.
>
> Anyway, LGTM
>
>
Thanks for reviewing.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT
@ 2026-03-17 19:23 Andrew Dunstan <[email protected]>
parent: Andrew Dunstan <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Andrew Dunstan @ 2026-03-17 19:23 UTC (permalink / raw)
To: Kirill Reshke <[email protected]>; +Cc: jian he <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On 2026-03-13 Fr 9:47 AM, Andrew Dunstan wrote:
>
> On 2026-03-13 Fr 9:16 AM, Kirill Reshke wrote:
>> On Fri, 13 Mar 2026 at 17:30, Andrew Dunstan <[email protected]>
>> wrote:
>>>
>>> On 2026-03-12 Th 11:55 PM, jian he wrote:
>>>> Hi.
>>>>
>>>> The regression test was very verbose; I removed some of it.
>>>> Also polished function ExecEvalJsonIsPredicate a little bit.
>>>
>>>
>>> Here's a v4. I changed resultBaseType to exprBaseType - I think it's
>>> clearer. I also trimmed the tests a bit more, and dropped the new
>>> objects after testing them. The error message now shows the domain
>>> name
>>> rather than the underlying base type. I think that's more useful.
>>>
>>
>> Hi!
>> V4 looks good. The only thing that I cannot explain is removing the
>> `exprtype` variable inside ExecEvalJsonIsPredicate. We can just change
>> its declaration to
>>
>> exprtype = pred->exprBaseType if i'm not mistaken?
>
>
>
> Yeah, we can do that.
>
>
>>
>> Anyway, LGTM
>>
>>
>
>
Committed with that change.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-03-17 19:23 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-18 15:43 IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT jian he <[email protected]>
2025-11-18 17:01 ` Tom Lane <[email protected]>
2025-12-02 15:03 ` jian he <[email protected]>
2026-03-13 03:55 ` jian he <[email protected]>
2026-03-13 13:16 ` Kirill Reshke <[email protected]>
2026-03-13 13:47 ` Andrew Dunstan <[email protected]>
2026-03-17 19:23 ` Andrew Dunstan <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox