SQL Server query JSONobject to get aggregated values

Question

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?

Zhorov · Accepted Answer · 2019-08-11 16:13:08Z

2

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

answered Aug 11, 2019 at 16:13

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Giox Over a year ago

So this is the only way with the JSON data stored in that way? There could be other possible way to store the data in order to simplify the SQL query? (Even if it seems perfect to me)

Zhorov Over a year ago

@Giox Yes, I think so. When you have a JSON array, use OPENJSON to parse this array.

Collectives™ on Stack Overflow

SQL Server query JSONobject to get aggregated values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related