Nested JSON in MySQL with filtering

Question

Here's the JSON that I have in the MySQL table:

{
    "qmSubsegmentFlags": [
        {
            "text": "Accuracy",
            "id": 1,
            "children": [
                {
                    "text": "Mistranslation",
                    "id": 2,
                    "children": [
                        {
                            "text": "Terminology",
                            "id": 3
                        }
                    ]
                },
                {
                    "text": "Omission",
                    "id": 4
                },
                {
                    "text": "Addition",
                    "id": 5
                },
                {
                    "text": "Untranslated",
                    "id": 6
                }
            ]
        }
    ]
}

So you see, id's and text can be in different levels. I only have the value of the id and have to get the text for this with SQL. Below a JSON path that works for the first level, but not for all levels. Neither does it work in MySQL, since MySQL only supports limited filtering.

$.qmSubsegmentFlags[?(@.id==1)].text

A little SQL that goes into the right direction, but also does not solve the issue.

SELECT JSON_EXTRACT(t.qmSubsegmentFlags, '$.qmSubsegmentFlags[*].text') AS lqa FROM translate5.LEK_task AS t where id = 3719;

I used to be a programmer, but after 4 years of project management, my skills are dead. But I need a proof of concept until monday. Any help would be great!

Are you committed to using JSON for this? It would be much easier if you did not use JSON (that statement applies for many uses of JSON in MySQL). — Bill Karwin, Feb 10 '23 at 16:17
Also can you run this query and report what it returns: `SELECT VERSION();` — Bill Karwin, Feb 10 '23 at 16:18
The nested JSON is like this in the DB of a product that we use. It's open source, so not set in stone, but most likely very very difficult to change. Version is 8.0.26 — Remy, Feb 13 '23 at 07:55

score 1 · Accepted Answer · answered Feb 13 '23 at 18:11

Here's a solution tested on MySQL 8.0.32.

WITH RECURSIVE tree AS (
  SELECT j.id, j.text, j.children
  FROM LEK_task CROSS JOIN JSON_TABLE(
    JSON_EXTRACT(qmSubsegmentFlags, '$.qmSubsegmentFlags'),
    '$[*]' COLUMNS (
      text TEXT PATH '$.text',
      id INT PATH '$.id',
      children JSON PATH '$.children'
    )
  ) AS j
  WHERE LEK_task.id = 3719
  UNION ALL
  SELECT j.id, j.text, j.children
  FROM tree CROSS JOIN JSON_TABLE(
    children,
    '$[*]' COLUMNS(
      text TEXT PATH '$.text',
      id INT PATH '$.id',
      children JSON PATH '$.children'
    )
  ) AS j
)
SELECT id, text FROM tree;

Result:

+------+----------------+
| id   | text           |
+------+----------------+
|    1 | Accuracy       |
|    2 | Mistranslation |
|    4 | Omission       |
|    5 | Addition       |
|    6 | Untranslated   |
|    3 | Terminology    |
+------+----------------+

You can add a WHERE id = 1 to the last line of the query to get the row for a specific id.

Nested JSON in MySQL with filtering

1 Answers1