Аналог JSON_TABLE в MYSQL, чтобы ключи были одним из столбцов?

Question

[Анатолий]

В некоторых сценариях мне нужно из столбца с json-данными построить таблицу.
Вот, к примеру, объект: {"1": 2, "2": 4, "3": 1, "4": [2, 3, 6]}. Все ключи уникальны между собой. Вложенных объектов нет.
Как проще всего через их нативные команды построить такую таблицу:

1 2
2 4
3 1
4 2
4 3
4 6

При использовании JSON_TABLE работа ведётся только со значениями, а ключи наоборот должны быть неуникальны, то есть чтобы построить таблицу через такую функцию, нужно иметь следующий исходный массив:
[{"key": 1, "value: 2}, {"key": 2, "value": 4}].

Как-то можно проще построить таблицу с уникальными ключами? Я не нашёл команды такой же простой и можно сказать обратной команде JSON_OBJECTAGG. Мне нужно выполнить команду обратно противоположную JSON_OBJECTAGG. Столбец1 - ключ, столбец2 - значение.

Неужели нужно работать в цикле с каждой парой через JSON_KEY и JSON_EXTRACT, и только так формировать строки таблицы?

Answer 1

[d'Ivan]

Неужели нужно работать в цикле с каждой парой через JSON_KEY и JSON_EXTRACT, и только так формировать строки таблицы?

Поскольку выбрана именно такая структура данных в JSON, то средствами SQL только так.

Попробуйте

[{"key": 1, "value: 2}, {"key": 2, "value": 4}]

Такая структура позволяет использовать JSON_TABLE. Тогда получится список (id, key, value) с одинаковыми id.
Однако, если исходя из задачи Как строить архитектуру БД, где у юзеров много полей? требуется получить список вопросов с их ответами по user_id, то проще раскодировать JSON на стороне клиента СУБД, в самой программе, занеся в соответствующие модели, без использования JSON_TABLE.

Comments

[Анатолий]

Большое спасибо за содержательный ответ!

Меня просто очень удивило, что сам вопрос структуры хранения данных уже внутри json столбца оказался с подвохом. Мы получаем ситуацию, когда ни один формат данных из двух приведённых выше не поддерживается всеми их (SQL) командами.

То, что поддерживается JSON_TABLE, не поддерживается, к примеру, JSON_SET, где нужен наоборот уникальный ключ, либо тогда уже указать позицию нужного объекта - всё это лишние действия и уже не так удобно, как предполагалось.

[d'Ivan]

Анатолий, был рад помочь. Выбор подходящей структуры данных непрост: нужно знать как данные заносятся в СУБД и каковы сценарии для их использования.
Вообще, в MySQL 8 и JSON я стал работать лишь год назад. В нашей стартап-компании обычно используют JSON колонку в качестве места хранения данных для сложных структур, которые получают целиком. С JSON_TABLE довелось работать лишь одинажды. Да и то было моим выбором. Многие другие разработчики предпочитали просто распаковывать JSON в нужные структуры вместо ухищрений с запросами в сочетании с JSON_TABLE. Так что не стоит сильно полагаться на нестандартные SQL функции для работы с JSON.

Answer 2

[Alex XYZ]

Все ключи уникальны между собой

Это лишнее уточнение, т.к. в object все ключи уникальны по определению. Одинаковых ключей не бывает в принципе.
Что касается простоты, то сам json является простым форматом, но не сценарии, в которых он может быть применён. То что вы написали не является стандартным способом работы с Json, который входит в набор его базовых функций - типа получить ключи или значение по ключу или длину массива элемента. Поэтому даже сценарий получения такого списка «ключ-значение», как у вас должен писаться. Увы.