如何将表转换为Json数组? [英] how to Convert Table to Json Arrays?
问题描述
我正在使用Sql Server 2016，我想将表转换为json.
I'm using Sql Server 2016 and I want to convert a table to 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字符串:
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.
推荐答案
首先:JSON支持需要v2016 +.其次:这里的问题将是裸数组，就像这里的"Number": ["1","2","3"].我不知道为什么，但是目前尚不支持.其余的过程很简单，但这需要一些技巧.
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.
尝试一下
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使用相当丑陋的技巧来摆脱中间的对象数组:
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":',''),'}]}',']}');
结果
[
{
"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;
更新2:从v2017开始，有STRING_AGG()
您可以在v2017上尝试
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;
这篇关于如何将表转换为Json数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
