CODEWITHSUNDEEP
sqlsql-servert-sqljson-query
Luki
Luki

Reputation: 419

Transact SQL union all on json results escaping nested json queries

I am having a problem with union operation on json results as for some reason my nested json queries results get their double quotes escaped which causes parse errors in my application. Here Is the example:

SELECT DISTINCT s.*,
JSON_QUERY((select * from SomeTable WHERE Id = 'some ID' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS ExampleColumn from [SomeOtherTable] sot where sot.Id = 'some other ID' 
UNION ALL
SELECT DISTINCT S1.*, JSON_QUERY((select * from SomeTable WHERE Id = 'some other other ID' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS ExampleColumn from [SomeOtherTable] sot1 where sot1.Id = 'some other other other ID' FOR JSON PATH

The result is correct, only the nested JSON_QUERY results have escaped double quotes. Can anyone help me please?

Upvotes: 3

Views: 5143

Answers (1)

Serkan Arslan
Serkan Arslan

Reputation: 13393

Your query converts json to text, that's why second json threads them as string and adds escaped double quotes.

You can try this. I hope it helps you.

SELECT STRING_AGG (JsonText,',') FROM (
    SELECT ( SELECT DISTINCT sot.*, ExampleColumn.*
             FROM 
                [SomeOtherTable] sot 
                OUTER APPLY(select * from SomeTable WHERE Id = 'some ID') AS ExampleColumn 
             WHERE 
                sot.Id = 'some other ID' FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER ) JsonText

    UNION ALL

    SELECT ( SELECT DISTINCT sot1.*, ExampleColumn.*
             FROM 
                [SomeOtherTable] sot1 
                OUTER APPLY((select * from SomeTable WHERE Id = 'some other other ID')) AS ExampleColumn 
             WHERE sot1.Id = 'some other other other ID' FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER ) JsonText
)

Upvotes: 2

Related Questions