Как конвертировать таблицы в массивы Json?

Question

Я использую Sql Server 2016 и хочу преобразовать таблицу в json.

У меня есть простая таблица:

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

Я хочу сгенерировать строку JSON следующим образом:

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

Может быть больше цветов и / или типов. Как я могу это сделать?

Если вам нужно больше деталей, я буду рад поделиться. В настоящее время я чувствую, что передал все, что необходимо для понимания проблемы.

Answer 1

Прежде всего: поддержка JSON требует v2016+. Во-вторых: проблема здесь будет в голом массиве, как здесь "Number": ["1","2","3"], Я понятия не имею, почему, но это не поддерживается в настоящее время. Остальное довольно просто, но для этого понадобятся некоторые хитрости.

Попробуй это

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;

результат (отформатированный)

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

Теперь мы должны использовать довольно уродливые трюки с REPLACE избавиться от массива объектов посередине:

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

результат

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

ОБНОВИТЬ

Может быть немного проще и понятнее создать голый массив на уровне строк:

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;

ОБНОВЛЕНИЕ 2: начиная с v2017 есть STRING_AGG()

Вы можете попробовать это на 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;