Mysql JSON update multiple object values in an array

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.