Transact SQL union all on json results escaping nested json queries

Question

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?

Could you show the whole query? Can you reproduce the issue with some sample data? It will be really easy to get answer if you do so. — gotqn
– gotqn, Commented Oct 10, 2017 at 6:25

Serkan Arslan · Accepted Answer · 2017-10-10 09:26:28Z

2

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
)

answered Oct 10, 2017 at 9:26

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Transact SQL union all on json results escaping nested json queries

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related