更新时间:2023-01-17 12:00:25
首先: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"
]
}
]
}
]
在字符串级别创建裸数组可能会更容易,更干净:
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;
STRING_AGG()
您可以在v2017上尝试
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;