Use MySQL JSON field in Laravel

I have been working on an application which needs to store a lot of metadata about user profile, we discussed with the team about using NoSQL database (MongoDB) since its a JSON data set which can be extended and customized for the specific user profile due to its schemaless structure. As we know from MySQL 5.7.8 JSON field types are supported natively, not only we can store data as JSON but we can run the where clauses on it. So we used this fields type instead of using a full NoSQL DB, Let’s see how we can use it in a Laravel app.

Requirement

You must have MySQL 5.7.8 or greater installed on your dev environment, if you are using MariaDB JSON fields will be supported in MariaDB v 10.2.

Creating Laravel project

Let’s create a fresh Laravel project and set up the credential to connect to MySQL database. Once that’s done, modify our user’s tables migration which comes out of the box with Laravel 5.4 installation. open the migration file and add one JSON meta column.

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('email')->unique();
        $table->string('password');
        $table->rememberToken();
        $table->json('meta')->nullable();
        $table->timestamps();
    });
}

Now run the php artisan migrate command, it should run without any error.

User Profile Meta

Here is what our user metadata can hold, its best that you only keep data which will be not going to be searched and not going to be query through where clauses, its possible to perform a where query on JSON field but I would say if you are going to perform sorting on a field you should add it as column on table which will give you more optimized query.

JSON value fields cannot be indexed, so avoid using it on columns which are updated or searched regularly

Here a sample of JSON structure we are going to store as users meta.

{
    "gender": "Male",
    "country": "India",
    "bio": {
        "summery": "Lorem ipsum dolor sit amet, consectetur adipisicing elit. A labore voluptatem ipsam velit alias, rem in voluptates, suscipit sapiente est.",
        "full": "Lorem ipsum dolor sit amet, consectetur adipisicing elit. Inventore est dolor, et optio sint nulla amet consequatur assumenda excepturi eius! Lorem ipsum dolor sit amet, consectetur adipisicing elit. Inventore est dolor, et optio sint nulla amet consequatur assumenda excepturi eius! Eos delectus in architecto dolor blanditiis adipisci laudantium repellendus error."
    },
    "skills": ["PHP", "Laravel", "MySQL", "VueJS", "JavaScript"]
}

This JSON structure can be easily customized and extended in future if needed, now let’s see how we can query user from a country.

User Model

We need to cast meta fields as JSON,  edit User model and cast meta as array which will turn this into JOSN.

protected $casts = [
     'id' => 'int',
     'meta' => 'array'
];

Seed Data

Before we can play with JSON type column lets seed some data, edit model factory of the User and add fields needed. Open /database/factories/ModelFactory.php

$factory->define(App\User::class, function (Faker\Generator $faker) {
    static $password;

    return [
        'name' => $faker->name,
        'email' => $faker->unique()->safeEmail,
        'meta' => [
            "gender" => $faker->randomElement(['Male', 'Female']),
            "country" => $faker->country,
            "bio" => [
                "summery" => $faker->realText(),
                "full" => $faker->realText(800)
            ],
            "skills" => $faker->randomElements(["PHP", "Laravel", "MySQL", "VueJS", "JavaScript"], rand(2,4))
        ],
        'password' => $password ?: $password = bcrypt('secret'),
        'remember_token' => str_random(10),
    ];
});

Now we can create some dummy data using tinker, run following command to create some users.

# migrate database
php artisan migrate

# open tinker
php artisan tinker

# create 60 dummy users using factory
factory(App\User::class, 60)->create()

Now we have some data to play with. Let’s see a daily CRUD (Create, Read, Update, Delete) operations using our users meta JSON columns.

INSERT data into JSON field

Thanks to Laravel attribute casting, serialization and deserialization of JSON columns are easy, we have casted meta field as array, now inserting data into this field as JSON is simple:

Open the tinker by running php artisan tinker in terminal and test the insert like this.

>>> $data = factory(App\User::class)->make(['password' => bcrypt('secret')])->makeVisible('password');
=> App\User {#706
     name: "Kirsten Reilly",
     email: "marvin.armand@example.net",
     meta: "{"gender":"Male","country":"Oman","bio":{"summery":"Dodo solemnly, rising to its feet?' 'In my youth,' said the Mock Turtle"},"skills":["Laravel","MySQL","VueJS","PHP"]}",
     password: "$2y$10$K4RTr5dP/lTbMVUqL.YB5.iut0DYOTvoDDULwDqbtXMaLQXmgymyW",
   }
>>> App\User::create($data->toArray())
=> App\User {#671
     name: "Kirsten Reilly",
     email: "marvin.armand@example.net",
     updated_at: "2017-06-29 03:25:10",
     created_at: "2017-06-29 03:25:10",
     id: 63,
   }

As you can see its created the user, but meta column is null. Why is so, let’s see next.

Laravel Mass Assignment

If you want to try creating meta field using $user->create(['meta->bio->full' => 'New Lorem ipsum']) or updating it using $user->update(['meta->bio->full' => 'New Lorem ipsum']) this won’t work, since we haven’t told Eloquent about which fields can be mass assigned, its a security feature provided by Laravel.

You can add meta fields as $fillable or reverse is $guarded field so you can use above create/update method to change the content of JSON fields.

If you are directly using DB::table('users')->create(array) facade which is Query Builder this will work since there is no mass assignment protection.

Open the User model and add meta field in it.

protected $fillable = [
  'name', 'email', 'password', 
  'meta'
];

This will allow meta field to be mass assigned. Now exit out of tinker and re launch it so that your code change can take effect. If you run above line of codes you will see now its working and meta field has JSON values.

SELECT data from JSON field

The real fun comes with the where clause on JSON fields given by MySQL 5.7, let’s try selecting all user from a country and by gender.

# To get all user from India
App\User::where('meta->country', 'India')->get()

# To get all the female users
App\User::where('meta->gender', 'Female')->get()

As you can see we can access path of the JSON document using -> syntax, so to access gender in this case you can call meta->gender and for full profile do meta->profile->full.

You can also user LIKE operator to perform search.

# Get all the user who has Vue in skills
App\User::where('meta->skills', 'like',  '%Vue%')->get()

MySQL 5.7 provides some functions which you can use to perform a search on JSON docs.

Run this into tinker to see what queries Eloquent is running under the hood, DB::listen(function ($query) { dump($query->sql); dump($query->bindings); dump($query->time); });

JSON_CONTAINS() function accepts the JSON field being searched and another to compare against. It returns 1 when a match is found, e.g.

# Get all the user skilled with Laravel
App\User::whereRaw('JSON_CONTAINS(meta->"$.skills", \'["Laravel"]\')')->get();

Another one is JSON_SEARCH() function returns the path to the given match or NULL when there’s no match. It is passed the JSON document being searched, 'one' to find the first match or 'all' to find all matches, and a search string, e.g.

# Get all the user skilled with PHP
App\User::whereRaw('JSON_SEARCH(meta->"$.skills", "one", "PHP%") IS NOT NULL')->get();

You can also select a JSON path as columns alias like its done form normal columns.

# Selecting json path as columns 
App\User::select('id', 'name', 'meta->gender as sex', 'meta->skills as skills')->get()

Comparison and Ordering of JSON Values

JSON values can be compared using the =, <, <=, >, >=, <>, !=, and <=> operators.

UPDATE JSON fields data

We will be updating our meta field most of the time, let’s see how can we do that.

// Update JSON data
$user = App\User::first();
$user->meta = [ 'bio' => [ 'full' => 'Lorem ipsum dolor sit amet...' ] ];
$user->save();

This will set meta fields with JSON like this.

{"bio":{"full":"Lorem ipsum dolor sit amet..."}}

Keep in mind above assignment will replace the content of meta field, if you wanted to update just part of JSON document you should do these instead.

Updating Part of JSON document

Let’s run following command in tinker to see how we can update the bio summary of a user.

# Get the user
$user = App\User::find(2)

// Update the bio summery
$user->update(['meta->bio->summery' => 'New sort bio goes here, pretty neat hah!'])

As you can see nothing happened, let’s try another way

# Update bio using attribute manipulation
$user['meta->bio->summery'] = 'New sort bio goes here, pretty neat hah!'
$user->save();

This works, but what if you wanted to make earlier update() call work, let’s try making this JSON path fillable, add 'meta->bio->summery' into User model $fillable array and try again by relaunching tinker and running same code, now it’s working.

You need to relaunch tinker every time you make any change in code so it can detect your changes.

Unfortunately you have to define all the path you want to make fillable which can be tedious since JSON structure can get very complex with lots of properties and nested properties, luckily there is another way, you can use forceFill() which will bypass the mass assignment check to perform update on any JSON path, if path is not there, it will be created and if it’s present it will be updated accordingly.

# This works and updates table
$user->forceFill(['meta->bio->summery' => 'Event newer bio goes here, pretty dope hah!'])

# Save the changes
$user->update()

You can also use classical way which is covered in Laravel docs 

Besides these, there are some native functions provided by MySQL 5.7.8 to manipulate JSON document. Here are some examples.

JSON_SET(doc, path, val[, path, val]...)  inserts or updates data in the document

JSON_INSERT(doc, path, val[, path, val]...) inserts data into the document

JSON_REPLACE(doc, path, val[, path, val]...) replaces data in the document

JSON_MERGE(doc, doc[, doc]...) merges two or more documents

JSON_ARRAY_APPEND(doc, path, val[, path, val]...) appends values to the end of an array

JSON_ARRAY_INSERT(doc, path, val[, path, val]...) inserts an array within the document

DELETE JSON document path

Whenever you need to delete some path from your JSON document you can use the native functionJSON_REMOVE(). Let’s try to delete complete skills array for a user.

// Remove skills from a user 
DB::table('users')->where('id', 17)->update(['meta' => DB::raw('JSON_REMOVE(meta, "$.skills")')])

Accessor for JSON fields

Let say you wanted to access all the skills of a user like $user->skills, currently there is no accessor for this so it won’t work, let’s add one.

Let’s define accessor and add this skills to $appends array of a model, so it will be added as a column on JSON and array serializations.

class User extends Authenticatable
{
    use Notifiable;

    /**
     * The accessors to append to the model's 
        array form.
     *
     * @var array
     */
    protected $appends = ['skills'];

    public function getSkillsAttribute() {
        return array_get($this->meta, 'skills', []);
    }
}

That’s it, now you can access skills $user->skills like a property on users model.

Conclusion

By using JSON filed in MySQL 5.7.8 we can utilize flexibility of non-relational database within MySQL, and Laravel, as always make it very easy to work with JSON fields, it’s tempting for new commerce to use JSON field for everything, I would suggest you should always use only for field type to store data which is not going to search and aggregated like user preferences, settings, users stats or any other sparsely-populated data or custom attributes. I hope it was fun for you, let me know if you have any comment below.

4 Responses to “Use MySQL JSON field in Laravel”

  1. Milrak Pereira Pessoa

    Perfect, but I still have questions, how will it look for information in a json with data collection?
    Example:
    {
    “links”: [{
    “rota”: “reserva”,
    “sref”: “oQueFazer”,
    “ordem”: “1”,
    “recursos”: [“index”, “show”],
    “descricao”: “Reservar”,
    “controller”: “ReservasController”
    }, {
    “rota”: “reserva”,
    “sref”: “oQueFazer”,
    “ordem”: “2”,
    “recursos”: [“index”, “show”],
    “descricao”: “Reservas”,
    “controller”: “ReservasController”
    }, {
    “rota”: “usuario”,
    “sref”: “oQueFazer”,
    “ordem”: “3”,
    “recursos”: [“index”, “show”],
    “descricao”: “Usuários”,
    “controller”: “UsuariosController”
    }, {
    “rota”: “feriado”,
    “sref”: “oQueFazer”,
    “ordem”: “4”,
    “recursos”: [“index”, “show”],
    “descricao”: “Feriados”,
    “controller”: “FeriadosController”
    }, {
    “rota”: “sala”,
    “sref”: “home.sala”,
    “ordem”: “5”,
    “recursos”: [“index”, “show”],
    “descricao”: “Salas”,
    “controller”: “SalasController”
    }]
    }