How to Query JSON Array of unnamed objects

Question

I have rows containing Json like: [{"Qty": 1, "Amt": 2.99},{"Qty": 3, Amt": 4.50}]

How do I get a sum of Amt per row?

Reproduction Code:

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;

I am stuck around the Json Path Expression. The documentation JSON Path Expressions (SQL Server) only shows arrays with a name "people": [{}.{}] for ex

This is the query I have at the moment:

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

Answer 1

here is one way:

SELECT 
    id,
    sum(cast(JSON_VALUE(value, '$.Amt') as numeric(10,2))) as sum_Amt
FROM dbo.SqlQueryResults 
CROSS APPLY OPENJSON(Result)
GROUP BY id 

output :

id	sum_Amt
1	7.49
2	10.49