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

Laravel 5.3 introduces a simple 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 
    public function up()
        Schema::create('contacts', function (Blueprint $table) {

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,
        "_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 name of the JSON property (favorite_color).

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

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 Alphonse to no longer want the newsletter?

    ->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.

Source link


Please enter your comment!
Please enter your name here