I have this query in which I’m trying to store the query output into @json
variable but getting syntax error
x
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"}]}}