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

Answer 1

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

Answer 2

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":',
        ''
    ),
    '}',
    ''
) +
'}'