Как получить уникальные значения JSON массива из ячейки MySQL?

Question

[Олег Правдин]

Добрый день, в таблице есть колонка, содержащая массивы значений в JSON'е, например:
| id | values |
| 1 | [1,2,3] |
| 2 | [2,3,4] |
Мне необходимо выполнить что-то вроде SELECT DISTINCT, но для значений массива:
| uniques |
| 1 |
| 2 |
| 3 |
| 4 |
Можно ли это сделать, и если можно, то как?

Answer 1

[FanatPHP]

Не надо хранить в базе данных " массивы значений в JSON'е".
MySQL - реляционная датабаза. Это означает, что база данных строится из связанных таблиц.
И вместо колонки values должна быть отдельная таблица,

table_id | value
1 | 1
1 | 2
1 | 3
2 | 2
2 | 3
2 | 4

И тогда все выборки будут делаться примитивными SQL запросами, без малейших затруднений со стороны разработчика.

Comments

[FanatPHP]

Согласен

[Олег Правдин]

В данном случае JSON был выбран намеренно. Спасибо за комментарий про реляционность, но есть идеи как реализовать выборку из вопроса?

[FanatPHP]

Олег Правдин, "я стрелял себе в ногу намеренно, и теперь хожу на костылях. есть идеи, как мне стать чемпионом по бегу?" Выберите что-то одно. c джейсоном будут только костыли. Перебирайте строки в пхп, и выбирайте из них значения. Тот, кто будет сопровождать этот код после вас, будет плакать кровавыми слезами, но это ведь не проблема, верно?

[Danil Sapegin]

Олег Правдин, используйте mongodb для намеренного использования json )

[Олег Правдин]

FanatPHP, разумеется :D
Как говорится, после нас хоть потоп. Тем не менее, спасибо за ответ. Вопрос решен, возможно вам будет интересно ознакомиться с решением.

Answer 2

[Олег Правдин]

Вопрос полностью решен. Проверил несколько способов: подсчетом на бэке, запросом из этого ответа на stackoverflow (https://stackoverflow.com/questions/39906435/conve... а также тестом аналогичной структуры, но реляционно.
Производительность проверил на таблице из 50к записей, 53к элементов в колонке суммарно (сумма длин всех массивов), среди которых ~320 уникальных элементов. В случае с реляционной таблицей, соответственно, 53к строк.
Решение 1. SELECT DISTINCT по колонке JSON (отсекает одинаковые массивы, которых в моем случае много). На бэке megre по всем массивам и unique. Время выполнения 0.57 секунды (учтено время от инициализации ORM до строки перед return)
Решение 2. Запрос со stackoverflow. 0.23 секунды. (учитывал только время запроса в phpmyadmin)
Решение 3. Реляционная форма данных. 0.03 секунды. (учитывал только время запроса в phpmyadmin)
Все 3 решения по скорости устраивают, запрос 2 среди имеющегося выглядит более костыльным

Comments

[Олег Правдин]

Иван, у меня нет задачи работать с 50м :) Сомневаюсь, что даже в 10 летней перспективе записей будет 1м т.к. 50к записей набирались ~4 года.

[Макс]

Олег Правдин, Я так и не понял, как получить простой список из уникальных значений через DISTINCT) У меня просто возвращаются ячейки, где порядок разный:
["Ягоды", "Фрукты", "Алкоголь"]
["Алкоголь", "Фрукты"]
["Алкоголь", "Шампусик"]

А мне нужно получить просто список: Якоды, Фрукты, Алкоголь, Шампусик.

У меня мускуль 5.7 =/

[FanatPHP]

Макс, это потому что у тебя не должно быть никаких "ячеек".
Надо купить себе букварь про базам данных и сделать нормальную структуру таблиц, а не эти "ячейки"

[Макс]

FanatPHP, у меня вынужденная мера, создавать пивотную таблицу мне не нужно. Не подскажешь, как запросом выбрать только уникальные значения json-массива?
Типичные отписки и ссылания на букварь с матчастью никак не помогут.

[FanatPHP]

Макс, КАКАЯ ЕЩЁ ПИВОТНАЯ ТАБЛИЦА?
тебе надо нормальную базу данных, а не "пивотную таблицу".
выкинуть весь этот джейсон и сделать нормальную реляционную схему, такую, к которой автор вопроса в итоге пришел