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

Question

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

How doesn't it work?

Martin
– Martin

2019-11-21 13:45:34 +00:00
Commented Nov 21, 2019 at 13:45 — Martin
– Martin, Commented Nov 21, 2019 at 13:45
What is the expected output?

Zhorov
– Zhorov

2019-11-21 13:48:39 +00:00
Commented Nov 21, 2019 at 13:48 — Zhorov
– Zhorov, Commented Nov 21, 2019 at 13:48

Zhorov · Accepted Answer · 2019-11-21 14:09:35Z

6

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

edited Nov 21, 2019 at 14:09

answered Nov 21, 2019 at 13:49

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related