如何查询无命名对象的JSON数组并计算每行Amt总和

我有包含如下JSON格式数据的行：[{"Qty": 1, "Amt": 2.99},{"Qty": 3, "Amt": 4.50}]，想计算每行里所有Amt的总和。

复现代码 #

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路径表达式上卡壳了，官方文档只展示了带命名的数组示例（比如"people": [{},{}]），自己写的查询语句没法得到正确结果：

SELECT Id, SUM(CAST( JSON_VALUE(Result,N'lax $.[].Amt') as decimal)) 
FROM SqlQueryResults
GROUP BY Id

你的问题出在JSON_VALUE只能返回单个值，没法一次性提取数组里所有Amt的值来求和。要处理这种顶级无命名JSON数组，得用OPENJSON来解析数组中的每个元素，再做聚合计算。

方法一：子查询结合OPENJSON #

SELECT 
    Id,
    SUM(CAST(Amt AS DECIMAL(10,2))) AS TotalAmt
FROM (
    SELECT 
        sqr.Id,
        JSON_VALUE(j.value, '$.Amt') AS Amt
    FROM dbo.SqlQueryResults sqr
    CROSS APPLY OPENJSON(sqr.Result) j
) t
GROUP BY Id;

方法二：OPENJSON指定返回架构 #

直接在OPENJSON里定义要提取的列，代码更简洁：

SELECT 
    sqr.Id,
    SUM(j.Amt) AS TotalAmt
FROM dbo.SqlQueryResults sqr
CROSS APPLY OPENJSON(sqr.Result)
WITH (
    Amt DECIMAL(10,2) '$.Amt'
) j
GROUP BY sqr.Id;

关键说明 #

• OPENJSON会把JSON数组拆成多行记录，每个数组元素对应一行。
• CROSS APPLY用来关联原表的每一行和解析后的JSON行。
• 最后按Id分组求和，就能得到每行的Amt总金额。

内容的提问来源于stack exchange，提问作者ttugates