sql - Transact SQL union all on json 结果转义嵌套的 json 查询

Question

我在对 json 结果进行联合操作时遇到问题，因为出于某种原因，我的嵌套 json 查询结果的双引号被转义，这导致我的应用程序出现解析错误。这是示例:

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

结果是正确的，只有嵌套的 JSON_QUERY 结果转义了双引号。谁能帮帮我？

Answer 1

您的查询将 json 转换为文本，这就是为什么第二个 json 将它们作为字符串进行线程化并添加转义双引号。

你可以试试这个。希望对您有所帮助。

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
)