В этом посте мы собираемся исследовать тип данных JSON в MySQL 5.7 и во время погружения будем использовать фреймворк Laravel для построения запросов.
Для начала, создадим новую таблицу:
И добавим несколько значений:
Мы можем прочесть значения JSON-колонки используя простой синтаксис:
Получим следующий результат:
Как вы, возможно, заметили, результаты получены в виде строки в формате JSON, это означает, что вам нужно декодировать их перед выводом на экран.
Выполнение запросов в формате JSON осуществляется через оператор "->", слева размещая имя столбца оператора, а справа синтаксис пути.
Для представления документа в JSON-формате с последующим селектором, синтаксис PATH использует ведущую $ для указания на конкретные части документа. Вот различные пути для извлечения данных:
Если ключ не является допустимым идентификатором ECMAScript, он должен быть заключен в кавычки внутри пути.
Вы также можете использовать маску для запроса значений JSON. Допустим, мы имеем следующие данные:
Это работает также, как и в обычных колонках MySQL. Теперь, когда мы знаем как написать правильный путь для запроса и/или сортировки значений в JSON-формате, посмотрим некоторые примеры:
Если Вы используете фреймворк Laravel версии 5.2.23 или выше, Вы будете иметь возможность свободно использовать конструктор запросов для формирования запроса в формате JSON:
Если нет, то Вы нужно использовать **RAW**:
Во многих случаях, разработчики предпочитают базу данных NoSQL для специфических особенностей, гибкости и/или производительности, однако базы данных SQL являются предпочтительными и много крупных компаний полагаются на них при разработке производительных веб-приложений, используя для этого связку MySQL + (Mongo|Redis|и т.д.), но это добавляет сложности в стек. С введением типа данных JSON в MySQL, он стал своего рода гибридной базой данных SQL-NoSQL.
В примерах там, где видны «елочки» — нужно ставить «кавычки». Это Хабр так их обрабатывает.
Для начала, создадим новую таблицу:
CREATE TABLE `products` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` JSON,
`specs` JSON,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
И добавим несколько значений:
INSERT INTO products VALUES(
null,
'{"en": "phone", "it": "telefono"}',
'{"colors": ["black", "white", "gold"], "size": {"weight": 1, "height": 1}}'
);
INSERT INTO products VALUES(
null,
'{"en": "screen", "it": "schermo"}',
'{"colors": ["black", "silver"], "size": {"weight": 2, "height": 3}}'
);
INSERT INTO products VALUES(
null,
'{"en": "car", "it": "auto"}',
'{"colors": ["red", "blue"], "size": {"weight": 40, "height": 34}}'
);
Считывание значений JSON
Мы можем прочесть значения JSON-колонки используя простой синтаксис:
select
name->"$.en" as name,
specs->"$.size.weight" as weight,
specs->"$.colors" as colors
from products;
Получим следующий результат:
| name | weight | colors |
|---|---|---|
| 'phone' | 1 | ['black', 'white', 'gold'] |
| 'screen' | 2 | ['black', 'silver'] |
| 'car' | 40 | ['red', 'blue'] |
Как вы, возможно, заметили, результаты получены в виде строки в формате JSON, это означает, что вам нужно декодировать их перед выводом на экран.
json_decode( Products::selectRaw('name->"$.en" as name')->first()->name )
О синтаксисе
Выполнение запросов в формате JSON осуществляется через оператор "->", слева размещая имя столбца оператора, а справа синтаксис пути.
Для представления документа в JSON-формате с последующим селектором, синтаксис PATH использует ведущую $ для указания на конкретные части документа. Вот различные пути для извлечения данных:
- specs->"$.colors" вернет массив цветов
- specs->"$.colors[0]" вернет JSON-строку «black»
- specs->"$.non_existing" вернет NULL
- specs->"$.'key name with space'" если ключ содержит пробелы
Если ключ не является допустимым идентификатором ECMAScript, он должен быть заключен в кавычки внутри пути.
Использование подстановок
Вы также можете использовать маску для запроса значений JSON. Допустим, мы имеем следующие данные:
{"name": "phone", "price": 400, "sizes": [3, 4, 5]}
| Синтаксис | Результат | Примечание |
|---|---|---|
| specs->"$.*" | ['phone', [3, 4, 5], [{'name': 'black'}, {'name': 'gold'}]] | |
| specs->"$.sizes[*]" | [3, 4, 5] | То же, что и $.sizes |
| specs->"$.colors**.name" | ['black', 'gold'] | Синтаксис «префикс**суффикс» будет запрашивать все пути, начинающиеся с префикса и заканчивающиеся суффиксом. |
Запрос значения в формате JSON
Это работает также, как и в обычных колонках MySQL. Теперь, когда мы знаем как написать правильный путь для запроса и/или сортировки значений в JSON-формате, посмотрим некоторые примеры:
select name->"$.en" from products where name->"$.en" = "phone";
select name->"$.en" from products where name->"$.en" IN ("phone");
select specs->"$.size.weight" from products where specs->"$.size.weight" BETWEEN 1 AND 10;
select * from products ORDER BY name->"$.en";
Тип данных JSON в MySQL и фреймворк Laravel
Если Вы используете фреймворк Laravel версии 5.2.23 или выше, Вы будете иметь возможность свободно использовать конструктор запросов для формирования запроса в формате JSON:
Product::where('name->en', 'car')->first();
Product::whereIn('specs->size->weight', [1, 2, 3])->get();
Product::select('name->en')->orderBy('specs->size->height', 'DESC')->get();
Если нет, то Вы нужно использовать **RAW**:
Product::whereRaw('name->"$.en"', 'car')->first();
Вывод
Во многих случаях, разработчики предпочитают базу данных NoSQL для специфических особенностей, гибкости и/или производительности, однако базы данных SQL являются предпочтительными и много крупных компаний полагаются на них при разработке производительных веб-приложений, используя для этого связку MySQL + (Mongo|Redis|и т.д.), но это добавляет сложности в стек. С введением типа данных JSON в MySQL, он стал своего рода гибридной базой данных SQL-NoSQL.
От переводчика
В примерах там, где видны «елочки» — нужно ставить «кавычки». Это Хабр так их обрабатывает.
Комментарии (18)
Methos
13.03.2016 22:26+2То есть, там внутри будет какой-то индекс по полям и поиск будет быстрым?
Starche
14.03.2016 01:08Этот вопрос лично меня заинтересовал в первую очередь, но нет. не судьба
JSON columns cannot be indexed. You can work around this restriction by creating an index on a generated column that extracts a scalar value from the JSON column. See Secondary Indexes and Virtual Generated Columns, for a detailed example.
Может в будущем что-то хорошее и появится. Предложенные сейчас в качестве обхода генерируемые поля довольно интересно выглядят. Я, правда, пока не понял, куда их применять, но всё равно интересно
savostin
13.03.2016 23:57+2Ну почему каждый считает своим долгом выдумать новый синтаксис? Все эти доллары, звездочки… Больное воображение какое-то.
beduin01
Я плохо представляю себе ситуацию когда в реляционных БД удобно хранить JSON. Точнее я однажды пытался это сделать, но решение получилось до ужаса кривое и я предпочел https://www.arangodb.com
lair
Вам сложно себе представить ситуацию, когда на проекте уже есть всем устраивающая реляционная БД, и надо для какой-то задачи хранить данные произвольного формата (с точки зрения БД — вообще неструктурированные, черный ящик)?
beduin01
Хранение JSON в реляционной БД не сильно превосходит хранение там простых текстовых строк. Те же самые неудобства и сложности.
zelenin
возможность делать запросы по полям json и индексы — это полностью превосходит хранение простых текстовых строк.
lair
Но тем не менее текстовые строки мы в БД храним. Тогда почему не хранить и JSON?
Rathil
У нас на проекте были несколько мест, где нужно было сделать выборку из 16 таблиц (join). Все было круто, пока не вышла ситуация, когда нам нужно было делать подобные запросы n -раз за один запрос! Вот тут мы и почувствовали, как не сладко приходится нашему Postgre, когда у него залетали запросы по 56 килобайт и таких запросов было много. Тогда мы решили все эти таблицы просто перенести в одно поле, не json, простой текст, нам нужно было с ними работать только на чтение, без сортировок и условий.
Да, тот запрос разгрузился просто в десятки раз!
Конечно на плечи приложения упала логика корректного заполнения этого поля и его контроль, но мы ни разу не пожалели об этом!
Rathil
За один https request.
babylon
А я отлично представляю.А-ааа!
Helldar
Как-то раз нужно было хранить JSON-строку в базе. Создал колонку типа TEXT и обращался к ней через json_encode / json_decode. Проект был мал в масштабах, поэтому решение подходило более чем.