Embed column containing JSON in FOR JSON AUTO

Question

If I have a table with a column containing an arbitrary valid JSON document, can I embed that document into a query returing JSON other than as a string?

For example:

CREATE TABLE #Example (Name nvarchar(50) not null, Document nvarchar(max) not null);
INSERT INTO #Example VALUES 
 ('Document 1', '{ "a": "a" }'),
 ('Document 2', '{ "b": "b" }');
SELECT *
FROM #Example
FOR JSON AUTO;

Actual output:

[
 {
 "Name":"Document 1",
 "Document":"{ \"a\": \"a\" }"
 },
 {
 "Name":"Document 2",
 "Document":"{ \"b\": \"b\" }"
 }
]

Desired output (note that the parsed value of Document has been embedded):

[
 {
 "Name":"Document 1",
 "Document": { "a": "a" }
 },
 {
 "Name":"Document 2",
 "Document": { "b": "b" }
 }
]

• Mitch: Note: I don't think dba.stackexchange.com/q/115670/16496 is a duplicate since it is troubleshooting the fact that JSON_VALUE doesn't exist in previous versions - though it does refer to this issue. The referenced JSON_VALUE(D.DATA,'$') does not work, and I can't find any blog referring to that.

Answer 1

Use JSON_QUERY with no path to prevent escaping

drop table if exists #Example
CREATE TABLE #Example (Name nvarchar(50) not null, Document nvarchar(max) not null);
INSERT INTO #Example VALUES 
 ('Document 1', '{ "a": "a" }'),
 ('Document 2', '{ "b": "b" }');
SELECT name, JSON_QUERY(Document) Document
FROM #Example
FOR JSON AUTO;

outputs

[
 {
 "name":"Document 1",
 "Document":{ "a": "a" }
 },
 {
 "name":"Document 2",
 "Document":{ "b": "b" }
 }
]