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