我有包含Json的行,比如:[{"Qty": 1, "Amt": 2.99},{"Qty": 3, Amt": 4.50}]
如何获得每行的收件箱总数?
复制代码:
DROP TABLE IF EXISTS dbo.SqlQueryResults;
CREATE TABLE dbo.SqlQueryResults(
Id INT,
Result nvarchar(max)
)
GO
INSERT INTO dbo.SqlQueryResults (Id, Result)
VALUES
(1, '[{"Qty":1, "Amt":2.99}, {"Qty":3, "Amt":4.50}]'),
(2, '[{"Qty":2, "Amt":7.99}, {"Qty":5, "Amt":2.50}]')
SELECT * FROM dbo.SqlQueryResults;
我被Json路径表达困住了. 文档JSON Path Expressions (SQL Server)仅显示名称"people": [{}.{}]
代表ex的数组
这是我目前的疑问:
SELECT Id, SUM(CAST( JSON_VALUE(Result,N'lax $.[].Amt') as decimal))
FROM SqlQueryResults
GROUP BY Id