Leveraging virtual generated columns

When MySQL released native JSON column types in 5.7.8, it provided developers with an easier way to store and retrieve data for applications that used it as the storage engine. Laravel quickly supported it all the way back in 5.3 in data migrations and querying with Eloquent.

While being a convenient way to store new values without adding additional columns to your data model, one of the big drawbacks is JSON columns cannot be indexed. For smaller applications, the trade-off is minimal, but as we move into more enterprise-level applications, scaling can quickly become an issue as searching this column will require full table scans.

Luckily, there is a feature also present in MySQL starting with 5.7 called virtual generated columns. What they allow us to do is everytime the JSON column data is saved, MySQL will automatically populate a virtual column with some piece of that data based on how it is defined. The best part is, virtual columns are indexable, allowing your application to scale with ease.

Let’s take a look at how this works in Laravel using a hypothetical User data model and storing multiple phone numbers in a JSON column. We have a use case of a search page of users and we want to look them up by their mobile number.

First, we need to add the virtual column to our users table:

Schema::table('users', function (Blueprint $table) {
    $table->string('mobile_number_virtual')
        ->nullable()
        ->virtualAs("json_unquote(json_extract(phone_numbers, '$.mobile'))");
 
    $table->index('mobile_number_virtual');
});

Breaking this migration down, most of it should make sense if you work with Laravel daily. However, you may be wondering where the expression comes from in the virtualAs() method. To get that, we write an Eloquent query and output the underlying SQL statement.

User::where(‘phone_numbers->mobile’, $searchTerm)->get();
 
// underlying query
select * from users
where json_unquote(json_extract(phone_numbers, '$.mobile'))

What makes getting the virtual column definition by grabbing the underlying query so important is that MySQL will automatically match the query statement to the virtual column definition and use the index you defined. The virtual column and its name can stay completely invisible to developers and all they need to do is write Eloquent queries using the JSON format they already know. How cool is that?

However, we will get a MySQL error anytime we try to create or update the user because the model adds the column to the $attributes array, which then tries to write to the column on any INSERT and UPDATE calls and is not allowed.

To get around this, we need to understand what happens in the core framework when you create or update a model. Digging into the Illuminate\Database\Eloquent\Model class, we can see the save() method is called anytime these operations happen. Since all your applications models extend this class, this makes overriding it quite easy. A custom trait is preferred here as we can have any model implement it when virtual columns are needed.

Let’s define an array with the virtual columns on the model so we know what to exclude when saving:

/**
 * The virtual generated columns on the model
 *
 * @var array
 */
protected $virtualColumns = [
    'mobile_number_virtual',
];

Now, let’s create our trait and have our User model implement it:

namespace App\Models\Concerns;
 
trait HasVirtualColumns
{
    public function save(array $options = [])
    {
        if (isset($this->virtualColumns)) {
            $this->attributes = array_diff_key($this->attributes, array_flip($this->virtualColumns));
        }
 
        $return = parent::save($options);
 
        return $return;
    }
}

We are checking to see if the model has the virtualColumns property assigned to it and then diffing it out of the attributes array before running the parent class's save() method.

The final step is to leverage this trait in our User model:

namespace App\Models
 
use App\Models\Concerns\HasVirtualColumns
 
Class User
{
   Use HasVirtualColumns;
   //
}

Leveraging virtual columns for often used queries of JSON data opens up tremendous possibilities for ensuring your queries can scale effortlessly as your dataset grows.