Table to JSON with specified format

Question

I have the following query and trying for the JSON format specified below:

SELECT
    Convert(VARCHAR(10),[Date],110) HolidayDate
FROM
    dbo.Holidays FOR JSON AUTO, ROOT('Holidays')

Current Output

{
  "Holidays": [
    {
      "HolidayDate": "05-21-2018"
    },
    {
      "HolidayDate": "05-22-2018"
    },
    {
      "HolidayDate": "05-26-2018"
    }
  ]
}

Expected Output

{
  "Holidays": [
    "05-21-2018",
    "05-22-2018"
  ]
}

SQL-Server developers did not include this feature seemingly... I would be happy with a `SELECT SomeColumn AS [*] FOR JSON PATH` (similar to nameless nodes in `FOR XML`) in order to tell the engine, that the value should be a *plain-type-array* without a key. Might be this will follow in a future version. — Shnugo, May 28 '18 at 13:05

score 0 · Answer 1 · answered May 28 '18 at 06:43

You can remove the unwanted object properties with replace, leaving only an array.

select '{"Holidays":' +
select replace(
    replace(
        (
            select convert(varchar(10), [date], 110) HolidayDate
            from dbo.Holidays
            for json auto
        ),
        '{"HolidayDate":',
        ''
    ),
    '}',
    ''
) +
'}'

Andrea · Answer 2 · 2018-05-28T10:35:58.963

In SQL Server 2017 you can build your result with a combination of string_agg, json_query and for json path:

select json_query(QUOTENAME(STRING_AGG('"' 
              + STRING_ESCAPE( Convert(VARCHAR(10),[Date],110) , 'json') 
              + '"', char(44)))) as Holidays
            from  dbo.Holidays 
            for json path , WITHOUT_ARRAY_WRAPPER

In SQL Server 2016 the solution is less elegant, but still working:

declare @cat nvarchar(max)='{"Holidays":'

select  @cat = @cat+ QUOTENAME(STRING_AGG('"' 
              + STRING_ESCAPE( Convert(VARCHAR(10),[Date],110) , 'json') 
              + '"', char(44))) from dbo.Holidays 

select  @cat + '}'

Results:

{
  "Holidays": [
    "05-21-2018",
    "05-22-2018",
    "05-26-2018"
  ]
}

Table to JSON with specified format

2 Answers2