arrays - 如何将表转换为 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;