1

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'
7
  • If it is common to make changes like that, you should not use JSON, but instead use multiple tables a la RDBMS.
    – Rick James
    Oct 6, 2022 at 16:41
  • I couldn't agree more. Unfortunately, changing it is not an option.
    – PW_Parsons
    Oct 6, 2022 at 16:45
  • 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.)
    – Rick James
    Oct 6, 2022 at 17:06
  • 1
    Welcome to the Inner-Platform Effect antipattern. You now get to implement a whole DBMS within a DBMS. Oct 6, 2022 at 21:42
  • What does SELECT VERSION(); return? I'd like to know if you can use JSON_TABLE(), which requires MySQL 8.0. Oct 6, 2022 at 21:43

1 Answer 1

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.

1
  • Thank you for sharing, Bill
    – PW_Parsons
    Oct 8, 2022 at 19:12

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .