时间戳

首页 > 解决方案 > mysql - 遍历所有行并更新 json 数组中的所有对象

mysql - mysql - 遍历所有行并更新 json 数组中的所有对象

问题描述

对于 mysql 5.7，我有一个copy_jobs包含 json 类型列的表job_configs。

job_configs有一个属性configurations，它是一个对象数组。

中的数据示例job_configs：

{"configurations": [
    {
        "role": "Introduction", "role_id": 1, "employee_id": 1, 
        "first_payment": "15.63", "second_payment": "46.88", 
        "employee_number": "00001", "employee_last_name": "Mendes", 
        "first_payment_made": false, "employee_first_name": "Mario", 
        "second_payment_made": false, "total_bonus_percentage": 25
    }, {
        "role": "P & D", "role_id": 2, "employee_id": 2, 
        "first_payment": "12.50", "second_payment": "37.50", 
        "employee_number": "00002", "employee_last_name": "Parker", 
        "first_payment_made": false, "employee_first_name": "Shaun", 
        "second_payment_made": false, "total_bonus_percentage": 20
    }
]}

我需要运行一个迁移，它将属性"fumbled": false和添加到该表每一行的数组中的fumblesCaught: []每个对象。configurations

我以为

UPDATE copy_jobs 
SET job_configs = JSON_SET(job_configs, '$.configurations[*].fumbled', false);

至少可以添加"fumbled": false，但这会返回错误消息：In this situation, path expressions may not contain the * and ** tokens.

有没有办法运行一个命令，它将两个新属性添加到configurations数组中的每个对象？

或者我是否需要创建一个我可以调用的过程，它将获取所有行，并为每一行循环遍历configurations对象，并将这两个新属性一一添加到对象中？

编辑：

在@wchiquito 发表评论后，我创建了一个程序，认为这可行。

CREATE PROCEDURE updateRoleConfig()
BEGIN
DECLARE counter Int DEFAULT 0;
DECLARE totalRows Int;
SELECT COUNT(*) INTO totalRows FROM copy_jobs;
SET totalRows = totalRows + 1;

WHILE counter < totalRows DO
    SET @offset = counter;
    PREPARE getRowSql FROM 'SELECT job_configs INTO @jsonConfigs FROM copy_jobs LIMIT 1 OFFSET ?';
    EXECUTE getRowSql USING @offset;
    SELECT concat('selected row is ', @jsonConfigs);

    UPDATE copy_jobs SET job_configs = JSON_SET(job_configs, '$.configurations', CAST(
        REPLACE(
            JSON_EXTRACT(@jsonConfigs, '$.configurations'),
            '}',
            ', "fumbled": false, "fumblesCaught": []}') AS JSON
        )
    );
    SET counter = counter + 1;
END WHILE;
END

configurations但是，这会导致使用单个对象覆盖所有行数组，从而导致所有行都相同。

奇怪的是，输出@jsonConfigs总是覆盖所有其他对象的值，巧合的是configurations，在表的第一行中找到的数组的值。

{"configurations": [
    {
        "role": "Introduction", "role_id": 1, "employee_id": 1, 
        "first_payment": "15.63", "second_payment": "46.88", 
        "employee_number": "00001", "employee_last_name": "Mendes", 
        "first_payment_made": false, "employee_first_name": "Mario", 
        "second_payment_made": false, "total_bonus_percentage": 25}
    ]
}

为什么会这样？

标签： mysqljsonloops

解决方案

我最终通过将 ID 存储在变量中，然后使用该 ID 更新和获取 json 来使其工作。

DELIMITER //
CREATE PROCEDURE updateRoleConfig()
BEGIN
DECLARE counter Int DEFAULT 0;
DECLARE totalRows Int;
SELECT COUNT(*) FROM copy_jobs INTO totalRows;
SET totalRows = totalRows + 1;
SET counter = 0;

WHILE counter < totalRows DO
    SET @offset = counter;

    SELECT concat('offset is ', @offset);
    SELECT concat('counter is ', counter);

    PREPARE getIdSql FROM 'SELECT id FROM copy_jobs LIMIT 1 OFFSET ? INTO @rowId';
    EXECUTE getIdSql USING @offset;
    PREPARE getJsonSql FROM 'SELECT job_configs FROM copy_jobs WHERE id=? INTO @jsonConfigs';
    EXECUTE getJsonSql USING @rowId;

    SELECT concat('selected row is ', @jsonConfigs);
    SELECT concat('selected row id ', @rowId);

    SET @json = JSON_SET(@jsonConfigs, '$.configurations', CAST(
        REPLACE(
            JSON_EXTRACT(@jsonConfigs, '$.configurations'),
            '}',
            ', "fumbled": false, "fumblesCaught": []}') AS JSON
        )
    );

    UPDATE copy_jobs SET job_configs = @json WHERE id = @rowId;
    SET counter = counter + 1;
END WHILE;
END//
DELIMITER ;

推荐阅读