Problem

Imagine that you have a table which contains lists and every list is related to list of companies (in JSON array column). But the company IDs that we have are let’s say pointing to old table with companies. Imagine that we have a new table with companies and we also have a table containing the relation between the old and new IDs (company_map).

Table: my_lists

list_id list_name company_ids
1 Some name [101, 102, 103]

Table: company_map

old_id new_id
101 1001
102 1002
103 1003

Goal

Our goal is to update the old company IDs my_lists table with the new IDs using the company_map table.

Solution

You will need MySQL 5.7+.

First we make a copy of the original table (without data).

CREATE TABLE my_lists_new LIKE my_lists; 

Next we will create a select with join on the elements from the company_ids column to the mapper table. And we can directly combine it with an insert. The IF condition in the select is helping us for the cases when none of the companies in the list are matched.

INSERT INTO my_list
(list_id, list_name, company_ids)
SELECT t.list_id, t.list_name, if(i.new_id, JSON_ARRAYAGG(i.new_id), JSON_ARRAY()) AS `company_ids` 
FROM my_lists t
LEFT JOIN company_map i ON JSON_CONTAINS(
t.`company_ids`->'$[*]',
CAST(i.old_id AS JSON)
)
GROUP BY t.list_id;

The last thing to do is to replace the newly created table with the old one.

RENAME TABLE my_lists TO my_lists_old, my_lists_new TO my_lists;

Result

We got the my_lists table with the new company IDs 🙂