Update a field with specific value inside a Json Object with MariaDB

Question

I'm trying to update the data stored in a json column in MariaDB (libmysql version - 5.6.43 , Server: 10.3.34-MariaDB-cll-lve - MariaDB Server).

My data is structured like this:

ID	json_data
1	{....}
2	{....}

where json_data is structured as follows:

{
    "company": {
        "id": "",
        "name": "",
        "address": ""
    },
    "info_company": {
          "diff_v": "1",
          "grav_v": "",
          "diff_s": "2",
          "grav_s": "",
          "diff_g": "3",
          "grav_g": "",
          "diff_ri": "4",
          "grav_ri": "2"
    }
}

I'm trying to update data inside info_company replacing:

• "1" with "<50%"
• "2" with "<50%"
• "3" with ">50%"
• "4" with ">50%"

so the result should be:

{
    "company": {
        "id": "",
        "name": "",
        "address": ""
    },
    "info_company": {
          "diff_v": "<50%",
          "grav_v": "",
          "diff_s": "<50%",
          "grav_s": "",
          "diff_g": ">50%",
          "grav_g": "",
          "diff_ri": ">50%",
          "grav_ri": "<50%"
    }
}

By writing this query, I can retrieve the info_company data, but then for each key contained I cannot update the data following the new value.

SELECT new_t.id, JSON_EXTRACT(new_t.json_data, “$.info_company“) FROM (SELECT * FROM `my_table` WHERE json_data LIKE “%info_company%”) new_t

Output:

ID	json_data
1	{"diff_v": "1","grav_v": "","diff_s": "2","grav_s": "","diff_g": "3","grav_g": "","diff_ri": "4","grav_ri": "2"}

Thank you for your help.

Answer 1

You can solve this problem by using a CTE to generate a regex to match the keys (and desired matching values) inside info_company and then using REGEXP_REPLACE to replace a 1 or 2 with <50% and a 3 or 4 with >50%:

UPDATE my_table
JOIN (
  WITH jkeys_table AS (
    SELECT id, JSON_KEYS(json_data, '$.info_company') AS jkeys
    FROM my_table
  )
  SELECT id,
         CONCAT('((?:',
                REPLACE(SUBSTRING(jkeys, 2, CHAR_LENGTH(jkeys)-2), ', ', '|'),
                ')\\s*:\\s*)"([12])"'
         ) AS regex12,
         CONCAT('((?:',
                REPLACE(SUBSTRING(jkeys, 2, CHAR_LENGTH(jkeys)-2), ', ', '|'),
                ')\\s*:\\s*)"([34])"'
         ) AS regex34
  FROM jkeys_table
) rt ON my_table.id = rt.id
SET json_data = REGEXP_REPLACE(REGEXP_REPLACE(json_data, regex12, '\\1"<50%"'), regex34, '\\1">50%"')

Output (for your sample JSON):

id  json_data
1   {
        "company": 
        {
            "id": "",
            "name": "",
            "address": ""
        },
        "info_company": 
        {
            "diff_v": "<50%",
            "grav_v": "",
            "diff_s": "<50%",
            "grav_s": "",
            "diff_g": ">50%",
            "grav_g": "",
            "diff_ri": ">50%",
            "grav_ri": "<50%"
        }
    }

Demo on dbfiddle

If it's possible the keys in info_company might exist elsewhere inside json_data, you need to localise the changes to the info_company element. You can do this by changing the SET clause of the UPDATE to:

SET json_data = JSON_REPLACE(json_data, '$.info_company',
                JSON_MERGE_PATCH(JSON_QUERY(json_data, '$.info_company'),
                                 REGEXP_REPLACE(REGEXP_REPLACE(JSON_QUERY(json_data, '$.info_company'), regex12, '\\1"<50%"'), regex34, '\\1">50%"')
                                )
                )

Demo on dbfiddle

If the keys in info_company are the same for every row, you can optimise the query by only computing the regex12 and regex34 values once, and then applying those values to all rows in my_table using a CROSS JOIN:

UPDATE my_table
CROSS JOIN (
  WITH jkeys_table AS (
    SELECT JSON_KEYS(json_data, '$.info_company') AS jkeys
    FROM my_table
    LIMIT 1
  )
  SELECT CONCAT('((?:',
                REPLACE(SUBSTRING(jkeys, 2, CHAR_LENGTH(jkeys)-2), ', ', '|'),
                ')\\s*:\\s*)"([12])"'
         ) AS regex12,
         CONCAT('((?:',
                REPLACE(SUBSTRING(jkeys, 2, CHAR_LENGTH(jkeys)-2), ', ', '|'),
                ')\\s*:\\s*)"([34])"'
         ) AS regex34
  FROM jkeys_table
) rt
SET json_data = REGEXP_REPLACE(REGEXP_REPLACE(json_data, regex12, '\\1"<50%"'), regex34, '\\1">50%"')

Demo on dbfiddle