Horton

Thread

Commits

  1. Allow IS JSON predicate to work with domain types

  2. SQL/JSON: support the IS JSON predicate

  1. IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT

    jian he <jian.universality@gmail.com> — 2025-11-18T15:43:13Z

    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/

  2. Re: IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-11-18T17:01:19Z

    jian he <jian.universality@gmail.com> 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

  3. Re: IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT

    jian he <jian.universality@gmail.com> — 2025-12-02T15:03:47Z

    On Wed, Nov 19, 2025 at 1:01 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> jian he <jian.universality@gmail.com> 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/

  4. Re: IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT

    jian he <jian.universality@gmail.com> — 2026-03-13T03:55:44Z

    Hi.

The regression test was very verbose; I removed some of it.
Also polished function ExecEvalJsonIsPredicate a little bit.



--
jian
https://www.enterprisedb.com/

  5. Re: IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT

    Andrew Dunstan <andrew@dunslane.net> — 2026-03-13T12:30:27Z

    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.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com

  6. Re: IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT

    Kirill Reshke <reshkekirill@gmail.com> — 2026-03-13T13:16:34Z

    On Fri, 13 Mar 2026 at 17:30, Andrew Dunstan <andrew@dunslane.net> 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

  7. Re: IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT

    Andrew Dunstan <andrew@dunslane.net> — 2026-03-13T13:47:52Z

    On 2026-03-13 Fr 9:16 AM, Kirill Reshke wrote:
> On Fri, 13 Mar 2026 at 17:30, Andrew Dunstan <andrew@dunslane.net> 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

  8. Re: IS JSON predicate support for domain base type as JSON/JSONB/BYTEA/TEXT

    Andrew Dunstan <andrew@dunslane.net> — 2026-03-17T19:23:07Z

    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 <andrew@dunslane.net> 
>> 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