PostgreSQL JSONB in Laravel: Indexing, Querying, and Schema-less Columns Done Right

Why JSONB Belongs in Your Laravel Toolkit

PostgreSQL's jsonb type stores JSON as a decomposed binary, making it faster to query than plain json. When your domain has genuinely variable attributes — product metadata, feature flags per tenant, user preferences — a jsonb column beats an EAV table or a pile of nullable columns. The catch: most Laravel codebases use it without indexes, then wonder why queries crawl at 100k rows.

Migration: Declaring the Column

Schema::table('products', function (Blueprint $table) {
    $table->jsonb('attributes')->default('{}');
});

Always default to '{}' rather than null — it simplifies whereJsonContains logic and avoids null-coalescing in every query.

GIN Indexes: The Non-Negotiable Step

A full-column GIN index lets PostgreSQL answer containment (@>) and existence (?) operators in milliseconds:

CREATE INDEX idx_products_attributes_gin
    ON products USING GIN (attributes);

In a migration:

DB::statement(
    'CREATE INDEX idx_products_attributes_gin ON products USING GIN (attributes)'
);

For queries on a specific key, a functional B-tree index is cheaper:

CREATE INDEX idx_products_brand
    ON products ((attributes->>'brand'));

Use GIN for "does this document contain this sub-object?" and functional B-tree for equality on a known key.

Querying Through Eloquent

Laravel's query builder wraps the most common JSONB operators cleanly:

// Containment: find products where attributes includes {"color": "red"}
Product::whereJsonContains('attributes->color', 'red')->get();

// Key existence (raw, no built-in helper)
Product::whereRaw("attributes \? 'warranty'");

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

// Ordering by a JSONB key
Product::orderByRaw("attributes->>'price_usd' DESC NULLS LAST")->get();

whereJsonContains compiles to the @> operator, which the GIN index can satisfy. Raw ? queries also hit the GIN index. Avoid ->>'key' LIKE '%value%' — it forces a sequential scan.

Custom Cast: Typed Value Object from JSONB

Raw arrays leak into your domain. A cast converts the column to a value object on read and back to JSON on write:

final class ProductAttributes
{
    public function __construct(
        public readonly string $brand,
        public readonly string $color,
        public readonly ?float $weightKg = null,
    ) {}

    public static function fromArray(array $data): self
    {
        return new self(
            brand: $data['brand'] ?? '',
            color: $data['color'] ?? '',
            weightKg: isset($data['weight_kg']) ? (float) $data['weight_kg'] : null,
        );
    }

    public function toArray(): array
    {
        return array_filter([
            'brand'     => $this->brand,
            'color'     => $this->color,
            'weight_kg' => $this->weightKg,
        ], fn ($v) => $v !== null);
    }
}

use Illuminate\Contracts\Database\Eloquent\CastsAttributes;

class ProductAttributesCast implements CastsAttributes
{
    public function get($model, $key, $value, $attributes): ProductAttributes
    {
        return ProductAttributes::fromArray(
            is_string($value) ? json_decode($value, true) : ($value ?? [])
        );
    }

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

        return json_encode($array, JSON_THROW_ON_ERROR);
    }
}

Register it on the model:

protected $casts = [
    'attributes' => ProductAttributesCast::class,
];

Now $product->attributes->brand is always a typed string, never null from a missing array key.

Partial GIN Index for Sparse Data

If only 20% of rows have a warranty key, index only those rows:

CREATE INDEX idx_products_warranty_gin
    ON products USING GIN (attributes)
    WHERE attributes ? 'warranty';

Smaller index, faster maintenance, same query speed for the filtered subset.

Takeaways

• Always add a GIN index before querying JSONB at scale; without it every query is a sequential scan.
• Use functional B-tree indexes for equality on a single known key — they are smaller and faster than GIN for that case.
• whereJsonContains compiles to @> and is index-friendly; raw LIKE on a JSONB path is not.
• Wrap JSONB columns in a typed cast so domain code never touches raw arrays.
• Partial GIN indexes cut index size dramatically when the key is sparse.