'MS SQL - Trying to use FOR JSON to output with square brackets for each value

I am using MSSQL to try to get a JSON output in a specific format, with square brackets around individual values. Here is part of the query:

SELECT DISTINCT 
    (SELECT Email_Address AS fieldValues
     FROM table1
     FOR JSON PATH) records
FROM table1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

This is the result:

{
    "records": [
        {
            "fieldValues": "[email protected]"
        }
    ]
}

I need to have square brackets around the email address so it looks like this:

{
    "records": [
        {
            "fieldValues": [
                 "[email protected]"
             ]
        }
    ]
}

Is there any way to do this?

sqlarraysjsonsql-server


Solution 1:[1]

Serializing of JSON arrays in SQL server is a little bit cumbersome and requires things like using JSON_QUERY() over STRING_AGG() output, e.g.:

select
  (
    select
      json_query('['+(
        select string_agg(quotename(Email_Address, '"'), ',')
        from table1
      )+']', '$') as [fieldValues]
      for json path
  ) as [records]
for json path, without_array_wrapper;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 AlwaysLearning

Related Questions