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 practical features for product engineers. It lets you store semi-structured data alongside relational columns without spinning up a separate document store. But used carelessly, JSONB columns become black holes: unindexed, untyped, and impossible to query efficiently.

This article covers the three things you actually need to get right: indexing, querying via Eloquent, and casting to typed PHP objects.

When JSONB Makes Sense

JSONB is not a replacement for normalized tables. Use it when:

• The shape of the data varies per row (e.g., feature flags, metadata bags, third-party webhook payloads).
• You need to query into the structure, not just store and retrieve it.
• The alternative is an EAV table, which is almost always worse.

Avoid JSONB for data you join on, aggregate with GROUP BY, or reference from foreign keys.

Migration: Column + GIN Index

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

// Separate migration for the index
DB::statement(
    "CREATE INDEX products_attributes_gin ON products USING GIN (attributes)"
);

The GIN (Generalized Inverted Index) index supports the @> containment operator, which powers whereJsonContains. Without it, every JSONB query does a full sequential scan.

If you only ever query a single key path, a partial B-tree index on an expression is cheaper:

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

Querying JSONB with Eloquent

Laravel's query builder has first-class JSONB support through a handful of methods:

// Containment: uses the GIN index via @>
Product::whereJsonContains('attributes->tags', 'sale')->get();

// Key existence (also GIN-indexed with jsonb_ops)
Product::whereRaw("attributes \?| array['color','size']");

// Scalar comparison on a path
Product::where('attributes->stock', '>', 0)->get();

// Nested path
Product::whereJsonContains('attributes->shipping->methods', 'express')->get();

whereJsonContains compiles to @> under the hood when targeting PostgreSQL, so your GIN index is used automatically. The scalar comparison (attributes->stock) casts to text by default — use ->>'stock' for text or (attributes->>'stock')::int for numeric comparisons via whereRaw.

Typed Casts: Stop Reading Raw Arrays

Returning a raw array from a JSONB column is a footgun. Define a typed cast instead:

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

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

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

// app/Data/Attributes.php
readonly class Attributes
{
    public function __construct(
        public readonly array $tags = [],
        public readonly ?string $color = null,
        public readonly int $stock = 0,
    ) {}

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

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

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

Now $product->attributes->color is typed, IDE-friendly, and never returns null unexpectedly.

Updating Partial Paths

Avoid re-serializing the entire column when you only change one key. Use jsonb_set:

DB::table('products')
    ->where('id', $product->id)
    ->update([
        'attributes' => DB::raw(
            "jsonb_set(attributes, '{stock}', '" . (int) $newStock . "')"
        ),
    ]);

This is a single atomic write and avoids a read-modify-write race condition.

Takeaways

• Always add a GIN index on JSONB columns you query with whereJsonContains or @>.
• Use expression indexes (B-tree on a path) when querying a single scalar key repeatedly.
• Wrap JSONB columns in a typed CastsAttributes class — raw arrays are untyped debt.
• Use jsonb_set for partial updates to avoid overwriting concurrent changes.
• JSONB is a tool for flexible metadata, not a substitute for relational modeling.