SQL Server中基于动态JSON键的status值筛选数据行方法

现有以下JSON格式的数据行（假设存储在SQL Server的JsonData表中，表包含RowId（行标识）和JsonContent（存储JSON内容）两个字段）：

data row 1 :

{
    "30":{"status":0,"approval":"0","entrydate":"2023-01-30"},
    "26":{"status":0,"approval":"0","entrydate":"2023-01-30"}
}

data row 2 :

{
    "12":{"status":0,"approval":"0","entrydate":"2023-01-30"},
    "13":{"status":1,"approval":"20022-xxxx","entrydate":"2023-01-30"}
}

data row 3 :

{
    "20":{"status":1,"approval":"20022-xxxx","entrydate":"2023-01-30"},
    "24":{"status":1,"approval":"20022-xxxx","entrydate":"2023-01-30"}
}

由于JSON中的键为动态值，需实现：

• 当筛选条件为status=1时，返回第2行、第3行数据
• 当筛选条件为status=0时，返回第1行、第2行数据

利用SQL Server的OPENJSON函数解析动态键的JSON，结合聚合逻辑判断整行是否符合筛选条件：

1. 创建示例表（若已有表可跳过） #

CREATE TABLE JsonData (
    RowId INT IDENTITY(1,1) PRIMARY KEY,
    JsonContent NVARCHAR(MAX) NOT NULL
);

-- 插入示例数据
INSERT INTO JsonData (JsonContent)
VALUES
('{
    "30":{"status":0,"approval":"0","entrydate":"2023-01-30"},
    "26":{"status":0,"approval":"0","entrydate":"2023-01-30"}
}'),
('{
    "12":{"status":0,"approval":"0","entrydate":"2023-01-30"},
    "13":{"status":1,"approval":"20022-xxxx","entrydate":"2023-01-30"}
}'),
('{
    "20":{"status":1,"approval":"20022-xxxx","entrydate":"2023-01-30"},
    "24":{"status":1,"approval":"20022-xxxx","entrydate":"2023-01-30"}
}');

2. 带参数的通用查询 #

定义@TargetStatus参数切换筛选条件，只要该行JSON中存在至少一个子对象的status等于目标值，就返回整行：

DECLARE @TargetStatus INT = 1; -- 改为0即可筛选status=0的行

SELECT jd.RowId, jd.JsonContent
FROM JsonData jd
CROSS APPLY OPENJSON(jd.JsonContent)
WITH (
    status INT '$.status'
) AS jsonItems
GROUP BY jd.RowId, jd.JsonContent
HAVING MAX(CASE WHEN jsonItems.status = @TargetStatus THEN 1 ELSE 0 END) = 1;

3. 分场景直接查询 #

筛选status=1的行（返回第2、3行）

SELECT jd.RowId, jd.JsonContent
FROM JsonData jd
CROSS APPLY OPENJSON(jd.JsonContent)
WITH (
    status INT '$.status'
) AS jsonItems
GROUP BY jd.RowId, jd.JsonContent
HAVING MAX(CASE WHEN jsonItems.status = 1 THEN 1 ELSE 0 END) = 1;

筛选status=0的行（返回第1、2行）

SELECT jd.RowId, jd.JsonContent
FROM JsonData jd
CROSS APPLY OPENJSON(jd.JsonContent)
WITH (
    status INT '$.status'
) AS jsonItems
GROUP BY jd.RowId, jd.JsonContent
HAVING MAX(CASE WHEN jsonItems.status = 0 THEN 1 ELSE 0 END) = 1;

逻辑说明 #

• OPENJSON将动态键的JSON拆分为多行，每行对应一个子对象
• 通过WITH子句提取每个子对象的status字段
• 分组后用MAX(CASE...)判断该行是否存在符合目标status的子对象，只要有一个符合就返回整行

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