Search a JSON with multiple array elements for specific attribute and get another attribute value

Question

I am using SQL Server 2019 and need to search for arrays in JSON that contains specific attribute. And get the latest value of another attribute. My JSON looks like this:

{
    "Comment":[
        {"Id":"1","CreateTime":"20241001","UpdateTime":0,"value":"aaa"}
        ,{"Id":"2","CreateTime":"20241002","UpdateTime":0,"User":"A","Result":"bbb"}
        ,{"Id":"3","CreateTime":"20241003","UpdateTime":0,"value":"ccc"}
        ,{"Id":"4","CreateTime":"20241004","UpdateTime":0,"User":"B","Result":"ddd"}
    ]
}

I need to search for arrays that contain attribute User. In my sample those are arrays with an Id 2 and 4. And I need to get the value of an attribute Result for Id 4, because it has latest CreateTime. So query must return ddd

I tried to write a query with JSON_QUERY() with no success. I would really appreciate some help or tips to solve my issue.

Answer 1

The actual statement depends on the JSON structure. In this situation a possible approach is to parse the JSON content with OPENJSON() and the appropriate schema, transform it into a table and get the expected result:

JSON:

DECLARE @json nvarchar(max) = N'{
    "Comment":[
        {"Id":"1","CreateTime":"20241001","UpdateTime":0,"value":"aaa"}
        ,{"Id":"2","CreateTime":"20241002","UpdateTime":0,"User":"A","Result":"bbb"}
        ,{"Id":"3","CreateTime":"20241003","UpdateTime":0,"value":"ccc"}
        ,{"Id":"4","CreateTime":"20241004","UpdateTime":0,"User":"B","Result":"ddd"}
    ]
}'

Statement:

SELECT TOP 1 [Result]
FROM OPENJSON(@json, '$.Comment') WITH (
   [User] nvarchar(10) '$.User',
   [Result] nvarchar(10) '$.Result',
   [CreateTime] varchar(10) '$.CreateTime'
)
WHERE [User] IS NOT NULL
ORDER BY TRY_CONVERT(date, [CreateTime]) DESC

Also note, that the $.Comment part of the stored JSON is an array, containing JSON objects, not JSON arrays.