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

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

JSONB is one of PostgreSQL's most powerful features, but it is also one of the most misused. Developers reach for it to avoid schema migrations, then discover their queries are doing sequential scans across millions of rows. This article covers the indexing strategies, Eloquent query methods, and custom casts that keep JSONB practical in production Laravel applications.

When JSONB Makes Sense

JSONB is a good fit when:

• The shape of data varies per row (e.g., product attributes, feature flags per tenant, webhook payloads).
• You need to query into the structure, not just store and retrieve it.
• You want to avoid a separate EAV table with its own join overhead.

It is a poor fit when every row shares the same keys and you query those keys frequently — that is a relational schema waiting to happen.

Migration: Column and Index

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->jsonb('attributes')->default('{}');
    $table->timestamps();
});

// Add a GIN index so containment queries use an index scan
DB::statement(
    'CREATE INDEX products_attributes_gin ON products USING GIN (attributes)'
);

For queries that filter on a specific key path rather than containment, a functional B-tree index is cheaper:

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

Run EXPLAIN (ANALYZE, BUFFERS) after inserting representative data to confirm the planner picks your index.

Querying with Eloquent

Laravel's whereJsonContains maps directly to the @> containment operator, which the GIN index covers:

// Products where attributes contain {"color": "red"}
Product::whereJsonContains('attributes->color', 'red')->get();

// Multiple values — generates @> for each
Product::whereJsonContains('attributes->tags', ['sale', 'new'])->get();

For range or comparison queries on a JSON key, drop to a raw expression so PostgreSQL can use the functional index:

Product::whereRaw("(attributes->>'price')::numeric > ?", [100])->get();

Avoid ->whereJsonLength() on large datasets unless you have a matching expression index — it forces a sequential scan.

Custom Eloquent Cast for Typed JSONB

Storing raw arrays is fine for prototypes, but a typed value object prevents silent key-name bugs and gives IDE autocompletion.

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

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

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

// app/ValueObjects/ProductAttributes.php
readonly class ProductAttributes
{
    public function __construct(
        public readonly string $color = '',
        public readonly float  $price = 0.0,
        public readonly array  $tags  = [],
    ) {}

    public static function fromArray(array $data): self
    {
        return new self(
            color: $data['color'] ?? '',
            price: (float) ($data['price'] ?? 0),
            tags:  $data['tags']  ?? [],
        );
    }

    public function toArray(): array
    {
        return ['color' => $this->color, 'price' => $this->price, 'tags' => $this->tags];
    }
}

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

// Usage
$product->attributes->color; // typed, no magic strings

Updating Nested Keys Without Overwriting the Column

PostgreSQL's jsonb_set lets you patch a single key atomically:

DB::table('products')
    ->where('id', $product->id)
    ->update([
        'attributes' => DB::raw(
            "jsonb_set(attributes, '{price}', '149.99'::jsonb)"
        ),
    ]);

This avoids a read-modify-write cycle and prevents race conditions under concurrent updates.

Key Takeaways

• Use a GIN index for containment (@>) queries; use a functional B-tree index for single-key comparisons.
• whereJsonContains is index-friendly; whereRaw with a cast is needed for numeric comparisons.
• Wrap JSONB columns in a typed value object + custom cast to eliminate magic strings and enable static analysis.
• jsonb_set for partial updates avoids read-modify-write races.
• Always verify index usage with EXPLAIN (ANALYZE, BUFFERS) on production-sized data before deploying.