Вложенный JSON в MySQL с фильтрацией

Вот JSON, который у меня есть в таблице MySQL:

{
    "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
                }
            ]
        }
    ]
}

Итак, вы видите, идентификаторы и текст могут быть на разных уровнях. У меня есть только значение идентификатора, и я должен получить текст для этого с помощью SQL. Ниже путь JSON, который работает для первого уровня, но не для всех уровней. Он также не работает в MySQL, поскольку MySQL поддерживает только ограниченную фильтрацию.

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

Небольшой SQL, который идет в правильном направлении, но тоже не решает проблему.

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

Раньше я был программистом, но после 4 лет управления проектами мои навыки умерли. Но мне нужно доказательство концепции до понедельника. Любая помощь будет здорово!

🤔 А знаете ли вы, что...
MySQL обеспечивает множество индексов для оптимизации поисковых запросов.


1
72
1

Ответ:

Решено

Вот решение, протестированное на 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;

Результат:

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

Вы можете добавить WHERE id = 1 к последней строке запроса, чтобы получить строку для определенного идентификатора.