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

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

JSONB is one of PostgreSQL's most powerful features, but it is also one of the easiest to misuse. Teams reach for it when they want a flexible schema, then wonder why their queries are slow and their application code is littered with json_decode calls. This article covers the three layers you need to get right: indexing, querying through Eloquent, and casting at the model layer.

Why JSONB and Not JSON?

PostgreSQL stores json as plain text and re-parses it on every access. jsonb is stored in a decomposed binary format, supports indexing, and allows operators like @> (contains) and ? (key exists) to use those indexes. Always use jsonb unless you need to preserve key order or duplicate keys — which you almost certainly do not.

// migration
$table->jsonb('settings')->nullable();
$table->jsonb('metadata')->default('{}');

GIN Indexes: The Only Index That Matters for JSONB

A plain B-tree index on a jsonb column is useless for containment queries. You need a GIN index.

// migration — raw statement because Blueprint has no jsonb GIN helper
DB::statement('CREATE INDEX users_metadata_gin ON users USING GIN (metadata)');

// For a specific path only (smaller, faster for known keys)
DB::statement(
    "CREATE INDEX users_metadata_role ON users USING GIN ((metadata->'role'))"
);

A jsonb_path_ops GIN index is smaller and faster for @> queries but does not support ? or ?|:

CREATE INDEX users_metadata_path_ops
    ON users USING GIN (metadata jsonb_path_ops);

Use jsonb_path_ops when you only need containment checks. Use the default operator class when you also need key-existence checks.

Querying JSONB Through Eloquent

Laravel ships with whereJsonContains, whereJsonLength, and arrow-notation column references. They cover most cases.

// Containment — uses GIN index
User::whereJsonContains('metadata->roles', 'admin')->get();

// Key existence — also uses GIN index
User::whereRaw("metadata ? 'verified'")-> get();

// Nested path extraction
User::whereRaw("(metadata->>'plan') = ?", ['pro'])->get();

// Ordering by a JSONB value
User::orderByRaw("metadata->>'last_login' DESC NULLS LAST")->get();

Avoid casting inside the WHERE clause (metadata::text LIKE '%admin%'). That defeats every index and forces a sequential scan.

Custom Eloquent Casts for Typed JSONB

Raw arrays in application code are a maintenance hazard. A custom cast turns a JSONB column into a typed value object with zero overhead.

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

final class UserSettingsCast implements CastsAttributes
{
    public function get($model, string $key, $value, array $attributes): Settings
    {
        return Settings::fromArray(json_decode($value ?? '{}', true));
    }

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

// app/Models/User.php
protected function casts(): array
{
    return [
        'settings' => UserSettingsCast::class,
        'metadata' => 'array', // fine for simple bags
    ];
}

The Settings value object can expose typed getters, enforce invariants, and be unit-tested independently of the database.

Partial GIN Indexes for High-Cardinality Columns

If only a subset of rows carry a particular key, a partial index keeps the index small and writes fast:

CREATE INDEX users_metadata_enterprise
    ON users USING GIN (metadata)
    WHERE (metadata->>'plan') = 'enterprise';

Eloquent can hit this with:

User::whereRaw("(metadata->>'plan') = 'enterprise'")
    ->whereJsonContains('metadata->features', 'sso')
    ->get();

PostgreSQL's planner will select the partial index automatically.

Key Takeaways

• Always use jsonb, never json, for any column you intend to query or index.
• Add a GIN index immediately; without it every containment query is a sequential scan.
• Use jsonb_path_ops when you only need @> — it is smaller and faster.
• Prefer whereJsonContains and whereRaw with path extraction over casting inside SQL.
• Wrap JSONB columns in typed Eloquent casts to keep application code clean and testable.
• Partial GIN indexes on high-cardinality JSONB columns reduce index bloat significantly.