MariaDB updating json attribute across array in JSON

Question

Goal I have a json column in my table which I want to update in MariaDB 10.3.18.

CREATE TABLE IF NOT EXISTS `json_data` (
  `name` VARCHAR(255),
  `config_json` MEDIUMTEXT(10000)
);

config_json field has value like this.

{
    "one": {
        "filters": [
            {
                "type": "campaign_categories_5",
                "title": "Title1"
            }
        ],
        "value1": "one",
        "containers": [],
        "combined": false
    },
    "description": "",
    "columns": [
        {
            "dataType": "STRING",
            "title": "Title1"
        },
        {
            "dataType": "STRING",
            "title": "Title2"
        },
        {
            "dataType": "STRING",
            "title": "Title3"
        },
        {
            "dataType": "STRING",
            "title": "Title1"
        }
    ]
}

I am trying to update every entry in the table by replacing attribute "title" of config_json in the path column[*].title by replacing value "Title1" with "Updated Title1", "Title2" with "Updated Title2" and "Title3" with "Updated Title3". Updated json should look like this

{
    "one": {
        "filters": [
            {
                "type": "campaign_categories_5",
                "title": "Title1"
            }
        ],
        "value1": "one",
        "containers": [],
        "combined": false
    },
    "description": "",
    "columns": [
        {
            "dataType": "STRING",
            "title": "Updated Title1"
        },
        {
            "dataType": "STRING",
            "title": "Updated Title2"
        },
        {
            "dataType": "STRING",
            "title": "Updated Title3"
        },
        {
            "dataType": "STRING",
            "title": "Updated Title1"
        }
    ]
}

As you can see above I shouldn't change title attribute in other path. I am trying this

drop FUNCTION  if EXISTS modify_json;
create FUNCTION modify_json(myvar json)
returns json
deterministic
begin

    SET @i = 0;
    SET @len = JSON_LENGTH(myvar, '$.columns');
    while i < @len do
        IF JSON_CONTAINS(myvar, '"Title1"', concat('$.columns[', i, '].title')) THEN
            set myvar = json_replace(myvar, concat('$.columns[', i, '].title'), '"Updated Title1"');
        END IF;
        IF JSON_CONTAINS(myvar, '"Title2"', concat('$.columns[', i, '].title')) THEN
            set myvar = json_replace(myvar, concat('$.columns[', i, '].title'), '"Updated Title2"');
        END IF;
        IF JSON_CONTAINS(myvar, '"Title3"', concat('$.columns[', i, '].title')) THEN
            set myvar = json_replace(myvar, concat('$.columns[', i, '].title'), '"Updated Title3"');
        END IF;
        set i = i + 1;
    end while;
    return myvar;
end;

update config_suite_reports_element set config_json = modify_json(config_json)
where json_contains_path(config_json, 'one', '$.columns[*].title');

But I am getting following error

Error occurred during SQL script execution

Reason:
SQL Error [1064] [42000]: (conn=5434951) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 6

This question has nothing to do with JSON at all. You just forgot to use DELIMITER. — Bill Karwin
– Bill Karwin, Commented Nov 26, 2019 at 15:40
I used delimiter as well, but faced similar fate. Even I tried similar code as a procedure without parameter, Please let me know what am I doing wrong. Thank you. — Codex
– Codex, Commented Nov 26, 2019 at 15:42
Variables with the @ sigil are user-defined session variables. Variables without the @ sigil are either built-in variables or else local variables you define with DECLARE. You can't use the @ sometimes. The variables @i and i are not the same variable. — Bill Karwin
– Bill Karwin, Commented Nov 26, 2019 at 15:54
@BillKarwin Thank you so much, It really helped. I fixed, its a silly mistake in the end. — Codex
– Codex, Commented Nov 26, 2019 at 17:35

Codex · Accepted Answer · 2019-11-26 17:40:03Z

I found the problem. 1. Delimiter 2. variable naming convention in latest mariaDB 10.3

DROP FUNCTION IF EXISTS func;
DELIMITER //

CREATE FUNCTION func(myvar json) RETURNS json DETERMINISTIC
BEGIN
  DECLARE i TINYINT;
  DECLARE len TINYINT;
  SET i = 0;
  SET len = JSON_LENGTH(myvar, '$.columns');
  while i < len do
    IF JSON_CONTAINS(myvar, '"Title1"', concat('$.columns[', i, '].title')) THEN
        set myvar = json_replace(myvar, concat('$.columns[', i, '].title'), 'Updated Title1');
    ELSEIF JSON_CONTAINS(myvar, '"Title2"', concat('$.columns[', i, '].title')) THEN
        set myvar = json_replace(myvar, concat('$.columns[', i, '].title'), 'Updated Title2');
    ELSEIF JSON_CONTAINS(myvar, '"Title3"', concat('$.columns[', i, '].title')) THEN
        set myvar = json_replace(myvar, concat('$.columns[', i, '].title'), 'Updated Title3');
    END IF;
    set i = i + 1;
  end while;
  RETURN myvar;
END 

//

DELIMITER ;

Thank you @BillKarwin

Collectives™ on Stack Overflow

MariaDB updating json attribute across array in JSON

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related