Laravel/MySQL JSON documents faster lookup using generated columns

Laravel 5.3 is shipped with built-in support for updating and querying JSON type database fields, the support currently fully covers MySQL 5.7 JSON type fields updates and lookups, this allows us to have the following:

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

This will bring all users who have a favorite color of red, here's a sample table structure:

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

You may also update the field like that:

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

In this post I'd like to show you how we can achieve faster lookups for data stored in JSON-type fields using MySQL generated columns.

As mentioned in MySQL manual:

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.

Let's see how we may create a generated column to store users favorite color for later indexing:

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

This will create a virtual column that'll be generated with every read operation, now let's add an index:

ALTER TABLE users 
ADD INDEX (meta_favorite_color);

The next time you query users' favorite colors you can point MySQL to scan the indexed column instead of the data stored in the JSON column.

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

Using database migrations

You can achieve the effect of the sql commands mentioned above using laravel database migrations as well:

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