Laravel JSON column types

You know that feeling when a solution works, but just doesn’t feel like the best one? Well, that’s how I’ve felt about having columns for varying data in MySQL databases.

Adding column after column that would be only utilized occasionally just never seemed elegant to me. And while alternatives, such as MongoDB, exist - this felt like something MySQL should support, and does with JSON data types.

MySQL has actually supported JSON data types since version 5.7.8, and support found it’s way into Laravel in version 5.3. Unfortunately, there’s not a ton of examples out there for Laravel usage, and most of the time I’ve heard “oh, I didn’t know that existed!” from talented and experienced developers. So let’s fix that and dive in with the basics, then we’ll finish up with some of the tips-and-tricks I’ve picked up from using these columns in production applications.

If you are already familiar with the basics of JSON in Laravel, feel free to jump straight to the Advanced Usage / Tips and Tricks section.

Why JSON?

In simple terms: it allows for great flexibility and future expandability. Say I had a User model with phone numbers, instead of having columns for the various phone number types, I can store these in one column:

{
	"cell": "561-555-5555",
	"office": {
		"number": "561-777-7777",
		"extension": "100"
	},
	"home": "561-999-9999"
}

I can also pull all these numbers, and their keys, without fetching multiple columns. Should I need to add additional types in the future, I won’t need to add another column.

Setting up JSON Columns

  1. Define a JSON column in your migration:

     Schema::table('users', function (Blueprint $table) {
     $table->json('phone_numbers')->nullable();
 });

  2. Cast the column as JSON in your model

     namespace App;

 use Illuminate\Database\Eloquent\Model;

 class User extends Model {
     protected $casts = [
         'phone_numbers' => 'json',
     ];
 }

Working with JSON data

Creating

Laravel has made it extremely convenient to utilize JSON in Models. Updating data is done utilizing the arrow -> accessor, similar to how we utilize them for objects:

User::find(1)->fill([
    'phone_numbers->cell' => '561-555-5555',
    'phone_numbers->home' => '561-999-9999',
]);

Laravel JSON column data in MySQL DB

We can even utilize nested arrays, like so:

User::find(1)->fill([
   'phone_numbers->office' => [
        'number' => '561-777-7777',
        'extension' => '100',
    ],
]);

However, I highly encourage taking a look at When to avoid nested arrays to understand how they compare with full accessor names as key values.

Retrieving

Accessing data in columns is done in the array syntax, using brackets []:

$cell = User::find(1)->phone_numbers['cell'];

And “where” queries can be performed using the arrow -> syntax:

$fakes = User::where('phone_numbers->cell', '000-000-0000')->get();

$cellNumbers = User::whereNotNull('phone_numbers->cell')->get();

$missingCell = User::whereNull('phone_numbers->cell')->get();

Updating

Both nested and arrow accessors work in place of a traditional column name for condition matching, such as on an updateOrCreate query:

User::updateOrCreate([
    'phone_numbers->cell' => '000-000-0000'
], [
    'details->is_fake' => true,
    'details->dont_call' => true,
]);

Advanced Usage / Tips and Tricks

When to avoid saving nested arrays (and use the arrow accessor instead)

From the MySQL official docs: To make lookups more efficient, it also sorts the keys of a JSON object. You should be aware that the result of this ordering is subject to change and not guaranteed to be consistent across releases.

What’s this mean in plain english? That nested arrays will trigger an actual update (with updated_at changing) because the array keys and values are not always stored in the order you input them.

For example: Timestamps changing in Laravel MySQL JSON Columns

Note how the updated_at value changes, even though the same data is input (MySQL is not storing keys in JSON columns alphabetically, so there is no telling in which order an array will be returned). This can be examined using ->getChanges() on the record:

if($user->wasChanged()) {
    dd($user->getChanges());
}

Further, using nested values will overwrite any values not specified:

return $user->phone_numbers;

/* [
    "home" => "561-999-9999"
    "cell" => "561-555-5555",
    "office" => [
        "number" => "561-777-7777",
        "extension" => "100"
    ],
] */

$user->update([
    'phone_numbers' => [
        'cell' => '222-222-2222',
        'international' => '+358 50 555 55 55',
    ],
]);

return $user->phone_numbers;

/* [
    "cell" => "222-222-2222",
    "international" => "+358 50 555 55 55"
] */

In this example, because we utilized an array, we completely overwrote the column values and dropped the office and home keys & values.

Both of these issues can be prevented by utilizing the arrow accessor to specify full paths in key-value pairs:

$user->update([
    'phone_numbers->cell' => '222-222-2222',
    'phone_numbers->international' => '+358 50 555 55 55',
]);

return $user->phone_numbers;

/* [
    "home" => "561-999-9999"
    "cell" => "222-222-2222",
    "office" => [
        "number" => "561-777-7777",
        "extension" => "100"
    ],
    "international" => "+358 50 555 55 55"
] */

Drawbacks

While JSON data types in Laravel are extremely versatile, there are a few major drawbacks out of the box:

  • The inability to utilize Mutators and Accessors
  • Keys not being sorted

However, both of these issues can be addressed and we’ll tackle them in the next article.

Improvements around the bend

  • Laravel 5.8 brings Unquoted MySQL JSON Values, meaning you get ‘en’ instead of ‘“en”’

Stuck and need help? Or want to brainstorm some ideas?

Send me a message on Twitter: @jani_gyllenberg