Вот 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 обеспечивает множество индексов для оптимизации поисковых запросов.
Вот решение, протестированное на 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
к последней строке запроса, чтобы получить строку для определенного идентификатора.