CODEWITHSUNDEEP
sqljsonmariadbsql-function
Codex
Codex

Reputation: 1281

MariaDB updating json attribute across array in JSON

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

Upvotes: 0

Views: 465

Answers (1)

Codex
Codex

Reputation: 1281

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

Upvotes: 1

Related Questions