SQL - How to store JSON_QUERY output to variable

Question

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?

Could this be of help: stackoverflow.com/questions/41957850/… — VBoka
– VBoka, Commented Oct 31, 2019 at 19:08

hoggar · Accepted Answer · 2019-10-31 21:20:50Z

6

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

edited Oct 31, 2019 at 21:20

answered Oct 31, 2019 at 19:21

hoggar

3,9076 gold badges34 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VBoka Over a year ago

And I will give you an up vote for your effort :) I am happy that it helped.

Collectives™ on Stack Overflow

SQL - How to store JSON_QUERY output to variable

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related