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

Why JSONB Deserves More Respect (and More Caution)

PostgreSQL's jsonb type is not a dumping ground for schema-averse data. Used deliberately, it solves real problems: sparse attributes, user-defined metadata, and semi-structured payloads that would otherwise demand a dozen nullable columns. Used carelessly, it produces full-table scans and unmaintainable query logic.

This article covers the three areas where Laravel developers most often go wrong: indexing strategy, query construction, and Eloquent casting.

Indexing JSONB Correctly

The default GIN index covers containment (@>) and existence (?) operators across the entire document. Create one in a migration:

// database/migrations/2024_01_01_000000_add_gin_index_to_products.php
public function up(): void
{
    Schema::table('products', function (Blueprint $table) {
        $table->jsonb('attributes')->nullable();
    });

    DB::statement(
        'CREATE INDEX products_attributes_gin ON products USING GIN (attributes)'
    );
}

If you query a specific key path repeatedly, a functional B-tree index is cheaper and more selective:

CREATE INDEX products_attributes_brand
    ON products ((attributes->>'brand'));

In a migration:

DB::statement(
    "CREATE INDEX products_attributes_brand ON products ((attributes->>'brand'))"
);

Rule of thumb: GIN for containment queries over unknown keys; functional B-tree for known, high-cardinality key paths.

Querying JSONB in Eloquent

Laravel's whereJsonContains maps directly to the @> operator and benefits from a GIN index:

// Find products tagged with 'waterproof'
Product::whereJsonContains('attributes->tags', 'waterproof')->get();

// Containment on a nested object
Product::whereJsonContains('attributes->dimensions', ['unit' => 'cm'])->get();

For scalar key lookups, use whereJsonPath (Laravel 10+) or a raw expression:

// whereJsonPath uses jsonpath syntax
Product::whereJsonPath('attributes', '$.brand ? (@ == "Acme")')->get();

// Raw alternative — hits the functional index defined above
Product::whereRaw("attributes->>'brand' = ?", ['Acme'])->get();

Avoid This Anti-Pattern

// Loads every row into PHP — no index used
Product::all()->filter(
    fn($p) => ($p->attributes['brand'] ?? null) === 'Acme'
);

Always push JSON filtering to the database layer.

Type-Safe Eloquent Casts for JSONB

Storing raw arrays in a model is a maintenance hazard. A custom cast backed by a value object gives you autocomplete, validation, and a single place to evolve the schema.

// app/Casts/ProductAttributesCast.php
namespace App\Casts;

use App\ValueObjects\ProductAttributes;
use Illuminate\Contracts\Database\Eloquent\CastsAttributes;
use Illuminate\Database\Eloquent\Model;

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

    public function set(Model $model, string $key, mixed $value, array $attributes): string
    {
        $data = $value instanceof ProductAttributes ? $value->toArray() : $value;
        return json_encode($data, JSON_THROW_ON_ERROR);
    }
}

// app/ValueObjects/ProductAttributes.php
namespace App\ValueObjects;

readonly class ProductAttributes
{
    public function __construct(
        public string $brand,
        public array $tags = [],
        public ?array $dimensions = null,
    ) {}

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

    public function toArray(): array
    {
        return array_filter([
            'brand' => $this->brand,
            'tags' => $this->tags,
            'dimensions' => $this->dimensions,
        ], fn($v) => $v !== null);
    }
}

Register the cast on the model:

protected function casts(): array
{
    return [
        'attributes' => ProductAttributesCast::class,
    ];
}

Now $product->attributes->brand is a typed string, not a fragile array key.

Key Takeaways

• Use a GIN index for containment queries; use a functional B-tree index for repeated single-key lookups.
• whereJsonContains and whereJsonPath push filtering to PostgreSQL — never filter JSONB in PHP collections.
• Wrap JSONB columns in a custom cast + value object to enforce structure and gain IDE support.
• JSON_THROW_ON_ERROR in your cast's set method surfaces encoding bugs immediately rather than silently storing null.
• Treat JSONB as a deliberate schema decision, not an escape hatch from migrations.