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

Why JSONB and Not Just JSON?

PostgreSQL offers two JSON column types. json stores raw text and re-parses it on every read. jsonb stores a decomposed binary representation, supports indexing, and enables operator-based querying. For any column you will filter or index, always choose jsonb.

-- migration
$table->jsonb('meta')->nullable();

GIN Indexes: The Right Tool for JSONB

A plain B-tree index on a jsonb column is useless for containment queries. You need a GIN (Generalized Inverted Index) index.

// database/migrations/xxxx_add_gin_index_to_products.php
public function up(): void
{
    DB::statement(
        'CREATE INDEX products_meta_gin ON products USING GIN (meta)'
    );
}

For queries that target a single known key path, a GIN index with jsonb_path_ops is smaller and faster:

DB::statement(
    'CREATE INDEX products_meta_path_gin ON products USING GIN (meta jsonb_path_ops)'
);

Use jsonb_path_ops when you only need the @> containment operator. Use the default opclass when you also need ?, ?|, or ?& key-existence operators.

Querying JSONB from Eloquent

Laravel's query builder exposes whereJsonContains, whereJsonLength, and raw expressions for everything else.

// Containment — uses the GIN index
Product::whereJsonContains('meta->tags', 'featured')->get();

// Key-path equality — add a generated column + B-tree for this pattern
Product::whereJsonPath('meta', '$.status', '=', 'active')->get();

// Raw operator when you need full control
Product::whereRaw("meta @> ?::jsonb", [json_encode(['tier' => 'pro'])])->get();

Tip: whereJsonContains emits the @> operator under the hood for PostgreSQL, so your GIN index will be hit. Verify with EXPLAIN ANALYZE.

Generated Columns for Selective B-tree Indexes

When you repeatedly filter on one stable key, a generated (stored) column plus a normal B-tree index beats a GIN index on cardinality-heavy data.

DB::statement(
    "ALTER TABLE products
     ADD COLUMN meta_status TEXT GENERATED ALWAYS AS (meta->>'status') STORED"
);

DB::statement(
    'CREATE INDEX products_meta_status_btree ON products (meta_status)'
);

Now WHERE meta_status = 'active' uses a tight B-tree scan instead of a GIN bitmap scan.

Eloquent Casts: Keeping PHP Types Honest

Storing raw arrays is fine for prototypes, but production code deserves typed value objects.

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

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

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

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

Your ProductMeta value object can enforce invariants, provide typed accessors, and keep business logic out of the model.

Avoiding the Silent Performance Traps

• Never use -> or ->> inside a WHERE without a supporting index or generated column — it triggers a sequential scan.
• whereJsonLength does not use a GIN index; add a generated column if you filter by array length frequently.
• Avoid storing deeply nested, frequently-updated structures in JSONB. Write amplification on updates is real.
• Run EXPLAIN (ANALYZE, BUFFERS) — not just EXPLAIN — to confirm index usage and shared-buffer hits.

Takeaways

• Always use jsonb, never json, for any column you will index or query.
• GIN indexes with jsonb_path_ops are the default choice; fall back to the full opclass only when you need key-existence operators.
• Generated stored columns + B-tree indexes outperform GIN for high-cardinality single-key filters.
• Wrap JSONB columns in typed Eloquent casts to enforce invariants at the PHP layer.
• Validate every JSONB query with EXPLAIN (ANALYZE, BUFFERS) before shipping to production.