public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: Tom Lane <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT
Date: Fri, 13 Mar 2026 11:55:44 +0800
Message-ID: <CACJufxF_9FXULmC8fqCNE9H734_RmJ_1jboBpAmL8r-2wO=HOg@mail.gmail.com> (raw)
In-Reply-To: <CACJufxEDN24yBCkFw9sgv2Ocoq=TAtydcJ1ZLR3PEb_NJnTfqA@mail.gmail.com>
References: <CACJufxEk34DnJFG72CRsPPT4tsJL9arobX0tNPsn7yH28J=zQg@mail.gmail.com>
<[email protected]>
<CACJufxEDN24yBCkFw9sgv2Ocoq=TAtydcJ1ZLR3PEb_NJnTfqA@mail.gmail.com>
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
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT
In-Reply-To: <CACJufxF_9FXULmC8fqCNE9H734_RmJ_1jboBpAmL8r-2wO=HOg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox