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

Why JSONB Deserves More Than a json Column

PostgreSQL's jsonb type stores JSON in a decomposed binary format, enabling indexing and operator-based querying that the plain json type simply cannot match. Laravel's json cast gets you to the finish line for simple storage, but the moment you need to filter or sort on nested keys at scale, you need to think about what PostgreSQL is actually doing under the hood.

Choosing the Right GIN Index

A generic GIN index on a jsonb column supports containment (@>) and existence (?) operators:

CREATE INDEX idx_orders_meta ON orders USING GIN (meta);

For deep path queries (meta->'shipping'->>'country'), a functional B-tree index is often faster:

CREATE INDEX idx_orders_shipping_country
  ON orders ((meta->'shipping'->>'country'));

In a Laravel migration:

Schema::table('orders', function (Blueprint $table) {
    // GIN for containment queries
    $table->rawIndex('meta', 'idx_orders_meta_gin', 'gin');

    // Functional B-tree for a known path
    DB::statement(
        "CREATE INDEX idx_orders_shipping_country "
        . "ON orders ((meta->'shipping'->>'country'))"
    );
});

Rule of thumb: GIN when you query arbitrary keys; functional B-tree when you always query the same path.

Querying JSONB in Eloquent

Laravel's query builder exposes whereJsonContains, whereJsonLength, and raw expressions. Use them deliberately.

Containment — hits the GIN index

// Find orders where meta contains a specific shipping country
Order::whereJsonContains('meta->shipping->country', 'DE')->get();

Path extraction — hits the functional B-tree index

Order::whereRaw("meta->'shipping'->>'country' = ?", ['DE'])->get();

Encapsulate in a scope to avoid raw SQL leaking everywhere

// app/Models/Order.php
public function scopeShippingCountry(Builder $query, string $country): Builder
{
    return $query->whereRaw(
        "meta->'shipping'->>'country' = ?",
        [$country]
    );
}

// Usage
Order::shippingCountry('DE')->paginate();

Type-Safe JSONB with a Custom Eloquent Cast

A plain array cast gives you an untyped array. A custom cast backed by a DTO gives you autocomplete, validation, and a clear contract.

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

class ShippingMetaCast implements CastsAttributes
{
    public function get($model, string $key, $value, array $attributes): ShippingMeta
    {
        $data = json_decode($value ?? '{}', true);
        return new ShippingMeta(
            country: $data['country'] ?? '',
            postalCode: $data['postal_code'] ?? '',
            carrier: $data['carrier'] ?? null,
        );
    }

    public function set($model, string $key, $value, array $attributes): string
    {
        if ($value instanceof ShippingMeta) {
            return json_encode([
                'country'     => $value->country,
                'postal_code' => $value->postalCode,
                'carrier'     => $value->carrier,
            ]);
        }
        return json_encode($value);
    }
}

// app/Data/ShippingMeta.php
readonly class ShippingMeta
{
    public function __construct(
        public string $country,
        public string $postalCode,
        public ?string $carrier,
    ) {}
}

// app/Models/Order.php
protected $casts = [
    'meta' => ShippingMetaCast::class,
];

// Now fully typed:
$order->meta->country; // string

Avoiding the Silent Performance Trap

The most common mistake: storing deeply nested, frequently queried data in JSONB and then filtering with LIKE on a cast text value. PostgreSQL cannot use any index for that.

Run EXPLAIN (ANALYZE, BUFFERS) on any JSONB query before shipping:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE meta->'shipping'->>'country' = 'DE';

Look for Bitmap Index Scan or Index Scan on your functional index. If you see Seq Scan, your index is missing or the planner is ignoring it — check column statistics with ANALYZE orders.

Key Takeaways

• Use GIN for containment/existence queries; use functional B-tree for fixed-path equality filters.
• Wrap raw JSONB expressions in Eloquent scopes to keep models readable and testable.
• Replace the generic array cast with a typed custom cast backed by a readonly DTO.
• Always verify index usage with EXPLAIN (ANALYZE, BUFFERS) — never assume the planner will do what you expect.
• ANALYZE your table after bulk inserts so the planner has fresh statistics for JSONB columns.