Вложенный 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 лет управления проектами мои навыки умерли. Но мне нужно доказательство концепции до понедельника. Любая помощь будет здорово!
Также вы можете запустить этот запрос и сообщить, что он возвращает: SELECT VERSION();
Вложенный JSON выглядит так в БД продукта, который мы используем. Это открытый исходный код, поэтому он не высечен на камне, но, скорее всего, его очень и очень трудно изменить. Версия 8.0.26
Ответы 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 к последней строке запроса, чтобы получить строку для определенного идентификатора.
Готовы ли вы использовать для этого JSON? Было бы намного проще, если бы вы не использовали JSON (это утверждение применимо ко многим случаям использования JSON в MySQL).