Possible Bug in JSON_QUERY() Behavior in PostgreSQL 17 - Mailing list pgsql-bugs

Dear PostgreSQL Development Team,

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.ds@gmail.com