SQL Server query JSONobject to get aggregated values

I have a JSON object stored in SQL Server, in a nvarchar(max) column. The JSON looks like this:

{
"data": [{
        "RespID": 3512,
        "ObsPriceValue": 2.34
    }, {
        "RespID": 4904,
        "ObsPriceValue": 2.54
    }, {
        "RespID": 5127,
        "ObsPriceValue": 3.44
    }]
}

The above example array is made up of three items, but I don’t know how many items are present in the record, and they can be 2 as well as up to 30.

Each row in the table has an object like the above.

I’d like to get, in a single query, the average value of the field ObsPriceValue

I’ve tried with JSON_QUERY, but I have always to specify the index of the element.

Is there a way to get it or the JSON schema the data is stored is wrong?

Answer

Next approach may help. You need to use OPENJSON() with explicit schema definition to return a table with ObsPriceValue column:

JSON:

DECLARE @json nvarchar(max) = N'{"data": [{
        "RespID": 3512,
        "ObsPriceValue": 2.34
    }, {
        "RespID": 4904,
        "ObsPriceValue": 2.54
    }, {
        "RespID": 5127,
        "ObsPriceValue": 3.44
    }]
}'

Statement:

SELECT AVG(ObsPriceValue) AS AvgObsPriceValue
FROM OPENJSON(@json, '$.data') WITH (
   ObsPriceValue numeric(10, 2) '$.ObsPriceValue' 
) j 

Output:

----------------
AvgObsPriceValue
----------------
2.773333