Possible Bug in JSON_QUERY() Behavior in PostgreSQL 17

Kindly find the below attachments for reference

On Thu, Feb 20, 2025 at 4:31 PM Susmitha S <susmithaselvarani.ds@gmail.com>
wrote:

On Thu, Feb 20, 2025 at 7:22 PM Susmitha S <susmithaselvarani.ds@gmail.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

On Thursday, February 20, 2025, Susmitha S <susmithaselvarani.ds@gmail.com>
wrote:

1.
- 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*.

[image: image.png]

The meaning of “empty result” in PostgreSQL is a result set with zero
rows. As shown here you get a result/rows, their contents are just null
(consider using \pset null ‘<null>’ or some such if you want to distinguish
empty string from nulls).

As already mentioned, the production of a null value for non-matches is the
documented behavior.

David J.