Раздел 34 · MySQL с нуля

JSON и выбор правильного типа

junior~25 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

В прошлом уроке в колонке лежало одно значение: дата, время, момент. Но у фильма бывают вещи, которые в одно значение не укладываются. У «Брата» два жанра, у «Москвы» есть «Оскар», у «Ёжика» нет наград вовсе. Заводить под каждую мелочь отдельную колонку значит плодить пустые клетки, и для таких данных в MySQL есть тип JSON. Сегодня учимся доставать из него значения, искать по нему и менять его, а в конце собираем всё, что знаем о типах, в одну таблицу выбора.

Конверт с анкетой

Представь картотеку кинотеатра. На каждой карточке фильма есть графы: название, год, длительность. А к карточке скрепкой приколот конверт с анкетой, где каждый пишет что хочет: жанры списком, страну, награды. Карточка строгая, конверт свободный.

Колонка films.info и есть такой конверт. Внутри JSON: объект с ключами genres, country, awards.

SELECT title, info FROM films WHERE id IN (1, 2, 6);
+-------------------------------------------+----------------------------------------------------------------------------+
| title                                     | info                                                                       |
+-------------------------------------------+----------------------------------------------------------------------------+
| Брат                                      | {"genres": ["драма", "криминал"], "country": "Россия"}                     |
| Москва слезам не верит                    | {"awards": ["Оскар"], "genres": ["драма", "мелодрама"]}                    |
| Новые имена                               | NULL                                                                       |
+-------------------------------------------+----------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Присмотрись ко второй строке. В наборе данных «Москву» записали как {"genres": ..., "awards": ...}, а MySQL вернул ключи в другом порядке. Тип JSON хранит документ не текстом, а в разобранном двоичном виде, и ключи раскладывает по-своему: сначала короткие, потом длинные, при равной длине по алфавиту. Порядок ключей в объекте ничего не значит, полагаться на него нельзя. У «Новых имён» анкеты нет вовсе, там NULL.

Достаём значение по пути

Чтобы добраться до значения внутри, пишут путь. $ означает весь документ, $.country ключ country, $.genres[0] первый элемент списка жанров, нумерация с нуля.

Оператор -> достаёт значение по пути и оставляет его JSON, а ->> ещё и снимает кавычки, превращая в обычную строку:

SELECT title, info->'$.country', info->>'$.country' FROM films WHERE id = 1;
+----------+-------------------+--------------------+
| title    | info->'$.country' | info->>'$.country' |
+----------+-------------------+--------------------+
| Брат     | "Россия"          | Россия             |
+----------+-------------------+--------------------+
1 row in set (0.00 sec)

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

SELECT title, info->'$.genres' AS genres, info->>'$.country' AS country FROM films;
+-------------------------------------------+------------------------------------------+--------------+
| title                                     | genres                                   | country      |
+-------------------------------------------+------------------------------------------+--------------+
| Брат                                      | ["драма", "криминал"]                    | Россия       |
| Москва слезам не верит                    | ["драма", "мелодрама"]                   | NULL         |
| Ирония судьбы                             | ["комедия", "мелодрама"]                 | СССР         |
| Сталкер                                   | ["фантастика", "драма"]                  | СССР         |
| Ёжик в тумане                             | ["мультфильм"]                           | СССР         |
| Новые имена                               | NULL                                     | NULL         |
| Солярис                                   | ["фантастика", "драма"]                  | СССР         |
+-------------------------------------------+------------------------------------------+--------------+
7 rows in set (0.00 sec)

У «Москвы» нет ключа country, и вместо ошибки пришёл NULL. Это важно: путь к несуществующему ключу молча даёт NULL.

-> это короткая запись функции JSON_EXTRACT. Рядом с ней часто нужна JSON_LENGTH, длина списка:

SELECT title, JSON_EXTRACT(info, '$.genres[0]') AS first_genre, JSON_LENGTH(info, '$.genres') AS genre_count FROM films;
+-------------------------------------------+------------------------+-------------+
| title                                     | first_genre            | genre_count |
+-------------------------------------------+------------------------+-------------+
| Брат                                      | "драма"                |           2 |
| Москва слезам не верит                    | "драма"                |           2 |
| Ирония судьбы                             | "комедия"              |           2 |
| Сталкер                                   | "фантастика"           |           2 |
| Ёжик в тумане                             | "мультфильм"           |           1 |
| Новые имена                               | NULL                   |        NULL |
| Солярис                                   | "фантастика"           |           2 |
+-------------------------------------------+------------------------+-------------+
7 rows in set (0.00 sec)

Ищем по содержимому

Советские фильмы ищем сравнением, через ->>:

SELECT title FROM films WHERE info->>'$.country' = 'СССР';
+---------------------------+
| title                     |
+---------------------------+
| Ирония судьбы             |
| Сталкер                   |
| Ёжик в тумане             |
| Солярис                   |
+---------------------------+
4 rows in set (0.00 sec)

А как найти драмы, если жанр лежит в списке? Сравнение = 'драма' не сработает: список не строка. Для этого есть MEMBER OF, «входит ли значение в список»:

SELECT title FROM films WHERE 'драма' MEMBER OF (info->'$.genres');
+-------------------------------------------+
| title                                     |
+-------------------------------------------+
| Брат                                      |
| Москва слезам не верит                    |
| Сталкер                                   |
| Солярис                                   |
+-------------------------------------------+
4 rows in set (0.00 sec)

Здесь нужен ->, а не ->>: MEMBER OF ждёт JSON-список. То же умеет JSON_CONTAINS, но второй аргумент у неё тоже JSON, поэтому строку пишут в двойных кавычках внутри одинарных:

SELECT title FROM films WHERE JSON_CONTAINS(info->'$.genres', '"фантастика"');
+----------------+
| title          |
+----------------+
| Сталкер        |
| Солярис        |
+----------------+
2 rows in set (0.00 sec)

Меняем документ

Переписывать весь документ ради одного ключа не нужно. JSON_SET ставит значения по путям: если ключ есть, заменяет, если нет, добавляет. Дадим «Солярису» страну и награду:

UPDATE films SET info = JSON_SET(info, '$.country', 'СССР', '$.awards', JSON_ARRAY('Золотой медведь')) WHERE id = 7;
SELECT info FROM films WHERE id = 7;
+------------------------------------------------------------------------------------------------------------------------+
| info                                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------+
| {"awards": ["Золотой медведь"], "genres": ["фантастика", "драма"], "country": "СССР"}                                  |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Страна у «Соляриса» уже была, её JSON_SET перезаписал тем же значением. Ключа awards не было, он появился, и снова в начале: короткие ключи впереди. JSON_ARRAY собирает список из аргументов.

Дописать элемент в конец списка помогает JSON_ARRAY_APPEND:

UPDATE films SET info = JSON_ARRAY_APPEND(info, '$.genres', 'триллер') WHERE id = 1;
SELECT info->'$.genres' FROM films WHERE id = 1;
+------------------------------------------------------+
| info->'$.genres'                                     |
+------------------------------------------------------+
| ["драма", "криминал", "триллер"]                     |
+------------------------------------------------------+
1 row in set (0.00 sec)

Раз отсутствующий ключ даёт NULL, по нему легко отобрать строки, где ключ есть. Кому из фильмов достались награды, и какая первая:

SELECT title, info->'$.awards[0]', info->>'$.awards[0]' FROM films WHERE info->'$.awards' IS NOT NULL;
+-------------------------------------------+---------------------------------+-------------------------------+
| title                                     | info->'$.awards[0]'             | info->>'$.awards[0]'          |
+-------------------------------------------+---------------------------------+-------------------------------+
| Москва слезам не верит                    | "Оскар"                         | Оскар                         |
| Солярис                                   | "Золотой медведь"               | Золотой медведь               |
+-------------------------------------------+---------------------------------+-------------------------------+
2 rows in set (0.00 sec)

«Солярис» попал в список, потому что минуту назад мы дали ему награду через JSON_SET. Условие IS NOT NULL отсеяло и фильмы без ключа awards, и «Новые имена» без анкеты.

Кривой JSON не пройдёт

В колонку JSON можно записать строку, но MySQL сначала её разберёт. Забытая скобка:

UPDATE films SET info = '{"genres": ["документальный"' WHERE id = 6;
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or ']' after an array element." at position 42 in value for column 'films.info'.

Позиция 42 считается в байтах, а не в буквах. Кириллица в UTF-8 занимает по два байта, поэтому символов в строке всего 28, а ошибка «на 42-м месте». Если ищешь, где сломалось, помни об этом.

Ключ без кавычек тоже ошибка, хотя в JavaScript такое пишут:

UPDATE films SET info = '{genres: []}' WHERE id = 6;
ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'films.info'.

Чтобы не собирать JSON руками и не ошибаться в кавычках, бери JSON_OBJECT: он строит объект из пар «ключ, значение»:

UPDATE films SET info = JSON_OBJECT('genres', JSON_ARRAY('альманах'), 'country', 'Россия') WHERE id = 6;
SELECT title, info FROM films WHERE id = 6;
+-----------------------+-------------------------------------------------------------+
| title                 | info                                                        |
+-----------------------+-------------------------------------------------------------+
| Новые имена           | {"genres": ["альманах"], "country": "Россия"}               |
+-----------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

Почему не сложить всё в JSON

Раз конверт принимает что угодно, хочется отказаться от колонок совсем: одна колонка data JSON, и никаких ALTER TABLE. Посмотри, что будет при опечатке. Ошибёмся в имени колонки:

SELECT title FROM films WHERE contry = 'СССР';
ERROR 1054 (42S22): Unknown column 'contry' in 'where clause'

База сразу сказала, что колонки нет. А теперь та же опечатка в ключе JSON:

SELECT title FROM films WHERE info->>'$.contry' = 'СССР';
Empty set (0.00 sec)

Ни ошибки, ни предупреждения, только пустой ответ. Запрос с такой опечаткой спокойно уедет в рабочий код. И это не единственная потеря. У ключа внутри JSON нет типа: в одной строке год запишут числом, в другой строкой. Нельзя сказать «ключ обязателен» через NOT NULL, нельзя поставить на ключ внешний ключ или UNIQUE. Индекс по ключу построить можно, но через обходные приёмы, а по колонке он ставится одной строкой.

Правило такое: всё, по чему ищешь, сортируешь и связываешь таблицы, живёт в обычных колонках. В JSON уходит необязательное и разнородное, то, что читают целиком вместе с карточкой.

Шпаргалка: какой тип выбрать

Соберём уроки блока в одну таблицу:

Что хранишьТипПочему
Номер, количество, возрастINT, SMALLINT и родняцелое, считается быстро, есть UNSIGNED
Деньги, ценыDECIMAL(10, 2)точный, без ошибок округления как у DOUBLE
Имя, email, городVARCHAR(N)строка до N символов, длину выбирай с запасом
Длинный текст: описание, отзывTEXTне нужен предел длины
Короткий фиксированный список: статус, рейтингENUMбаза не пустит значение вне списка
День без времениDATEдень рождения, дата регистрации
Событие по местным часамDATETIMEне зависит от часового пояса, живёт до 9999 года
Разнородные необязательные сведенияJSONлюбая вложенность, но без типов и ограничений

Про целые и DECIMAL подробно в уроке про числа, про VARCHAR и ENUM в уроке про строки. Если сомневаешься, бери самый узкий тип, который точно вместит данные, и не прячь в JSON то, что можно положить в колонку. В PostgreSQL у JSON есть брат посильнее, jsonb с особыми индексами, о нём в уроке PostgreSQL про jsonb.

Что запомнить

  • Колонка JSON проверяет документ при записи: кривой текст не пройдёт, а позиция ошибки считается в байтах.
  • -> возвращает значение JSON в кавычках, ->> снимает кавычки и отдаёт обычную строку.
  • MEMBER OF и JSON_CONTAINS ищут по содержимому массивов, JSON_SET меняет документ.
  • Ключи объекта MySQL хранит отсортированными: сначала короткие, поэтому порядок в ответе может отличаться от вставки.
  • В JSON кладут редкие и разнородные свойства, а то, по чему ищут и что связывают, держат в обычных колонках.

Попробуй сам

В песочнице достанем страну, найдём драмы, допишем жанр «Брату» и проверим, что кривой JSON не пройдёт:

Найди фильмы, у которых больше одного жанра, через JSON_LENGTH. Выведи первую награду через info->>'$.awards[0]' и посмотри, что вернётся у фильмов без наград. Запиши «Новым именам» анкету через JSON_OBJECT и проверь, в каком порядке MySQL сохранил ключи.

Проверь себя

Задание

Дальше

Типы колонок разобраны, пора собирать из них таблицы. В следующем уроке пишем CREATE TABLE с нуля, разбираемся с первичным ключом и смотрим, как InnoDB раскладывает строки по нему.

домашка

Домашка