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!

Answer 1

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.