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

(This is part of a series of posts on New Features in Laravel 5.3.)

  1. Introducing Laravel Echo: An In-Depth Walk-Through
  2. The new $loop variable in Laravel 5.3
  3. Customizing additional parameters in FirstOrCreate in Laravel 5.3
  4. The new cache() global helper in Laravel 5.3
  5. New JSON-column where() and update() syntax in Laravel 5.3
  6. Advanced operations with Collection::where in Laravel 5.3
  7. Image dimension validation rules in Laravel 5.3
  8. Customizing pagination templates in Laravel 5.3
  9. 5.3 feature announcement notes from Laracon
  10. Routing changes in Laravel 5.3
  11. Introducing Laravel Scout
  12. Introducing Laravel Passport
  13. Introducing Mailables in Laravel 5.3
  14. Directory structure changes in Laravel 5.3
  15. The new Notification system in Laravel 5.3
  16. Update to queue workers in Laravel 5.3
  17. Using Vue in Laravel 5.3, with the Vue bootstrap and sample component
  18. Defining console commands via closure in Laravel 5.3

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

Matt Stauffer headshot

Hi, I'm Matt Stauffer.

I'm partner & technical director at Tighten Co.

You can find me on Twitter at @stauffermatt


Like what you're reading?

I wrote an entire 450+ page book for O'Reilly: Laravel: Up and Running.

You can order the eBook or print book today.