[Перевод] Декодирование типа данных JSON MySQL13.03.2016 23:03

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

image

Во-первых, создадим новую таблицу: 

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 использует ведущую $ для указания на конкретные части документа. Вот различные пути для извлечения данных:

  1. specs→»$.colors» вернет массив цветов
  2. specs→»$.colors[0]» вернет JSON-строку «black»
  3. specs→»$.non_existing» вернет NULL
  4. 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.

© Habrahabr.ru