SQL Server 2016 Json result structure to array

Question

This is my table and data

My Query:

SELECT [Name]
  FROM [TestDB].[dbo].[MyCategory]
 WHERE [Category] = 'A'
   FOR JSON AUTO

Result:

[
   {"Name": "John"},
   {"Name": "Mary"},
   {"Name": "Dick"}
]

But I want to get the result like:

["John", "Mary", "Dick"]

Look at Magne Rekdal's answer here: https://stackoverflow.com/questions/37708638/sql-to-json-array-of-objects-to-array-of-values-in-sql-2016 — Santiago, Aug 10 '17 at 19:07

score 0 · Answer 1 · answered Jul 31 '17 at 06:40

0

declare @stra varchar (200)
set @stra = ''

select  top 5 @stra = 
    case when @stra = ''
    then '["'+ Name
    else @stra + coalesce('","' + Name, '')
    end
  from [TestDB].[dbo].[MyCategory]

  set @stra= @stra +'"]'
print @stra

answered Jul 31 '17 at 06:40

Trushna

110
6

1

`SELECT @v = @v + ...` could be undefined and it is not safe method for string concatenation. – Lukasz Szozda Jul 31 '17 at 06:41

score 0 · Answer 2 · answered Jul 31 '17 at 08:16

I do not think that this JSON format is supported in FOR JSON clause, see this article, although the string ["John","Mary","Dick"] is considered valid json using ISJSON function.

Your only way probably would be to modify the output yourself like this:

SET NOCOUNT ON

DECLARE @MyCategory TABLE (ID int , Category char(1), [Name] varchar(50))

INSERT INTO @MyCategory VALUES (1, 'A', 'John')
INSERT INTO @MyCategory VALUES (2, 'A', 'Mary')
INSERT INTO @MyCategory VALUES (3, 'A', 'Dick')
INSERT INTO @MyCategory VALUES (4, 'B', 'Sam')

DECLARE @MyJSON varchar(max) 
SELECT @MyJSON =
REPLACE(
REPLACE(
(
 SELECT [Name] AS ReplaceMe
  FROM @MyCategory
 WHERE [Category] = 'A'
   FOR JSON AUTO
   ), '{"ReplaceMe":',''
   ) , '}','')

PRINT @MyJSON
PRINT ISJSON(@MyJSON)

Outputs to:

["John","Mary","Dick"]
1

score 0 · Answer 3 · answered Aug 10 '17 at 19:18

Based on Magne Rekdal's answer to SQL to JSON - array of objects to array of values in SQL 2016

SELECT REPLACE(
          REPLACE((
               SELECT [Name]
               FROM [TestDB].[dbo].[MyCategory]
               WHERE [Category] = 'A'
               FOR JSON AUTO), '{"Name":','')
               '}','')

SQL Server 2016 Json result structure to array

3 Answers3