#mysql #mysql-json
Вопрос:
В столбце JSON MySQL 8 у меня есть объект JSON со значениями разных типов (но без вложенных объектов). Подобный этому:
{
"abc": "Something123",
"foo": 63.4,
"bar": "Hi world!",
"xyz": false
}
Каков самый простой способ выбора объединенных строковых значений? В качестве примера, из приведенного выше JSON мы должны получить "Something123 Hi world!" ?
Ответ №1:
Вот решение для MySQL 8.0:
select group_concat(json_unquote(json_extract(t.data, concat('$.', j.`key`))) separator '') as joined_string
from mytable cross join json_table(json_keys(mytable.data), '$[*]' columns (`key` varchar(20) path '
Вывод с учетом ваших примерных данных:
-----------------------
| joined_string |
-----------------------
| Something123Hi world! |
-----------------------
Однако я задаюсь вопросом, является ли это хорошей идеей для хранения данных в JSON, если вам нужно это сделать. Решение является сложным в разработке, и его будет трудно отладить или изменить.
Использование JSON для хранения данных в виде документа, когда вы действительно хотите, чтобы предикаты SQL обрабатывали поля документа как отдельные элементы, сложнее, чем использование обычных строк и столбцов.
Если вы используете MySQL 5.7 или более ранней версии, то функция JSON_TABLE() не поддерживается. В этом случае я бы предложил загрузить весь документ JSON в ваше приложение и превратить его в объект, которым вы можете управлять.
Комментарии:
1. Билл, спасибо за твой ответ. К сожалению, ваше решение очень медленное. На столе с 10 тысячами строк он работал в течение 10 минут, и мне пришлось прервать его. Смотрите мой ответ - он использует аналогичные функции JSON, но занял 0,1 секунды на той же таблице.
2. Что касается "Однако мне интересно, хорошая ли это идея хранить данные в JSON, если вам нужно это сделать": Да, в моем случае JSON отлично работает. Не делайте выводов только из одного странного запроса, который мне иногда нужно было бы выполнить. :)
3. Справедливо. Это правда, что любой вид денормализации является преимуществом для определенных запросов, и если это те запросы, которые вам нужно оптимизировать большую часть времени, то это чистая победа. Но каждый тип оптимизации влечет за собой штраф для других типов запросов.
Ответ №2:
Вот решение:
SELECT GROUP_CONCAT(va SEPARATOR ' ') str_vals_only
FROM
(
SELECT id, JSON_UNQUOTE(JSON_EXTRACT(my_json_col, CONCAT('$.', j.obj_key))) va
FROM my_tbl,
JSON_TABLE(JSON_KEYS(my_json_col), '$[*]' COLUMNS(obj_key TEXT PATH "$")) j
WHERE JSON_TYPE(JSON_EXTRACT(my_json_col, CONCAT('$.', j.obj_key))) = 'STRING'
) a
GROUP BY id
Не очень просто и лаконично, но я думаю, что это все, что может быть.
Мне нравится поддержка JSON в MySQL. Для некоторых конкретных случаев тип JSON может обеспечить очень хорошее и гибкое решение. Это главная причина, по которой я даже не думаю о переходе на MariaDB (у которого гораздо более ограниченная поддержка JSON). Но я хотел бы, чтобы было больше функций для манипулирования JSON. JSON_TABLE Функция очень мощная, но она может быть немного сложной и многословной.
)) j
join mytable t on json_type(json_extract(t.data, concat('$.', j.`key`)))='STRING';
Вывод с учетом ваших примерных данных:
Однако я задаюсь вопросом, является ли это хорошей идеей для хранения данных в JSON, если вам нужно это сделать. Решение является сложным в разработке, и его будет трудно отладить или изменить.
Использование JSON для хранения данных в виде документа, когда вы действительно хотите, чтобы предикаты SQL обрабатывали поля документа как отдельные элементы, сложнее, чем использование обычных строк и столбцов.
Если вы используете MySQL 5.7 или более ранней версии, то функция JSON_TABLE() не поддерживается. В этом случае я бы предложил загрузить весь документ JSON в ваше приложение и превратить его в объект, которым вы можете управлять.
Комментарии:
1. Билл, спасибо за твой ответ. К сожалению, ваше решение очень медленное. На столе с 10 тысячами строк он работал в течение 10 минут, и мне пришлось прервать его. Смотрите мой ответ — он использует аналогичные функции JSON, но занял 0,1 секунды на той же таблице.
2. Что касается «Однако мне интересно, хорошая ли это идея хранить данные в JSON, если вам нужно это сделать»: Да, в моем случае JSON отлично работает. Не делайте выводов только из одного странного запроса, который мне иногда нужно было бы выполнить. 🙂
3. Справедливо. Это правда, что любой вид денормализации является преимуществом для определенных запросов, и если это те запросы, которые вам нужно оптимизировать большую часть времени, то это чистая победа. Но каждый тип оптимизации влечет за собой штраф для других типов запросов.
Ответ №2:
Вот решение:
Не очень просто и лаконично, но я думаю, что это все, что может быть.
Мне нравится поддержка JSON в MySQL. Для некоторых конкретных случаев тип JSON может обеспечить очень хорошее и гибкое решение. Это главная причина, по которой я даже не думаю о переходе на MariaDB (у которого гораздо более ограниченная поддержка JSON). Но я хотел бы, чтобы было больше функций для манипулирования JSON. JSON_TABLE Функция очень мощная, но она может быть немного сложной и многословной.