Декодирование типа данных JSON MySQL

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

  • 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.

Автор: Андрей Николаевич, Источник
 

Добавить комментарий


Защитный код
Обновить