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

Why JSONB Deserves More Than a json_encode Column

PostgreSQL's jsonb type stores JSON in a decomposed binary format, enabling index-backed queries that plain text or MySQL's JSON type cannot match. Laravel developers often reach for JSONB to handle dynamic attributes, feature flags, or third-party webhook payloads — then discover that ->where('meta->price', '>', 100) silently does a full table scan.

This article fixes that.

Migration: Declare the Column and Its Index Together

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('sku')->unique();
    $table->jsonb('meta')->default('{}');

    // GIN index for containment (@>) and key-existence (?) operators
    $table->rawIndex(
        "(meta) jsonb_path_ops",
        'products_meta_gin'
    );
});

jsonb_path_ops is a smaller, faster GIN opclass that supports the @> containment operator — the most common JSONB query pattern. Use the default jsonb_ops opclass only when you also need key-existence (?, ?|, ?&) queries on the same index.

Querying JSONB in Eloquent

Laravel's ->where('meta->key', $value) compiles to the ->> text-extraction operator, which cannot use a GIN index. For indexed lookups, use raw expressions with the containment operator:

// ✅ Uses the GIN index — containment check
$products = Product::whereRaw(
    "meta @> ?::jsonb",
    [json_encode(['category' => 'electronics'])]
)->get();

// ✅ Numeric comparison via a B-tree index on an extracted path
// Requires a separate expression index (see below)
$expensive = Product::whereRaw(
    "(meta->>'price')::numeric > ?",
    [500]
)->get();

Expression Index for Typed Comparisons

When you frequently filter on a specific JSONB path with a type cast, a functional B-tree index beats GIN:

// In a migration
DB::statement(
    "CREATE INDEX products_meta_price_btree 
     ON products (((meta->>'price')::numeric))"
);

Now (meta->>'price')::numeric > 500 uses a B-tree range scan instead of a sequential scan.

Eloquent Casts: Typed DTOs from JSONB

Raw arrays are error-prone. A custom cast converts JSONB into a typed value object on read and back to JSON on write:

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

class ProductMetaCast implements CastsAttributes
{
    public function get($model, string $key, $value, array $attributes): ProductMeta
    {
        $data = json_decode($value, true) ?? [];
        return new ProductMeta(
            price: (float) ($data['price'] ?? 0),
            category: $data['category'] ?? '',
            tags: $data['tags'] ?? [],
        );
    }

    public function set($model, string $key, $value, array $attributes): string
    {
        if ($value instanceof ProductMeta) {
            return json_encode([
                'price'    => $value->price,
                'category' => $value->category,
                'tags'     => $value->tags,
            ]);
        }
        return is_string($value) ? $value : json_encode($value);
    }
}

// app/ValueObjects/ProductMeta.php
readonly class ProductMeta
{
    public function __construct(
        public float $price,
        public string $category,
        public array $tags,
    ) {}
}

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

Now $product->meta->price is always a float, and IDE autocompletion works.

Avoiding the Containment Trap with Arrays

A common mistake is querying for array membership:

// ❌ Checks if the entire array equals ['sale'] — almost never what you want
Product::whereRaw("meta @> ?::jsonb", [json_encode(['tags' => ['sale']])])->get();

// ✅ Correct: checks if the tags array CONTAINS 'sale'
Product::whereRaw(
    "meta->'tags' @> ?::jsonb",
    [json_encode(['sale'])]
)->get();

The @> operator checks that the right-hand side is a subset of the left-hand side, so wrapping the scalar in an array is intentional.

Key Takeaways

• Use jsonb_path_ops GIN for containment queries; add functional B-tree indexes for typed path comparisons.
• Eloquent's ->where('col->key', $val) uses ->> and skips GIN indexes — use whereRaw with @> for indexed lookups.
• Wrap JSONB columns in a custom CastsAttributes implementation backed by a readonly value object for type safety.
• Never store deeply nested, frequently queried data in JSONB — normalize it; JSONB shines for sparse, variable-shape attributes.
• Always EXPLAIN (ANALYZE, BUFFERS) your JSONB queries in staging before deploying; GIN index misses are silent.