如何将表格转换为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"
        ]
      }
    ]
  }
]

可以有更多的颜色和/或类型。我该怎么做？

如果需要更多细节，我很乐意分享。目前我感到我已经传达了理解问题所需的一切。

- galgil

---

可能是SQL Server表转JSON的重复问题。 - Phillip Morton

嗨，如果可能的话，就在信息来源中找不到。 - galgil

那个问题的其中一个答案会有所帮助。https://dev59.com/D2Mm5IYBdhLWcg3wFL_s#50467906 你只需要修改你的 SQL 查询，可能需要包含一个分组。 - Phillip Morton

@PhillipMorton 两个回答都没有真正涵盖这个问题，而且被接受的答案已经过时了... - Shnugo

@PhillipMorton 对不起，但你错了，它并没有完全相同的结构。法语中有表格，您可以自行查看。您必须更加认真地思考如何做到这一点。 - galgil

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;

- Shnugo

---

嗨@JohnCappelletti，是的，这个 STRING_AGG 相当不错，可以让我们摆脱这成千上万的 FOR XML PATH('') 行 ；-) 不过，我仍然不明白，为什么他们没有一个 FOR JSON ARRAY（类似于 STRING_SPLIT() 中缺失的片段索引）;-) - Shnugo

我认为这是我见过的最美丽的代码之一，感谢您的善良和认真。 - galgil