SQL Server 2019中查找含User属性的JSON数组并获取最新Result值

针对你的需求，SQL Server 2019可以通过OPENJSON解析JSON数组，结合常规筛选、排序逻辑实现目标。以下是两种常见场景的实现方式：

场景1：JSON数据存储在表字段中 #

假设JSON数据存储在YourTable表的JsonContent字段里，执行以下查询即可获取结果：

SELECT TOP 1 JSON_VALUE(CommentItem, '$.Result') AS LatestResult
FROM YourTable
CROSS APPLY OPENJSON(JsonContent, '$.Comment') AS CommentItem
WHERE JSON_VALUE(CommentItem, '$.User') IS NOT NULL
ORDER BY JSON_VALUE(CommentItem, '$.CreateTime') DESC;

场景2：JSON数据为临时变量 #

如果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"}
    ]
}';

SELECT TOP 1 JSON_VALUE(CommentItem, '$.Result') AS LatestResult
FROM OPENJSON(@json, '$.Comment') AS CommentItem
WHERE JSON_VALUE(CommentItem, '$.User') IS NOT NULL
ORDER BY JSON_VALUE(CommentItem, '$.CreateTime') DESC;

逻辑说明 #

• OPENJSON(JsonContent, '$.Comment')：将JSON中的Comment数组展开为行集，每个数组元素对应一行数据。
• WHERE JSON_VALUE(CommentItem, '$.User') IS NOT NULL：筛选出包含User属性的元素（若属性不存在，JSON_VALUE会返回NULL）。
• ORDER BY JSON_VALUE(CommentItem, '$.CreateTime') DESC：按CreateTime降序排序，确保最新记录排在首位。
• TOP 1：仅取排序后的第一条记录，即CreateTime最新的元素对应的Result值。

你之前尝试的JSON_QUERY()主要用于提取JSON片段，而这里需要提取具体属性值并进行关系型筛选，因此OPENJSON结合JSON_VALUE是更适配的方案。

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