如何使用SQL替换嵌套JSON对象中的指定数据点

针对你的需求，我们可以基于原有的数据组替换逻辑，进一步定位到datapoints数组中的目标元素，实现精准替换。以下是改造后的SQL语句：

核心更新语句 #

UPDATE dashboards
SET data = JSON_SET(
    data,
    -- 拼接目标数据点的完整JSON路径
    CONCAT(
        -- 获取目标数据组的基础路径（如 $[0]）
        SUBSTRING_INDEX(
            JSON_UNQUOTE(JSON_SEARCH(data, 'one', :datagroup_id, NULL, '$[*].id')),
            '.id',
            1
        ),
        '.datapoints',
        -- 获取数据组内目标数据点的路径（如 [0]）
        SUBSTRING_INDEX(
            JSON_UNQUOTE(JSON_SEARCH(data, 'one', :datapoint_id, NULL, CONCAT(
                SUBSTRING_INDEX(
                    JSON_UNQUOTE(JSON_SEARCH(data, 'one', :datagroup_id, NULL, '$[*].id')),
                    '.id',
                    1
                ),
                '.datapoints[*].id'
            ))),
            '.id',
            1
        )
    ),
    :data -- 直接传入新的数据点JSON对象
)
WHERE id = :dashboard_id;

逻辑拆解 #

1. 定位数据组：和原语句逻辑一致，用JSON_SEARCH找到对应datagroup_id的路径（如$[0].id），再通过SUBSTRING_INDEX截取到数据组的根路径$[0]。
2. 定位数据点：基于数据组的根路径，构造datapoints数组的搜索路径（如$[0].datapoints[*].id），再次用JSON_SEARCH找到目标datapoint_id的路径，截取后得到数据点在数组中的位置（如[0]）。
3. 拼接完整路径：把数据组路径和数据点位置拼接成完整的JSON路径（如$[0].datapoints[0]），最后用JSON_SET替换该路径下的内容为传入的:data参数。

优化：避免无效更新 #

如果担心datagroup_id或datapoint_id不存在导致的无意义执行，可以在WHERE条件中加入存在性判断：

UPDATE dashboards
SET data = JSON_SET(
    data,
    CONCAT(
        SUBSTRING_INDEX(
            JSON_UNQUOTE(JSON_SEARCH(data, 'one', :datagroup_id, NULL, '$[*].id')),
            '.id',
            1
        ),
        '.datapoints',
        SUBSTRING_INDEX(
            JSON_UNQUOTE(JSON_SEARCH(data, 'one', :datapoint_id, NULL, CONCAT(
                SUBSTRING_INDEX(
                    JSON_UNQUOTE(JSON_SEARCH(data, 'one', :datagroup_id, NULL, '$[*].id')),
                    '.id',
                    1
                ),
                '.datapoints[*].id'
            ))),
            '.id',
            1
        )
    ),
    :data
)
WHERE 
    id = :dashboard_id
    -- 确保目标数据组存在
    AND JSON_SEARCH(data, 'one', :datagroup_id, NULL, '$[*].id') IS NOT NULL
    -- 确保目标数据点存在于该数据组内
    AND JSON_SEARCH(data, 'one', :datapoint_id, NULL, CONCAT(
        SUBSTRING_INDEX(
            JSON_UNQUOTE(JSON_SEARCH(data, 'one', :datagroup_id, NULL, '$[*].id')),
            '.id',
            1
        ),
        '.datapoints[*].id'
    )) IS NOT NULL;

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