我在 MS-SQL 資料庫中有以下示例資料：（Microsoft SQL Server Standard Version 13；Microsoft SQL Server Management Studio 18）
---------- ----------- ----- -------- --------- ---------
| LastName | Firstname | Age | Weight | Sallery | Married |
---------- ----------- ----- -------- --------- ---------
| Smith | Stan | 58 | 87 | 59.000 | true |
| Smith | Maria | 53 | 57 | 45.000 | true |
| Brown | Chris | 48 | 77 | 159.000 | true |
| Brown | Stepahnie | 39 | 67 | 95.000 | true |
| Brown | Angela | 12 | 37 | 0.0 | false |
---------- ----------- ----- -------- --------- ---------
我想從中得到一個嵌套的 JSON 陣列，如下所示：
[
{
"Smith": [
{
"Stan": [
{
"Age": 58,
"Weight": 87,
"Sallery": 59.000,
"Married": true
}
],
"Maria": [
{
"Age": 53,
"Weight": 57,
"Sallery": 45.000,
"Married": true
}
]
}
],
"Brown": [
{
"Chris": [
{
"Age": 48,
"Weight": 77,
"Sallery": 159.000,
"Married": true
}
],
"Stepahnie": [
{
"Age": 39,
"Weight": 67,
"Sallery": 95.000,
"Married": true
}
],
"Angela": [
{
"Age": 12,
"Weight": 37,
"Sallery": 0.0,
"Married": false
}
]
}
]
}
]
如何構建 SQL 查詢？
我嘗試了不同的方法，但我沒有使根動態化，或者根不斷重復......
例如，我嘗試了以下查詢：
我得到一個級別：
WITH cte AS
(
SELECT FirstName
js = json_query(
(
SELECT Age,
Weight,
Sallery,
Married
FOR json path,
without_array_wrapper ) )
FROM Table1)
SELECT '[' stuff(
(
SELECT '},{"' FirstName '":' '[' js ']'
FROM cte
FOR xml path ('')), 1, 2, '') '}]'
但是我需要一個帶有 LastName 的嵌套級別
另一個嘗試：
SELECT
LastName ,json
FROM Table1 as a
OUTER APPLY (
SELECT
FirstName
FROM Table1 as b
WHERE a.LastName = b.LastName
FOR JSON PATH
) child(json)
FOR JSON PATH
uj5u.com熱心網友回復：
不幸的是，SQL Server 不支持JSON_AGGnor JSON_OBJECT_AGG，這在這里會有所幫助。但是我們可以用STRING_AGG和破解它STRING_ESCAPE
WITH ByFirstName AS
(
SELECT
p.LastName,
p.FirstName,
json = STRING_AGG(j.json, ',')
FROM Person p
CROSS APPLY (
SELECT
p.Age,
p.Weight,
p.Sallery,
p.Married
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS j(json)
GROUP BY
p.LastName,
p.FirstName
),
ByLastName AS
(
SELECT
p.LastName,
json = STRING_AGG(CONCAT(
'"',
STRING_ESCAPE(p.FirstName, 'json'),
'":[',
p.json,
']'
), ',')
FROM ByFirstName p
GROUP BY
p.LastName
)
SELECT '[{'
STRING_AGG(CONCAT(
'"',
STRING_ESCAPE(p.LastName, 'json'),
'":{',
p.json,
'}'
), ',') '}]'
FROM ByLastName p
db<>小提琴
這讓你
[
{
"Brown": {
"Angela": [
{
"Age": 12,
"Weight": 37,
"Sallery": 0,
"Married": false
}
],
"Chris": [
{
"Age": 48,
"Weight": 77,
"Sallery": 159000,
"Married": true
}
],
"Stepahnie": [
{
"Age": 39,
"Weight": 67,
"Sallery": 95000,
"Married": true
}
]
},
"Smith": {
"Maria": [
{
"Age": 53,
"Weight": 57,
"Sallery": 45000,
"Married": true
}
],
"Stan": [
{
"Age": 58,
"Weight": 87,
"Sallery": 59000,
"Married": true
}
]
}
}
]
uj5u.com熱心網友回復：
當然可以得到你想要的 JSON 輸出，但是正如你在下面看到的那樣，代碼相當復雜......
/*
* Data setup...
*/
create table dbo.Person (
LastName varchar(10),
FirstName varchar(10),
Age int,
Weight int,
Sallery int,
Married bit
);
insert dbo.Person (LastName, FirstName, Age, Weight, Sallery, Married)
values
('Smith', 'Stan', 58, 87, 59000, 1),
('Smith', 'Maria', 53, 57, 45000, 1),
('Brown', 'Chris', 48, 77, 159000, 1),
('Brown', 'Stepahnie', 39, 67, 95000, 1),
('Brown', 'Angela', 12, 37, 0, 0);
/*
* Example JSON query...
*/
with Persons as (
select LastName, Stan, Maria, Chris, Stepahnie, Angela
from (
select
LastName,
FirstName,
(
select Age, Weight, Sallery, Married
for json path
) as data
from dbo.Person
) src
pivot (max(data) for FirstName in (Stan, Maria, Chris, Stepahnie, Angela)) pvt
)
select
json_query((
select
json_query(Stan) as Stan,
json_query(Maria) as Maria
from Persons
where LastName = 'Smith'
for json path
)) as Smith,
json_query((
select
json_query(Chris) as Chris,
json_query(Stepahnie) as Stepahnie,
json_query(Angela) as Angela
from Persons
where LastName = 'Brown'
for json path
)) as Brown
for json path;
這會產生輸出......
[
{
"Smith": [
{
"Stan": [
{
"Age": 58,
"Weight": 87,
"Sallery": 59000,
"Married": true
}
],
"Maria": [
{
"Age": 53,
"Weight": 57,
"Sallery": 45000,
"Married": true
}
]
}
],
"Brown": [
{
"Chris": [
{
"Age": 48,
"Weight": 77,
"Sallery": 159000,
"Married": true
}
],
"Stepahnie": [
{
"Age": 39,
"Weight": 67,
"Sallery": 95000,
"Married": true
}
],
"Angela": [
{
"Age": 12,
"Weight": 37,
"Sallery": 0,
"Married": false
}
]
}
]
}
]
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/492732.html
上一篇：創建可自行重置的列
下一篇：資料在熊貓中被覆寫