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

Why JSONB Over JSON (and Over EAV)

PostgreSQL's jsonb type stores JSON in a decomposed binary format. Reads are faster than json, and — critically — you can index it. For Laravel applications that need flexible per-tenant settings, feature flags, or dynamic product attributes, jsonb is almost always the right call over an EAV table or a plain text column.

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

GIN Indexes: The Key to Fast JSONB Queries

Without an index, every JSONB query is a full table scan. A GIN (Generalized Inverted Index) index covers containment and existence operators.

-- Raw migration statement
DB::statement('CREATE INDEX products_attributes_gin ON products USING GIN (attributes)');

For queries that target a single known key path, a functional B-tree index is cheaper:

DB::statement(
    "CREATE INDEX products_attributes_color ON products ((attributes->>'color'))"
);

Use EXPLAIN (ANALYZE, BUFFERS) to confirm the planner picks your index.

Querying JSONB with Eloquent

Laravel ships with first-class JSONB helpers that map to PostgreSQL operators.

// Containment: attributes @> '{"color": "red"}'
Product::whereJsonContains('attributes->color', 'red')->get();

// Key existence: attributes ? 'warranty'
Product::whereJsonContainsKey('attributes->warranty')->get();

// Numeric comparison via path extraction
Product::whereRaw("(attributes->>'weight')::numeric > ?", [5.0])->get();

// Ordering by a JSONB path
Product::orderByRaw("attributes->>'sort_order' ASC NULLS LAST")->get();

whereJsonContains generates the @> containment operator, which the GIN index can satisfy. The ->>'key' extraction casts to text; add ::numeric or ::int for numeric comparisons.

Custom Eloquent Casts for Typed JSONB

Raw arrays are fine for prototyping, but a typed cast keeps your domain clean.

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

class ProductAttributes implements CastsAttributes
{
    public function get($model, string $key, $value, array $attributes): \App\Data\ProductAttributesData
    {
        return \App\Data\ProductAttributesData::fromArray(
            json_decode($value ?? '{}', true)
        );
    }

    public function set($model, string $key, $value, array $attributes): string
    {
        if ($value instanceof \App\Data\ProductAttributesData) {
            return json_encode($value->toArray());
        }
        return json_encode($value);
    }
}

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

Now $product->attributes returns a strongly typed value object, not a plain array. IDE autocompletion works, and you can add validation logic inside ProductAttributesData.

Partial GIN Indexes for High-Cardinality Tables

If only a subset of rows have meaningful JSONB data, a partial index reduces index size and write overhead:

DB::statement(
    "CREATE INDEX products_attributes_active_gin
     ON products USING GIN (attributes)
     WHERE attributes IS NOT NULL AND status = 'active'"
);

The planner will use this index only when the WHERE clause matches, keeping it lean.

Avoiding Common Pitfalls

• Type coercion: JSONB stores numbers as numeric, but ->>'key' always returns text. Cast explicitly in SQL.
• Deep nesting: Deeply nested paths (attributes->'specs'->'dimensions'->>'width') are harder to index. Flatten where possible.
• Migrations on large tables: Adding a GIN index locks the table. Use CREATE INDEX CONCURRENTLY via DB::statement in a separate migration.
• Eloquent update with JSONB: $model->update(['attributes->color' => 'blue']) uses PostgreSQL's jsonb_set under the hood in Laravel 10+. Verify with query logging.

Takeaways

• Use jsonb, not json; the binary format enables indexing.
• Add a GIN index for containment queries; use functional B-tree indexes for single-key lookups.
• whereJsonContains maps to @> and is index-aware.
• Wrap JSONB columns in a custom CastsAttributes implementation for type safety.
• Use CREATE INDEX CONCURRENTLY on production tables to avoid locks.
• Profile every JSONB query with EXPLAIN (ANALYZE, BUFFERS) before shipping.