how to Convert Table to Json Arrays?

Question

I'm using Sql Server 2016 and I want to convert a table to json.

I have a simple Table :

CREATE TABLE [dbo].[TableTmp](
    [Color] [nvarchar](50) NULL,
    [Type] [nvarchar](50) NULL,
    [Number] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TableTmp] ([Color], [Type], [Number]) VALUES (N'Blue', N'A', N'1')
GO
INSERT [dbo].[TableTmp] ([Color], [Type], [Number]) VALUES (N'Blue', N'A', N'2')
GO
INSERT [dbo].[TableTmp] ([Color], [Type], [Number]) VALUES (N'Blue', N'A', N'3')
GO
INSERT [dbo].[TableTmp] ([Color], [Type], [Number]) VALUES (N'Blue', N'B', N'1')
GO
INSERT [dbo].[TableTmp] ([Color], [Type], [Number]) VALUES (N'Blue', N'C', N'1')
GO
INSERT [dbo].[TableTmp] ([Color], [Type], [Number]) VALUES (N'Red', N'A', N'1')
GO
INSERT [dbo].[TableTmp] ([Color], [Type], [Number]) VALUES (N'Red', N'B', N'2')
GO

I want to generate a JSON string like this:

[
  {
    "Color": "Blue",
    "Part": [
      {
        "Type": "A",
        "Number": [
          "1",
          "2",
          "3"
        ]
      },
      {
        "Type": "B",
        "Number": [
          "1"
        ]
      },
      {
        "Type": "C",
        "Number": [
          "1"
        ]
      }
    ]
  },
  {
    "Color": "Red",
    "Part": [
      {
        "Type": "A",
        "Number": [
          "1"
        ]
      },
      {
        "Type": "B",
        "Number": [
          "2"
        ]
      }
    ]
  }
]

There can be more colors and/or types. How can I do this?

If you need more details, I'll be happy to share. I'm currently feeling that I have passed on all that is needed to understand the problem.

Answer 1

First of all: JSON support needs v2016+. Secondly: The problem here will be the naked array like here "Number": ["1","2","3"]. I have no idea why, but that is not supported at the moment. The rest is rather easy, but this will need some tricks.

Try this

DECLARE @tmp TABLE(
    [Color] [nvarchar](50) NULL,
    [Type] [nvarchar](50) NULL,
    [Number] [nvarchar](50) NULL
)

INSERT INTO @tmp ([Color], [Type], [Number]) 
VALUES 
 (N'Blue', N'A', N'1')
,(N'Blue', N'A', N'2')
,(N'Blue', N'A', N'3')
,(N'Blue', N'B', N'1')
,(N'Blue', N'C', N'1')
,(N'Red', N'A', N'1')
,(N'Red', N'B', N'2');

SELECT t.Color
     ,(
        SELECT t2.[Type]
              ,(
                SELECT t3.Number
                FROM @tmp t3
                WHERE t3.Color=t.Color AND t3.[Type]=t2.[Type]
                FOR JSON PATH
               ) AS Number
        FROM @tmp t2
        WHERE t2.Color=t.Color
        GROUP BY t2.[Type]
        FOR JSON PATH
      ) AS Part
FROM @tmp t
GROUP BY t.Color
FOR JSON PATH;

the result (formatted)

[
    {
        "Color": "Blue",
        "Part": [
            {
                "Type": "A",
                "Number": [
                    {
                        "Number": "1"
                    },
                    {
                        "Number": "2"
                    },
                    {
                        "Number": "3"
                    }
                ]
            },
            {
                "Type": "B",
                "Number": [
                    {
                        "Number": "1"
                    }
                ]
            },
            {
                "Type": "C",
                "Number": [
                    {
                        "Number": "1"
                    }
                ]
            }
        ]
    },
    {
        "Color": "Red",
        "Part": [
            {
                "Type": "A",
                "Number": [
                    {
                        "Number": "1"
                    }
                ]
            },
            {
                "Type": "B",
                "Number": [
                    {
                        "Number": "2"
                    }
                ]
            }
        ]
    }
]

Now we have to use rather ugly tricks with REPLACE to get rid of the array of objects in the middle:

SELECT REPLACE(REPLACE(REPLACE(
(
    SELECT t.Color
         ,(
            SELECT t2.[Type]
                  ,(
                    SELECT t3.Number
                    FROM @tmp t3
                    WHERE t3.Color=t.Color AND t3.[Type]=t2.[Type]
                    FOR JSON PATH
                   ) AS Number
            FROM @tmp t2
            WHERE t2.Color=t.Color
            GROUP BY t2.[Type]
            FOR JSON PATH
          ) AS Part
    FROM @tmp t
    GROUP BY t.Color
    FOR JSON PATH
),'},{"Number":',','),'{"Number":',''),'}]}',']}');

the result

[
    {
        "Color": "Blue",
        "Part": [
            {
                "Type": "A",
                "Number": [
                    "1",
                    "2",
                    "3"
                ]
            },
            {
                "Type": "B",
                "Number": [
                    "1"
                ]
            },
            {
                "Type": "C",
                "Number": [
                    "1"
                ]
            }
        ]
    },
    {
        "Color": "Red",
        "Part": [
            {
                "Type": "A",
                "Number": [
                    "1"
                ]
            },
            {
                "Type": "B",
                "Number": [
                    "2"
                ]
            }
        ]
    }
]

UPDATE

It might be a bit easier and cleaner to create the naked array on string level:

SELECT t.Color
        ,(
        SELECT t2.[Type]
                ,JSON_QUERY('[' + STUFF((
                SELECT CONCAT(',"',t3.Number,'"')
                FROM @tmp t3
                WHERE t3.Color=t.Color AND t3.[Type]=t2.[Type]
                FOR XML PATH('')),1,1,'') + ']') AS Number
        FROM @tmp t2
        WHERE t2.Color=t.Color
        GROUP BY t2.[Type]
        FOR JSON PATH
        ) AS Part
FROM @tmp t
GROUP BY t.Color
FOR JSON PATH;

UPDATE 2: Starting with v2017 there is STRING_AGG()

You can try this on v2017

SELECT t.Color
        ,(
        SELECT t2.[Type]
                ,JSON_QUERY('["' + STRING_AGG(t2.Number,'","') + '"]') AS Number
        FROM @tmp t2
        WHERE t2.Color=t.Color
        GROUP BY t2.[Type]
        FOR JSON PATH
        ) AS Part
FROM @tmp t
GROUP BY t.Color
FOR JSON PATH;