Kindly find the below attachments for reference
On Thu, Feb 20, 2025 at 4:31 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.
>
>
> Best regards,
>
> SUSMITHA,
>
> CDAC-CHENNAI
>
> CONTACT:susmithaselvarani(dot)ds(at)gmail(dot)com
> [image: image.png]
>
> [image: image.png]
>
>
>
>
