PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Pain

Why JSONB Belongs in Your Laravel Stack

PostgreSQL's jsonb type is not a document-store escape hatch — it is a first-class column type with binary storage, deduplication, and indexable paths. Used correctly it eliminates entire pivot tables and EAV nightmares. Used naively it becomes an unindexed black hole that kills query plans.

This article covers the three layers you need to get right: indexing strategy, query builder patterns, and Eloquent casts.

Indexing JSONB Correctly

GIN for Containment Queries

The default GIN index covers the @> (contains) and ? (key exists) operators — the two you will use most.

CREATE INDEX idx_users_meta_gin ON users USING GIN (meta);

In a migration:

$table->jsonb('meta')->nullable();
DB::statement('CREATE INDEX idx_users_meta_gin ON users USING GIN (meta)');

Expression Index for a Specific Path

When you always filter on meta->>'plan', a targeted B-tree expression index is cheaper than a full GIN index:

CREATE INDEX idx_users_meta_plan
  ON users ((meta->>'plan'));

This index is used by WHERE meta->>'plan' = 'pro' and nothing else — tight and fast.

Querying JSONB in Eloquent

Laravel's query builder has no native JSONB operator support, but whereRaw and -> / ->> operators are readable enough:

// Containment: users whose meta contains {"plan": "pro"}
User::whereRaw("meta @> ?::jsonb", [json_encode(['plan' => 'pro'])])->get();

// Text extraction: uses expression index above
User::whereRaw("meta->>'plan' = ?", ['pro'])->get();

// Key existence
User::whereRaw("meta \? ?", ['onboarded'])->get();

A Reusable Scope

Wrap the noise in a query scope so call sites stay clean:

// app/Models/Concerns/HasJsonbMeta.php
trait HasJsonbMeta
{
    public function scopeWhereMetaContains(
        Builder $query,
        array $subset,
        string $column = 'meta'
    ): Builder {
        return $query->whereRaw(
            "{$column} @> ?::jsonb",
            [json_encode($subset)]
        );
    }

    public function scopeWhereMetaPath(
        Builder $query,
        string $path,
        mixed $value,
        string $column = 'meta'
    ): Builder {
        return $query->whereRaw(
            "{$column}->>'$path' = ?",
            [(string) $value]
        );
    }
}

Usage:

User::whereMetaContains(['plan' => 'pro', 'trial' => false])->paginate();
User::whereMetaPath('plan', 'pro')->whereMetaPath('locale', 'en')->get();

Custom Eloquent Cast for Typed JSONB

Storing arbitrary arrays is fine for prototypes. In production, cast to a typed DTO so you get IDE completion and validation at the boundary.

// app/Casts/UserMetaCast.php
use Illuminate\Contracts\Database\Eloquent\CastsAttributes;

class UserMetaCast implements CastsAttributes
{
    public function get($model, string $key, $value, array $attributes): UserMeta
    {
        $data = json_decode($value ?? '{}', true);
        return UserMeta::fromArray($data);
    }

    public function set($model, string $key, $value, array $attributes): string
    {
        if ($value instanceof UserMeta) {
            return json_encode($value->toArray());
        }
        return json_encode($value);
    }
}

// app/Data/UserMeta.php
readonly class UserMeta
{
    public function __construct(
        public string $plan = 'free',
        public string $locale = 'en',
        public bool $trial = false,
    ) {}

    public static function fromArray(array $data): self
    {
        return new self(
            plan: $data['plan'] ?? 'free',
            locale: $data['locale'] ?? 'en',
            trial: $data['trial'] ?? false,
        );
    }

    public function toArray(): array
    {
        return ['plan' => $this->plan, 'locale' => $this->locale, 'trial' => $this->trial];
    }
}

Register on the model:

protected $casts = [
    'meta' => UserMetaCast::class,
];

Now $user->meta->plan is typed, and saving is automatic.

Updating Nested Keys Without Overwriting

Avoid loading the full row just to change one key. Use PostgreSQL's jsonb_set:

DB::table('users')
    ->where('id', $userId)
    ->update([
        'meta' => DB::raw("jsonb_set(meta, '{plan}', '\"enterprise\"')"),
    ]);

This is an atomic server-side update — no race condition, no full-row read.

Takeaways

• Use a GIN index for containment/key-existence queries; use an expression B-tree index when filtering a single known path.
• Prefer @> with ::jsonb cast over ->> string comparisons when you need multi-key containment — one operator, one index scan.
• Wrap raw JSONB operators in query scopes or macro helpers to keep Eloquent call sites readable.
• Cast JSONB columns to typed readonly DTOs rather than plain arrays; you get validation, IDE support, and serialization in one place.
• Use jsonb_set for surgical key updates instead of read-modify-write cycles in PHP.