babelfish-for-postgresql / babelfish_extensions / 13906459436

77%

push

github

Select "for json" output contains Backsla- Resolve improper oid handling (#3569)

FOR JSON is a SQL clause that formats query results as JSON. Normally, it escapes quotes in strings to ensure valid JSON output. However, there's a special case we need to handle: when the input string is already the output of a JSON function, we want to avoid this escaping.

In this PR, we're addressing this issue. Here are the key changes we've made:

We've introduced a new domain type called 'nvarchar_json'. This allows us to differentiate between regular string output and output from JSON functions.

We've updated the return type of the JSON_QUERY system function from NVARCHAR to NVARCHAR_JSON. This change helps identify when a string is already in JSON format.

We've modified how FOR JSON handles input from JSON_QUERY. Specifically, we're now providing a JSONOID for these inputs, which signals that they should be treated differently from regular strings.

These changes allow FOR JSON to recognize when a string is already in JSON format (coming from a JSON function like JSON_QUERY), and avoid unnecessary escaping in these cases. This results in more accurate and efficient JSON output when working with nested JSON structures

During the implementation we found out more existing issues( to be handle in future) with escaping-

Nested FOR JSON + JSON FUNCS case
FOR JSON + JSON_MODIFY
FOR JSON AUTO doesn't work properly for all cases

Issues Resolved: BABEL-5112
Signed-off-by: Herambh Shah <herambhs@amazon.com>

7 of 7 new or added lines in 1 file covered. (100.0%)

47373 of 63135 relevant lines covered (75.03%)

188159.64 hits per line