New JSON-column where() and update() syntax in Laravel 5.3

Posted on July 08, 2016 | By Matt Stauffer


Warning: This post is over a year old. I don't always update old posts with new information, so some of this information may be out of date.

While Laravel has had the ability to cast your data to and from JSON since version 5.0, it was previously just a convenience—your data was still just stored in a TEXT field. But MySQL 5.7 introduced an actual JSON column type.

Laravel 5.3 introduces a simple syntax for lookups and updates based on the value of specific keys in your JSON columns.

Let's assume we have a table with a JSON column:

...
class CreateContactsTable extends Migration
{
    public function up()
    {
        Schema::create('contacts', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->json('meta');
            $table->timestamps();
        });
    }

We'll imagine that each contact has some foundational information like their name, but some other properties are flexible. The best way to store them might be a JSON column—like our meta column above.

We could imagine one contact (output to JSON for blog-post-readability) might look like this:

{
    "id": 1,
    "name": "Alphonse",
    "meta": {
        "wants_newsletter": true,
        "favorite_color": "red"
    }
}

So, let's get all of our users whose favorite color is red. As you can see below, we start with the column (meta), followed by an arrow (->), followed by the key name of the JSON property (favorite_color).

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

This means "look for every entry in the users table which has a JSON object stored in meta that has a key of favorite_color that's set to red."

What if we want to update Alphonse to no longer want the newsletter?

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

What's great here is, even if the wants_newsletter key wasn't previously set on this record, it will be now, and it'll be correctly set to false.

See the power? We can query based on properties in the JSON column and we can update individual pieces of the JSON column without having to know, or care about, the others. Brilliant.

Note: MariaDB does not have JSON columns, and PostgreSQL has JSON columns but this feature appears to not currently work on them. So consider this a MySQL 5.7+ feature for now.


Comments? I'm @stauffermatt on Twitter


Tags: laravel  •  laravel 5.3  •  eloquent