Using JSON column types in Laravel

Learn how to store JSON in a MySQL database and how to select and update them easily with Eloquent.

First of all, you should check if your MySQL version is equal to or greater than 5.7.8 version, otherwise will not be compatible.

MySQL supports the native JSON data type since version 5.7.8.

In these examples I will use the information of the furniture used in the Habbo game, which is served in JSON by means of this url.

As you can see, the data is in json format segmented in two categories: room item types and wall item types. We will get that information and we will store it in a table of our database to make some examples with the JSON data.

Creating the migration

First of all we need to make a migration to create the items table and be able to store our data

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateItemsTable extends Migration
{
    public function up()
    {
        Schema::create('items', function (Blueprint $table) {
            $table->id();
            $table->json('furni');
            $table->string('type');
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('items');
    }
}

As you have noticed, we have marked furni column as json.

Did you know?

When creating a migration with the make:migration artisan command you don’t need to specify the migration name with low bars and without spaces, you only need to do it like this:

php artisan make:migration "create items table"

Fetching the data

To fetch the data from the furni data resource I have crafted a tiny command that will export It to the items table.

First of all make sure to create the Item model:

php artisan make:model Item

Then, we proceed with the command that looks like this:

<?php

namespace App\Console\Commands;

use App\Models\Item;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\Http;
use Symfony\Component\Console\Helper\ProgressBar;

class AddItemsCommand extends Command
{
    private const HASHES_URL = 'https://www.habbo.com/gamedata/hashes2';

    protected $signature = 'add:items';
    protected $description = 'Add items from gamedata';

    public function handle()
    {
        $this->storeFurniData();

        $this->newLine();
        return $this->info('Furni info fetched!');
    }

    private function getFurniDataUrl(): string
    {
        $hashUrl = Http::get(self::HASHES_URL);

        if (! $hashUrl->successful()) {
            $this->error('Furni data could not be fetched.');
        }

        $hashes = collect($hashUrl->json()['hashes']);
        $hashes = $hashes->mapWithKeys(fn($hash) => [$hash['name'] => $hash]);

        $furnidata = $hashes->get('furnidata');
        return "{$furnidata['url']}/{$furnidata['hash']}";
    }

    private function storeFurniData(): void
    {
        $data = Http::get($this->getFurniDataUrl());
        $data = collect($data->json());

        $data = $data->mapWithKeys(fn($items, $key) => [$key => $items]);
        $furniCount = $data->map(fn($items) => count($items['furnitype']))->flatten()->sum();

        $this->info("Trying to fetch {$furniCount} furnis.");
        $progressBar = $this->output->createProgressBar($furniCount);
        $progressBar->start();

        $data->each(fn($types, $type) => collect($types)->each(fn($items) => collect($items)->each(fn($item) => $this->addItem($item, $type, $progressBar))));

        $progressBar->finish();
    }

    private function addItem(array $furni, string $type, ProgressBar $bar): void
    {
        $item = new Item();
        $item->furni = json_encode($furni);
        $item->type = $type;
        $item->save();

        $bar->advance();
    }
}

As you can see, we get the furni data URL and then we iterate through the items to store in our database. Remember to encode the JSON information as we do in this example when adding new rows to the table!

By executing this command we will have our items table full of data in a few seconds.

Processing the data

Let’s see the JSON information of the first furniture through a simple select:

\App\Models\Item::first()->furni

{
	"bc": true,
	"id": 13,
	"name": "Beige Bookcase",
	"rare": false,
	"xdim": 1,
	"ydim": 1,
	"adurl": null,
	"buyout": true,
	"offerid": 5,
	"canlayon": false,
	"cansiton": false,
	"category": "shelf",
	"revision": 61856,
	"classname": "shelves_norja",
	"furniline": "iced",
	"canstandon": false,
	"defaultdir": 0,
	"partcolors": {
		"color": [
			"#ffffff",
			"#F7EBBC"
		]
	},
	"rentbuyout": false,
	"description": "For nic naks and books.",
	"environment": null,
	"rentofferid": -1,
	"specialtype": 1,
	"customparams": null,
	"excludeddynamic": false
}

This is what is included in the first row of our items table in the furni column. As you can see, there are a lot of properties that are stored in a single column!

Casting JSON to array

It’s possible to transform JSON into array with no need of using json_decode by casting the column. All we need to do is adding this in our Item model.

protected $casts = [
    'furni' => 'array'
];

Now, every time we fetch data from the furni column, we will get an array response.

Using JSON Where Clauses

Imagin you only want to query the iced furniline items, how could be it done?

Laravel eloquent provides JSON where clauses which will help us to fix this quickly:

\App\Models\Item::where('furni->furniline', 'iced')->get()

As you can see, we only need to query a JSON column using the -> operator.

Query JSON arrays by their length

What if we want to query the furnis that have three colors? This can be done through the whereJsonLength method, thath will check how many items does the array have.

\App\Models\Item::whereJsonLength('furni->partcolors->color', 3)->get()

And that’s it, we have obtained the 534 items in an easy way!

Updating JSON column data

Updating JSON data is also very easy to do using the -> syntax.

We will change the rare property to true in those furnis with the rare furniline:

\App\Models\Item::where('furni->furniline', 'rare')->update(['furni->rare' => true])

And it’s done! By doing the query below we will see that our rare furniline furnis has they rare parameter as true.

\App\Models\Item::where('furni->furniline', 'rare')->get()

Conclusions

In my opinion, store and process JSON data is very easy to handle with Laravel. From selecting a simple JSON value to updating massively some JSON data. I hope these tricks have been helpful to you.