Working with JSON Column Relationships in Laravel

Working with JSON column relationships in Laravel can make your interactions with the database easier when you deal with complex data structures. Fortunately, Laravel’s Eloquent ORM supports JSON columns out of the box, enabling developers to handle relational data without requiring additional tables. This will make your applications more flexible, especially when dealing with dynamic or semi-structured data formats.

How to Use JSON Column Relationships in Laravel

Let’s build a simple example to understand how to use a JSON column in a relationship. Suppose we have two tables: users and projects. Each project contains a JSON column named user_ids that stores multiple user IDs assigned to it.

Creating Model and Migration For Our Setup

php artisan make:migration create_projects_table

Open the created migration file and add the structure:

<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    public function up(): void
    {
        Schema::create('projects', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->json('user_ids')->nullable();
            $table->timestamps();
        });
    }

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

Next, run the migration:

php artisan migrate

Let’s move further with models. Create two models one is for Project and another one for User. Open terminal and enter below command:

php artisan make:model Project
php artisan make:model User

Update the Project model:

<?php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use App\Models\User;

class Project extends Model
{
    protected $fillable = ['name', 'user_ids'];

    protected $casts = [
        'user_ids' => 'array',
    ];

    public function users()
    {
        return User::whereIn('id', $this->user_ids)->get();
    }
}

Insert Example Data

For testing, we need to add some data to the models. We will use tinker here for manually adding some data. However, you can database seeding for that.

User::insert([
    ['name' => 'John Doe', 'email' => 'john@example.com'],
    ['name' => 'Jane Smith', 'email' => 'jane@example.com'],
    ['name' => 'Alex Brown', 'email' => 'alex@example.com'],
]);

Project::create([
    'name' => 'Website Development',
    'user_ids' => [1, 2],
]);

Project::create([
    'name' => 'Mobile App Design',
    'user_ids' => [2, 3],
]);

Fetch Related Users Using JSON Column

With this setup, you can easily get all users assigned to a specific project using Eloquent. Let’ explore an example to utilize this user data.

$project = Project::find(1);
$users = $project->users();

foreach ($users as $user) {
    echo $user->name . "\n";
}

Query JSON Column in Laravel

You can also search for projects that include a specific user ID within the JSON column:

$projects = Project::whereJsonContains('user_ids', 2)->get();

foreach ($projects as $project) {
    echo $project->name . "\n";
}

For searching multiple users through project, you can follow approach like below:

$projects = Project::whereJsonContains('user_ids', [1, 3])->get();

$projects = Project::whereJsonDoesntContain('user_ids', 2)->get();

This flexibility makes working with JSON column relationships in Laravel powerful for handling dynamic or nested data structures while keeping your code clean and efficient.

Conclusion

The relationship of JSON columns in Laravel provides developers with a very practical and powerful way to simplify the database design while keeping the handling of relationships efficient. It’s modern, fitting well in today’s data-driven applications where flexibility and scalability are key. You will be able to minimize database complexity by using Eloquent’s JSON features and maintain clean and readable code.

Codewolfy