Блог

Главная - Вопросы по программированию - Как извлечь максимальное значение из столбца json в mysql

Как извлечь максимальное значение из столбца json в mysql

#mysql #json

#mysql #json

Вопрос:

У меня есть таблица под названием упражнения, которая имеет следующую структуру

exercise_id Наборы
1 [{«повторения»: «50»}, {«повторения»: «50»}, {«повторения»: «50»}, {«повторения»: «50»}]
2 [{«повторения»: «25»}, {«повторения»: «25»}, {«повторения»: «25»}, {«повторения»: «25»}]
3 [{«повторения»: «50»}, {«повторения»: «50»}, {«повторения»: «80»}, {«повторения»: «50»}]

Столбцы наборов имеют тип JSON. Я хочу получить упражнение с максимальным количеством повторений, то есть exercise_id 3 с 80 повторениями.

ВЕРСИЯ MYSQL 5.7.24

Комментарии:

1. Вам придется использовать JSON_TABLE() . Но было бы проще, если бы вы хранили эти данные в обычных строках и столбцах и перестали использовать JSON.

2. Почему бы просто не нормализовать вашу схему?

3. В нем уже есть огромное количество данных, и для этого потребуется серьезный рефакторинг.

4. Тогда нельзя терять время. Если вы храните данные таким образом, что не знаете, как запрашивать, вам нужно так или иначе их реорганизовать.

5. Согласен @BillKarwin. Будет искать рефрактор

Ответ №1:

Мы уже обсуждали это в комментариях выше, но для записи я не могу придумать хорошего способа решить этот запрос в MySQL 5.7, версии, которую вы используете.

В MySQL 8.0 вы должны использовать JSON_TABLE():

 SELECT e.*
FROM exercises,
JSON_TABLE(e.sets, '$[*]' COLUMNS(
  repetitions INT PATH '$.repetitions'
)) AS j
ORDER BY j.repetitions DESC LIMIT 1;

Но поскольку вы не используете MySQL 8.0, и я предполагаю, что обновление до MySQL 8.0 не входит в ваши краткосрочные планы, вам нужно другое решение.

Решение, которое я бы использовал, — забыть о хранении данных в формате JSON. Реорганизуйте в обычную таблицу с одной строкой на набор упражнений.

 CREATE TABLE exercise_sets (
  exercise_id INT,
  set_id INT,
  repetitions INT
  PRIMARY KEY (exercise_id, set_id),
  KEY (repetitions)
);

Тогда запрос намного проще написать:

 SELECT e.*
FROM exercise_sets AS s
JOIN exercises AS e USING (exercise_id)
ORDER BY s.repetitions DESC LIMIT 1;