SQL中批量更新含随机后缀的JSON对象中bicycle的visibility值

针对你遇到的JSON结构（动态键名带随机数字），可以通过拆解JSON为关系数据→筛选目标键→批量修改→重组JSON的思路来实现，用OPEN_JSON解析动态键，JSON_MODIFY更新值，具体步骤如下：

1. 测试数据准备（模拟你的场景） #

假设你有一张存储JSON的表test_table，先创建测试数据：

CREATE TABLE test_table (id INT PRIMARY KEY, json_content NVARCHAR(MAX));
INSERT INTO test_table VALUES (
    1,
    N'{"objects": {"object1": {"components": {"car": {"visibility": true},"bicycle": {"visibility": true},"bicycle1": {"visibility": true},"bicycle2": {"visibility": true},"van": {"visibility": true}}},"object2": {"components": {"car": {"visibility": true},"bicycle5": {"visibility": true},"van": {"visibility": true}}},"objectN": {"components": {"car": {"visibility": true},"bicycle": {"visibility": true},"bicycle3": {"visibility": true},"van": {"visibility": true}}}}}'
);

2. 核心解决方案代码 #

通过CTE拆解JSON结构，筛选出所有bicycle开头的键，再批量更新：

WITH ObjectDetails AS (
    -- 解析顶层objects，获取每个子对象的名称和对应的components JSON
    SELECT 
        t.id,
        obj_name = [key],
        components_json = JSON_QUERY(value, '$.components')
    FROM test_table t
    CROSS APPLY OPEN_JSON(t.json_content, '$.objects')
),
BicycleTargets AS (
    -- 解析components里的所有键，筛选出bicycle开头的目标键
    SELECT 
        od.id,
        od.obj_name,
        bike_key = [key]
    FROM ObjectDetails od
    CROSS APPLY OPEN_JSON(od.components_json)
    WHERE [key] LIKE 'bicycle%'
)
-- 动态拼接JSON路径，逐个修改visibility为false
UPDATE test_table
SET json_content = JSON_MODIFY(
    json_content,
    CONCAT('$.objects.', bt.obj_name, '.components.', bt.bike_key, '.visibility'),
    CAST(0 AS BIT)
)
FROM test_table t
JOIN BicycleTargets bt ON t.id = bt.id;

3. 代码说明 #

• ObjectDetails CTE：用OPEN_JSON解析$.objects节点，把每个子对象（object1、object2等）拆成一行，同时提取对应的components子JSON。
• BicycleTargets CTE：继续解析每个components里的键值对，用LIKE 'bicycle%'筛选出所有目标键（bicycle、bicycle1等）。
• UPDATE语句：通过CONCAT动态拼接JSON路径（比如$.objects.object1.components.bicycle.visibility），用JSON_MODIFY把对应节点的visibility设为false（SQL里用CAST(0 AS BIT)对应JSON的false）。

4. 验证结果 #

执行完更新后，查询表查看修改后的JSON：

SELECT json_content FROM test_table;

返回的JSON会和你期望的输出一致，所有bicycle开头的键下的visibility都被设为false，其他键保持不变。

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