0

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" }
    }
]
1
  • 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.
    – Mitch
    Aug 18, 2022 at 23:47

1 Answer 1

2

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" }
  }
]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.