Re: Possible Bug in JSON_QUERY() Behavior in PostgreSQL 17

On Thu, Feb 20, 2025 at 7:22 PM Susmitha S <susmithaselvarani(dot)ds(at)gmail(dot)com>
wrote:

> *Dear PostgreSQL Development Team,*
> *Environment Details:*
>
> - *PostgreSQL Version:* 17.3
> - *Operating System:Linux*
>
> I would like to report a possible issue with the JSON_QUERY() function in
> PostgreSQL 17.3, which I encountered during testing.
> *Issue Description:*
>
> While working with JSON_QUERY(), I observed inconsistent behavior when
> handling missing or multiple values in JSON paths. Below are two specific
> cases where the function does not behave as expected:
>
> 1.
>
> *Multiple Values Without Wrapper:*
> - When using ERROR ON ERROR with a JSON path that returns multiple
> values, the query correctly throws an error.
> - However, when the ERROR ON ERROR clause is omitted, instead of
> throwing an error or returning a meaningful response, it simply returns an *empty
> result*.
> - *Query:*
>
> SELECT JSON_QUERY(
> data,
> '$.profile.contacts.email[*]'
> ERROR ON ERROR
> ) FROM users;
>
> - *Expected Behavior:* Without ERROR ON ERROR, it should either
> return an array or provide a clear error message, instead of an empty
> response.
> 2.
>
> *Handling Missing Keys with DEFAULT Clause:*
> - When a JSON path does not exist in the input JSON, using the DEFAULT
> ... ON EMPTY clause correctly returns the default value.
> - However, if the DEFAULT ... ON EMPTY clause is *not* specified,
> instead of an explicit error or NULL, the function simply returns an *empty
> result*.
> - *Query:*
>
> SELECT JSON_QUERY(
> '{"a": 1}',
> '$.b'
> DEFAULT '{"status": "not_found"}' ON EMPTY
> );
>
> - *Expected Behavior:* Without DEFAULT ... ON EMPTY, the function
> should return NULL or an error indicating the missing key, instead
> of an empty response.
>
>
> *Suggested Fix:*
>
> - Ensure JSON_QUERY() returns a meaningful response instead of an
> empty result when dealing with missing paths or multiple values without a
> wrapper.
> - If an empty result is intended behavior, update the documentation to
> clarify this case.
>
>
> See https://www.postgresql.org/docs/current/functions-json.html

-

For both ON EMPTY and ON ERROR, specifying ERROR will cause an error to
be thrown with the appropriate message. Other options include returning an
SQL NULL, an empty array (EMPTY [ARRAY]), an empty object (EMPTY OBJECT),
or a user-specified expression (DEFAULT *expression*) that can be
coerced to jsonb or the type specified in RETURNING. The default when ON
EMPTY or ON ERROR is not specified is to return an SQL NULL value.

The last sentence states the behavior.

--
Regards
Junwang Zhao