我有這個表格，這個資料很簡單
CREATE TABLE #Prices
(
ProductId int,
SizeId int,
Price int,
Date date
)
INSERT INTO #Prices
VALUES (1, 1, 100, '2020-01-01'),
(1, 1, 120, '2020-02-01'),
(1, 1, 130, '2020-03-01'),
(1, 2, 100, '2020-01-01'),
(1, 2, 100, '2020-02-01'),
(2, 1, 100, '2020-01-01'),
(2, 1, 120, '2020-02-01'),
(2, 1, 130, '2020-03-01'),
(2, 2, 100, '2020-01-01'),
(2, 2, 100, '2020-02-01')
我想將輸出格式化為這樣的：
{
"Products": [
{
"Product": 2,
"UnitSizes": [
{
"SizeId": 1,
"PerDate": [
{
"Date": "2020-01-02",
"Price": 870.0
},
{
"Date": "2021-04-29",
"Price": 900.0
}
]
},
{
"SizeId": 2,
"PerDate": [
{
"Date": "2020-01-02",
"Price": 435.0
},
{
"Date": "2021-04-29",
"Price": 450.0
}
]
}
]
},
{
"Product": 4,
"UnitSizes": [
{
"SizeId": 1,
"PerDate": [
{
"Date": "2020-01-02",
"Price": 900.0
}
]
}
]
}
]
}
我幾乎擁有它，但我不知道如何格式化以獲取“PerDate”中的陣列。這就是我所擁有的
SELECT
ProductId AS [Product],
SizeId AS 'Sizes.SizeId',
date AS 'Sizes.PerDate.Date',
price AS 'Sizes.PerDate.Price'
FROM
#Prices
ORDER BY
ProductId, [Sizes.SizeId], Date
FOR JSON PATH, ROOT('Products')
我嘗試FOR JSON AUTO過JSON_QUERY()但沒有任何效果，我嘗試過但我無法達到我想要的結果。
每一個幫助將不勝感激。
謝謝
uj5u.com熱心網友回復：
不幸的是，SQL Server 沒有該JSON_AGG功能，這意味著您通常需要使用多個相關子查詢并不斷重新掃描基表。
但是，我們可以通過使用STRING_AGG.json 檔案中生成的單個 JSON 物件來模擬它APPLY。這意味著我們只掃描基表一次。
使用
JSON_QUERYwith no path 防止雙重轉義
WITH PerDate AS (
SELECT
p.ProductId,
p.SizeId,
PerDate = '[' STRING_AGG(j.PerDate, ',') WITHIN GROUP (ORDER BY p.Date) ']'
FROM #Prices AS p
CROSS APPLY ( -- This produces multiple rows of single JSON objects
SELECT p.Date, p.Price
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) j(PerDate)
GROUP BY
p.ProductId,
p.SizeId
),
UnitSizes AS (
SELECT
p.ProductId,
UnitSizes = '[' STRING_AGG(j.UnitSizes, ',') WITHIN GROUP (ORDER BY p.SizeId) ']'
FROM PerDate p
CROSS APPLY (
SELECT p.SizeId, PerDate = JSON_QUERY(p.PerDate)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) j(UnitSizes)
GROUP BY
p.ProductId
)
SELECT
Product = p.ProductId,
UnitSizes = JSON_QUERY(p.UnitSizes)
FROM UnitSizes p
ORDER BY p.ProductId
FOR JSON PATH, ROOT('Products');
資料庫<>小提琴
uj5u.com熱心網友回復：
這是一種方法
DROP TABLE IF EXISTS #Prices
CREATE TABLE #Prices
(
ProductId INT,
SizeId INT,
Price INT,
Date DATE
)
-- SQL Prompt formatting off
INSERT INTO #Prices
VALUES (1, 1, 100, '2020-01-01'),
(1, 1, 120, '2020-02-01'),
(1, 1, 130, '2020-03-01'),
(1, 2, 100, '2020-01-01'),
(1, 2, 100, '2020-02-01'),
(2, 1, 100, '2020-01-01'),
(2, 1, 120, '2020-02-01'),
(2, 1, 130, '2020-03-01'),
(2, 2, 100, '2020-01-01'),
(2, 2, 100, '2020-02-01')
-- SQL Prompt formatting on
SELECT m.ProductId AS Product,
(
SELECT s.SizeId,
(
SELECT p.Date,
p.Price
FROM #Prices AS p
WHERE p.ProductId = s.ProductId
AND p.SizeId = s.SizeId
ORDER BY p.Date
FOR JSON PATH
) AS PerDate
FROM #Prices AS s
ORDER BY s.SizeId
FOR JSON PATH
) AS UnitSizes
FROM #Prices AS m
GROUP BY m.ProductId
ORDER BY m.ProductId
FOR JSON PATH, ROOT('Products')
輸出：
{
"Products":
[
{
"Product": 1,
"UnitSizes":
[
{
"SizeId": 1,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 120
},
{
"Date": "2020-03-01",
"Price": 130
}
]
},
{
"SizeId": 1,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 120
},
{
"Date": "2020-03-01",
"Price": 130
}
]
},
{
"SizeId": 1,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 120
},
{
"Date": "2020-03-01",
"Price": 130
}
]
},
{
"SizeId": 1,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 120
},
{
"Date": "2020-03-01",
"Price": 130
}
]
},
{
"SizeId": 1,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 120
},
{
"Date": "2020-03-01",
"Price": 130
}
]
},
{
"SizeId": 1,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 120
},
{
"Date": "2020-03-01",
"Price": 130
}
]
},
{
"SizeId": 2,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 100
}
]
},
{
"SizeId": 2,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 100
}
]
},
{
"SizeId": 2,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 100
}
]
},
{
"SizeId": 2,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 100
}
]
}
]
},
{
"Product": 2,
"UnitSizes":
[
{
"SizeId": 1,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 120
},
{
"Date": "2020-03-01",
"Price": 130
}
]
},
{
"SizeId": 1,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 120
},
{
"Date": "2020-03-01",
"Price": 130
}
]
},
{
"SizeId": 1,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 120
},
{
"Date": "2020-03-01",
"Price": 130
}
]
},
{
"SizeId": 1,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 120
},
{
"Date": "2020-03-01",
"Price": 130
}
]
},
{
"SizeId": 1,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 120
},
{
"Date": "2020-03-01",
"Price": 130
}
]
},
{
"SizeId": 1,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 120
},
{
"Date": "2020-03-01",
"Price": 130
}
]
},
{
"SizeId": 2,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 100
}
]
},
{
"SizeId": 2,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 100
}
]
},
{
"SizeId": 2,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 100
}
]
},
{
"SizeId": 2,
"PerDate":
[
{
"Date": "2020-01-01",
"Price": 100
},
{
"Date": "2020-02-01",
"Price": 100
}
]
}
]
}
]
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/347758.html
標籤：sql json sql-server
上一篇：查詢帶有空格條件的XML列