CODEWITHSUNDEEP
sqljsonsql-servert-sql
AndyDaSilva52
AndyDaSilva52

Reputation: 159

SQL to JSON - array of objects to array of values with UNION ALL

How to make the query below work?

I used the concept from another question SQL Server 2016 JSON: Select array of strings instead of array of objects

But when I tried the select below it doesn't work

SELECT
    (SELECT line AS "line"  
     FROM
         (SELECT 
              CONCAT('5th', ' ', '566') AS "line"
          UNION ALL
          SELECT 'Complement' AS LINE
         )
     FOR JSON PATH) AS "address.lines"

The version of the SQL Server SELECT @@VERSION = Microsoft SQL Server 2016

The result from the query is

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'FOR'.

The excepted result is [{"line":"5th 566"},{"line":"Complement"}], with this result I will use the function from SQL Server 2016 JSON: Select array of strings instead of array of objects to remove "line" and get the final result ["5th 566","Complement"]

I need to use the UNION ALL because I have two different values from the same table to result in an array called address.lines at the JSON result

Upvotes: 0

Views: 860

Answers (1)

Zhorov
Zhorov

Reputation: 29943

If I understand your question correctly, you just need an alias (t) to solve this error:

Statement:

SELECT (
    SELECT [Line] FROM (
        SELECT CONCAT('5th', ' ',  '566') AS [Line]
        UNION ALL
        SELECT 'Complement' AS [Line]
    ) t
    FOR JSON PATH
)
AS "address.lines"

Output:

address.lines
[{"Line":"5th 566"},{"Line":"Complement"}]

Of course, you can use only JSON functions to generate the final JSON output:

Statement:

DECLARE @json nvarchar(max) = N'[]'
SELECT @json = JSON_MODIFY(@json, 'append $', [Line])
FROM (
   SELECT CONCAT('5th', ' ',  '566') AS [Line]
   UNION ALL
   SELECT 'Complement' AS [Line]
) t
SELECT @json AS [address.lines]

Output:

address.lines
["5th 566","Complement"]

Upvotes: 6

Related Questions