Possible Bug in JSON_QUERY() Behavior in PostgreSQL 17

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

Best regards,

SUSMITHA,

CDAC-CHENNAI

CONTACT:susmithaselvarani(dot)ds(at)gmail(dot)com
[image: image.png]

[image: image.png]