Jul 8, 2016 | laravel, laravel 5.3, eloquent

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

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

!
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 contacts 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('contacts')
    ->where('meta->favorite_color', 'red')
    ->get();

This means "look for every entry in the contacts 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('contacts')
    ->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

This is part of a series of posts on New Features in Laravel 5.3:

Introducing Laravel Echo: An In-Depth Walk-Through

Jun 16, 2016 | laravel, laravel 5.3, echo, websockets
The new $loop variable in Laravel 5.3

Jun 27, 2016 | laravel, laravel 5.3
Customizing additional parameters in FirstOrCreate in Laravel 5.3

Jun 29, 2016 | laravel, laravel 5.3, eloquent
The new cache() global helper in Laravel 5.3

Jul 6, 2016 | laravel, laravel 5.3
Advanced operations with Collection::where in Laravel 5.3

Jul 8, 2016 | laravel, laravel 5.3
New JSON-column where() and update() syntax in Laravel 5.3

Jul 8, 2016 | laravel, laravel 5.3, eloquent
Image dimension validation rules in Laravel 5.3

Jul 25, 2016 | laravel, laravel 5.3
Customizing pagination templates in Laravel 5.3

Jul 26, 2016 | laravel, laravel 5.3
Routing changes in Laravel 5.3

Jul 27, 2016 | laravel, laravel 5.3, routing
5.3 feature announcement notes from Laracon

Jul 27, 2016 | laravel, laravel 5.3, laravel scout, laravel passport, mailable
Introducing Laravel Scout

Jul 29, 2016 | laravel, laravel 5.3, laravel scout
Introducing Laravel Passport

Jul 30, 2016 | laravel, laravel 5.3, laravel passport, oauth
Introducing Mailables in Laravel 5.3

Aug 5, 2016 | laravel, laravel 5.3, mail, laravel mailables
Directory structure changes in Laravel 5.3

Aug 8, 2016 | laravel, laravel 5.3
The new Notification system in Laravel 5.3

Oct 19, 2016 | laravel, laravel 5.3, laravel notifications, notifications
Using Vue in Laravel 5.3, with the Vue bootstrap and sample component

Dec 21, 2016 | laravel, laravel 5.3, vuejs, vueify, authorization
Update to queue workers in Laravel 5.3

Dec 21, 2016 | laravel, laravel 5.3, queues
Defining console commands via closure in Laravel 5.3

Jan 30, 2017 | laravel, laravel 5.3, artisan

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

Subscribe