Mysql JSON update multiple object values in an array

Question

I have the following data in a meta json column and need to rename the Visual Arts interests.

{
 "industries": [
 {
 "name": "Science",
 "id": 1,
 "interests": [
 {
 "id": 1,
 "name": "Visual Arts"
 },
 {
 "id": 2,
 "name": "Data Science"
 }
 ]
 },
 {
 "name": "Arts",
 "id": 2,
 "interests": [
 {
 "id": 3,
 "name": "Music"
 },
 {
 "id": 1,
 "name": "Visual Arts"
 }
 ]
 }
 ]
}

I have managed to rename the industry name with this statement:

UPDATE table
SET meta = JSON_REPLACE(meta, JSON_UNQUOTE(JSON_SEARCH(meta, 'all', 'old_name')), 'new_name')

This works because the JSON_SEARCH returns one path: $.industries[0].name

How can I update the interests if there are multiple values to be updated?

Running the above update statement to update the Visual Arts interest results in an error:

Query 1 ERROR: Invalid JSON path expression. The error is around character position 1.

I assume it's because there are two paths returned from the statement:

SELECT JSON_SEARCH(meta, 'all', 'Visual Arts')
FROM table

Results in:

["$.industries[0].interests[0].name", "$.industries[1].interests[1].name"]

Any help would be appreciated.

Thanks in advance.

Edit This query works:

SELECT JSON_REPLACE(meta, '$.industries[0].interests[0].name', 'new_name', '$.industries[1].interests[1].name', 'new_name')
FROM job_requirements

Is there a way to convert

["$.industries[0].interests[0].name", "$.industries[1].interests[1].name"]

into

'$.industries[0].interests[0].name', 'new_name', '$.industries[1].interests[1].name', 'new_name'

• Rick James: If it is common to make changes like that, you should not use JSON, but instead use multiple tables a la RDBMS.
• PW_Parsons: I couldn't agree more. Unfortunately, changing it is not an option.
• Rick James: Changing jobs is an option? It sounds like you were hired to take care of the database, but are not allowed to do so. (I do understand that the conversion would be non-trivial.)
• Bill Karwin: Welcome to the Inner-Platform Effect antipattern. You now get to implement a whole DBMS within a DBMS.
• Bill Karwin: What does SELECT VERSION(); return? I'd like to know if you can use JSON_TABLE(), which requires MySQL 8.0.

Answer 1

Here's my experimental attempt, though it fails. I'm posting this because you asked to see what I had tried. I don't claim this is a full solution to your question.

drop table if exists mytable;
create table mytable (
 id serial primary key,
 meta json
);
insert into mytable set meta = '...your example data...';
SELECT mytable.id, j.*
FROM mytable CROSS JOIN JSON_TABLE(
 mytable.meta,
 '$.industries[*]' COLUMNS(
 industry_num FOR ORDINALITY,
 industry_name VARCHAR(20) PATH '$.name',
 NESTED PATH '$.interests[*]' COLUMNS(
 interest_num FOR ORDINALITY,
 interest_name VARCHAR(20) PATH '$.name'
 )
 )
) AS j;

Output:

+----+--------------+---------------+--------------+---------------+
| id | industry_num | industry_name | interest_num | interest_name |
+----+--------------+---------------+--------------+---------------+
| 1 | 1 | Science | 1 | Visual Arts |
| 1 | 1 | Science | 2 | Data Science |
| 1 | 2 | Arts | 1 | Music |
| 1 | 2 | Arts | 2 | Visual Arts |
+----+--------------+---------------+--------------+---------------+

Now that we have a method for identifying the array index at each level of the JSON nested objects, we can try to UPDATE the value:

WITH cte AS (
 SELECT mytable.id, j.*
 FROM mytable CROSS JOIN JSON_TABLE(
 mytable.meta,
 '$.industries[*]' COLUMNS(
 industry_num FOR ORDINALITY,
 industry_name VARCHAR(20) PATH '$.name',
 NESTED PATH '$.interests[*]' COLUMNS(
 interest_num FOR ORDINALITY,
 interest_name VARCHAR(20) PATH '$.name'
 )
 )
 ) AS j
)
UPDATE mytable JOIN cte USING (id)
SET meta = JSON_REPLACE(meta, CONCAT('$.industries[', cte.industry_num-1, '].interests[', cte.interest_num-1, '].name'), 'New Interest Name')
WHERE cte.interest_name = 'Visual Arts';

The problem that I have not solved is that this appears to only do one replacement.

JSON_REPLACE() takes variable arguments, so you can change the value at more than one path, but constructing that call would require dynamic SQL. I'm not interested in doing any more work on this, because in my opinion, the work above should demonstrate that managing this data in JSON is already more trouble than it's worth.

• PW_Parsons: Thank you for sharing, Bill