[SOLVED] SQL Server For JSON returns object as a string

SQL Server For JSON returns object as a string

I have the following sql code:

SELECT 
definitions = JSON_QUERY('{"ID": "INT)", "ID": "VARCHAR(23)"}'),
data = 
(
    SELECT ID, ID_FOR_DISPLAY
    FROM (SELECT TOP 1 ID, dbo.A2F_0012_ReturnIDforDisplay(1) as ID_FOR_DISPLAY FROM RateReviewBaseTBL) v(ID, ID_FOR_DISPLAY)
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

This came from an example I found which utilized the following: https://sqlfiddle.com/sql-server/online-compiler?id=ce2d52e2-d1f7-4d9c-8d02-a7d6fc062b7d

I copied this structure almost exactly except for some reason my code returns the object in a string format:

{"definitions":{"ID": "INT)", "ID_FOR_DISPLAY": "VARCHAR(23)"},"data":"{\"ID\":128,\"ID_FOR_DISPLAY\":\"NM-00000001\"}"}

Its not clear why its making my object a string rather than the example which seems to show the object as an object with {} and no strings around it.

However, I want the results to show this:

{"definitions":{"ID": "INT)", "ID_FOR_DISPLAY": "VARCHAR(23)"},"data":{"ID":128,"ID_FOR_DISPLAY":"NM-00000001"}}

Solution

As siggemannen notes just wrap your subquery in JSON_QUERY. This is the query from the fiddle, as I cannot run your query due to no sample data or definitions

SELECT JSON_QUERY('{"NAME": "VARCHAR(23)", "DESCRIPTION": "VARCHAR(23)"}') AS definitions,
       JSON_QUERY((SELECT NAME,
                          DESCRIPTION
                   FROM (SELECT TOP (1)
                                Name,
                                Description
                         FROM dbo.Product
                         ORDER BY <Expression(s)>) v(NAME, DESCRIPTION)
                  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS data
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

db<>fiddle