•   Новости. Советы. Уроки.
Блог / Советы и уроки / Быстрый поиск по JSON документам в Laravel/MySQL при помощи виртуальных полей.

Быстрый поиск по JSON документам в Laravel/MySQL при помощи виртуальных полей.

Mohamed Said
Виталий Николенко
26.08.2016

В Laravel 5.3 есть все для работы (запросов и обновления) с JSON полями в MySQL 5.7, выглядит это примерно так:

DB::table('users')->where('meta->favorite_color', 'red')->get();

Этот запрос выдаст выборку пользователей, у которых любимый цвет – красный. Структура таблицы при этом такая:

id name meta
1 Melisandre {"favorite_color": "red", "religion": "R'hllor, the Lord of Light"}

Также можно обновить поле таким образом:

DB::table('users')
    ->where('id', 1)
    ->update(['meta->origin' => 'Asshai']);

Matt Stauffer писал об этом недавно здесь

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

В документации MySQL пишут:

JSON столбцы не могут быть проиндексированы. Это ограничение можно обойти при помощи индекса на генерируемом столбце, который извлекает скалярное значение из JSON колонки.

Давайте посмотрим как же можно создать колонку, в который мы будем хранить любимый цвет наших пользователей и которую можно будет проиндексировать:

ALTER TABLE users 
ADD meta_favorite_color VARCHAR(50) 
    AS (meta->>"$.favorite_color");

Этот запрос даст нам виртуальную колонку, которая будет создаваться каждый раз при операциях чтения. Теперь давайте добавим индекс:

ALTER TABLE users 
ADD INDEX (meta_favorite_color);

В следующий раз при запросе можно будет обращаться к этой проиндексированной колонке а не к данным в JSON поле

DB::table('users')->where('meta_favorite_color', 'red')->get();

То же самое при помощи миграций

Тот же эффект, что и запрос выше можно достичь при помощи Laravel миграций:

Schema::table('users', function (Blueprint $table) {
    $table->string('meta_favorite_color')->virtualAs('meta->>"$.favorite_color"');
    $table->index('meta_favorite_color');
});