Jul 8, 2016 | laravel, laravel 5.3, eloquent

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

Series

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:

  1. Jun 16, 2016 | laravel, laravel 5.3, echo, websockets
  2. Jun 27, 2016 | laravel, laravel 5.3
  3. Jun 29, 2016 | laravel, laravel 5.3, eloquent
  4. Jul 6, 2016 | laravel, laravel 5.3
  5. Jul 8, 2016 | laravel, laravel 5.3, eloquent
  6. Jul 25, 2016 | laravel, laravel 5.3
  7. Jul 26, 2016 | laravel, laravel 5.3
  8. Jul 27, 2016 | laravel, laravel 5.3, routing
  9. Jul 27, 2016 | laravel, laravel 5.3, laravel scout, laravel passport, mailable
  10. Jul 29, 2016 | laravel, laravel 5.3, laravel scout
  11. Jul 30, 2016 | laravel, laravel 5.3, laravel passport, oauth
  12. Aug 5, 2016 | laravel, laravel 5.3, mail, laravel mailables
  13. Aug 8, 2016 | laravel, laravel 5.3
  14. Oct 19, 2016 | laravel, laravel 5.3, laravel notifications, notifications
  15. Dec 21, 2016 | laravel, laravel 5.3, vuejs, vueify, authorization
  16. Dec 21, 2016 | laravel, laravel 5.3, queues
  17. Jan 30, 2017 | laravel, laravel 5.3, artisan

Subscribe

For quick links to fresh content, and for more thoughts that don't make it to the blog.