Skip to content
Advertisement

SQL – How to store JSON_QUERY output to variable

I have this query in which I’m trying to store the query output into @json variable but getting syntax error

DECLARE @json NVARCHAR(MAX)

SELECT @json = (JSON_QUERY((SELECT 
                                'Ball' AS title, 
                                'Blue' AS color, 
                                (SELECT 
                                     '1' AS Opt1, 
                                     '2' AS Opt2 
                                 FOR JSON PATH) AS Options
                            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Product
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;)

SELECT @json

Error:

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword ‘AS’.

How can I do that?

Advertisement

Answer

Following VBokšić’s link I made this query as answer:

DECLARE @json NVARCHAR(MAX)

SET @json = (SELECT (JSON_QUERY((
    SELECT 'Ball' AS title, 
           'Blue' AS color, (
                SELECT '1' AS Opt1, 
                       '2' AS Opt2 
                FOR JSON PATH
           ) AS Options
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ))) AS product
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)

SELECT @json

Output:

{"product":{"title":"Ball","color":"Blue","Options":[{"Opt1":"1","Opt2":"2"}]}}
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement