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

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

JSONB is one of PostgreSQL's most powerful features, but it is also one of the most abused. Teams reach for it to avoid migrations, end up with unindexed blobs, and wonder why their dashboards time out. This article covers the practical mechanics: when to use JSONB, how to index it correctly, how to query it through Eloquent without raw SQL sprawl, and how to wrap it in a typed cast so your domain layer never sees an array.

When JSONB Is the Right Tool

JSONB earns its place when the shape of data genuinely varies per row — feature flags per tenant, metadata from third-party webhooks, or user-defined attributes on a product. It is not a substitute for a proper relational schema when the keys are known and stable.

Rule of thumb: if you find yourself writing WHERE data->>'status' = 'active' on every query, that column belongs in its own typed column with a B-tree index.

GIN Indexes: The Only Index That Matters for Containment

A plain B-tree index on a JSONB column is useless for containment queries (@>). You need a GIN index.

CREATE INDEX idx_products_attributes_gin
    ON products USING GIN (attributes);

In a Laravel migration:

$table->jsonb('attributes')->nullable();
DB::statement(
    'CREATE INDEX idx_products_attributes_gin ON products USING GIN (attributes)'
);

For queries on a specific key path, a functional GIN index is cheaper:

CREATE INDEX idx_products_attributes_tags
    ON products USING GIN ((attributes->'tags'));

This index is smaller and faster when you only ever query attributes->'tags'.

Querying JSONB Through Eloquent

Laravel's query builder exposes whereJsonContains, whereJsonLength, and raw path expressions. Use them instead of raw SQL strings scattered through your codebase.

// Containment: find products tagged 'wireless'
Product::whereJsonContains('attributes->tags', 'wireless')->get();

// Path equality
Product::where('attributes->color', 'red')->get();

// Nested path
Product::where('attributes->dimensions->unit', 'cm')->get();

// Array length guard
Product::whereJsonLength('attributes->tags', '>', 2)->get();

whereJsonContains compiles to the @> containment operator, which will use your GIN index. The -> path operator compiles to ->> (text cast) for equality, which can use a functional B-tree index if you create one:

CREATE INDEX idx_products_color
    ON products ((attributes->>'color'));

Typed Casts: Keep Arrays Out of Your Domain

Returning raw PHP arrays from a JSONB column leaks implementation details everywhere. A custom Eloquent cast converts the column to a value object on read and back to JSON on write.

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

class ProductAttributesCast implements CastsAttributes
{
    public function get($model, string $key, $value, array $attributes): ProductAttributes
    {
        $data = json_decode($value ?? '{}', true);
        return new ProductAttributes(
            tags: $data['tags'] ?? [],
            color: $data['color'] ?? null,
            dimensions: isset($data['dimensions'])
                ? Dimensions::from($data['dimensions'])
                : null,
        );
    }

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

// app/Models/Product.php
protected $casts = [
    'attributes' => ProductAttributesCast::class,
];

Now $product->attributes is always a ProductAttributes object. PHPStan can analyse it, your IDE autocompletes it, and tests can assert on typed properties instead of array keys.

Avoiding the -> Operator in SELECT at Scale

Extract frequently-read JSONB keys into generated columns so PostgreSQL materialises them:

ALTER TABLE products
    ADD COLUMN color TEXT
    GENERATED ALWAYS AS (attributes->>'color') STORED;

CREATE INDEX idx_products_color_gen ON products (color);

Laravel sees color as a normal column. Queries are faster, and you keep the flexibility of JSONB for the rest of the payload.

Key Takeaways

• Use a GIN index for containment (@>) queries; a functional B-tree for single-key equality.
• whereJsonContains in Eloquent maps to @> and will hit your GIN index.
• Wrap JSONB columns in a custom cast to expose typed value objects, not raw arrays.
• For hot query paths on a single key, add a generated stored column and index that instead.
• JSONB is not a schema escape hatch — use it only where the document shape genuinely varies.