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

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?

CodePudding user response：

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;