Using PostgreSQL JSONB Columns in Laravel Without Losing Your Mind

When JSONB Makes Sense

JSONB is not a replacement for relational design. It shines when:

Schema varies per record — product attributes, event metadata, feature flags
Third-party data — you're storing API responses you don't fully control
Rapid iteration — the schema needs to evolve before normalization is justified

The anti-pattern is using JSONB to avoid designing a proper schema. If you know every field, use columns.

Migration and Index Setup

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

// GIN index enables containment and key-existence queries
DB::statement('CREATE INDEX idx_products_attributes ON products USING GIN (attributes)');

The GIN index is critical. Without it, every WHERE attributes @> '{"color":"red"}' query is a full table scan.

Querying with Eloquent

Laravel exposes JSONB querying through whereJsonContains and raw expressions:

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

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

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

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

Eloquent Casting for Type Safety

Without a cast, attributes comes back as a JSON string. Add the cast:

class Product extends Model
{
    protected $casts = [
        'attributes' => 'array', // or AsCollection::class for a fluent interface
    ];
}

// Now you can read/write like a PHP array
$product = Product::find(1);
$product->attributes['color'] = 'blue';
$product->save(); // Eloquent serializes it back to JSON automatically

The Gotcha: When the GIN Index Doesn't Help

The GIN index accelerates @> (containment) and ? (key exists). It does not help extraction with comparison:

-- NOT covered by GIN — full table scan
WHERE attributes->>'weight' > '10'

For those queries, add a functional B-tree index:

CREATE INDEX idx_products_weight ON products ((attributes->>'weight'));

And cast properly in the query:

CREATE INDEX idx_products_weight ON products (((attributes->>'weight')::numeric));

Updating Nested Fields

The - and || operators let you update JSONB without fetching the whole record:

-- Remove a key
UPDATE products SET attributes = attributes - 'legacy_field';

-- Merge/update a nested path (requires PostgreSQL 14+)
UPDATE products SET attributes = jsonb_set(attributes, '{dimensions,unit}', '"mm"');

In Laravel, wrap these in DB::statement() or a raw update for bulk operations — Eloquent's save() always replaces the entire column.