PostgreSQL JSONB in Laravel: Indexing & Casting | Mohamed Said [Skip to content](#main) [ ![](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed SaidLaravel Backend Engineer ](https://www.msaied.com/public) - [Home](https://www.msaied.com/public) - [Projects](https://www.msaied.com/public/projects) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [About](https://www.msaied.com/public#about) [ Contact](https://www.msaied.com/public#contact) Menu Menu ---- Close - [HomeStart here](https://www.msaied.com/public) - [ProjectsCase studies](https://www.msaied.com/public/projects) - [ArticlesEngineering notes](https://www.msaied.com/public/articles) - [CertificatesCredentials](https://www.msaied.com/public/certificates) - [AboutHow I work](https://www.msaied.com/public#about) - [ContactGet in touch](https://www.msaied.com/public#contact) [Start a conversation](https://www.msaied.com/public#contact) [WhatsApp](https://wa.me/201094619204) [Email](mailto:hello@msaied.com) 1. [Home](https://www.msaied.com/public) 2. / 3. [Articles](https://www.msaied.com/public/articles) 4. / 5. PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Pain PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Pain ============================================================================== JSONB columns unlock flexible schemas inside PostgreSQL, but misused they become slow blobs. Learn how to index, query, and cast JSONB correctly in Laravel Eloquent without sacrificing type safety. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 8 Sep 2026 · Updated 8 Sep 2026 · 3 min read ShareCopy linkCopied ![PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Pain](https://cdn.msaied.com/643/656efe6f0c30b559bcdb27456edcc366.png) On this page +1. [Why JSONB Deserves More Than a json\_encode Column](#why-jsonb-deserves-more-than-a-codejson-encodecode-column) 2. [Migration: Declare the Column and Its Index Together](#migration-declare-the-column-and-its-index-together) 3. [Querying JSONB in Eloquent](#querying-jsonb-in-eloquent) 4. [Expression Index for Typed Comparisons](#expression-index-for-typed-comparisons) 5. [Eloquent Casts: Typed DTOs from JSONB](#eloquent-casts-typed-dtos-from-jsonb) 6. [Avoiding the Containment Trap with Arrays](#avoiding-the-containment-trap-with-arrays) 7. [Key Takeaways](#key-takeaways) 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 ---------------------------------------------------- ```php 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: ```php // ✅ 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: ```php // 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: ```php // 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); } } ``` ```php // app/ValueObjects/ProductMeta.php readonly class ProductMeta { public function __construct( public float $price, public string $category, public array $tags, ) {} } ``` ```php // 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: ```php // ❌ 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. - [laravel](https://www.msaied.com/public/articles?search=laravel) - [postgresql](https://www.msaied.com/public/articles?search=postgresql) - [eloquent](https://www.msaied.com/public/articles?search=eloquent) - [performance](https://www.msaied.com/public/articles?search=performance) Frequently asked questions --------------------------- Does Laravel's built-in `AsArrayObject` or `AsCollection` cast use GIN indexes?No. Those casts handle serialization only; they do not change how Eloquent builds SQL. Queries through those casts still use the `->>` operator and will not hit a GIN index. You must write `whereRaw` with `@>` explicitly to leverage GIN. When should I use `jsonb\_ops` instead of `jsonb\_path\_ops` for my GIN index?`jsonb\_path\_ops` only supports the `@>` containment operator but produces a smaller, faster index. Choose `jsonb\_ops` (the default) when you also need key-existence operators (`?`, `?|`, `?&`) on the same column. If you only ever do containment queries, `jsonb\_path\_ops` is the better choice. Can I use Laravel Scout or full-text search on JSONB columns?Scout abstracts away the search backend, so it depends on your driver. With the database driver, Scout uses `LIKE` and won't leverage JSONB operators. For full-text search over JSONB content in PostgreSQL, generate a `tsvector` from the JSONB paths using `to\_tsvector` and index it with a GIN index separately from your JSONB column. ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp)About the author ---------------- [Mohamed Said](https://www.msaied.com/public#person)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. [About](https://www.msaied.com/public#about) [GitHub ↗](https://github.com/EG-Mohamed) [LinkedIn ↗](https://www.linkedin.com/in/msaiedm/) [WhatsApp ↗](https://wa.me/201094619204) [Email Address ↗](mailto:hello@msaied.com) [My CV ↗](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) [Previous articleFilament v5.8.0 Released: Deferred Schema Loading, Session Grouping & More](https://www.msaied.com/public/articles/filament-v580-released-deferred-schema-loading-session-grouping-more) [Next articleChunked Iteration, Lazy Collections, and Cursor Pagination at Scale in Laravel](https://www.msaied.com/public/articles/chunked-iteration-lazy-collections-and-cursor-pagination-at-scale-in-laravel) On this page ------------- 1. [Why JSONB Deserves More Than a json\_encode Column](#why-jsonb-deserves-more-than-a-codejson-encodecode-column) 2. [Migration: Declare the Column and Its Index Together](#migration-declare-the-column-and-its-index-together) 3. [Querying JSONB in Eloquent](#querying-jsonb-in-eloquent) 4. [Expression Index for Typed Comparisons](#expression-index-for-typed-comparisons) 5. [Eloquent Casts: Typed DTOs from JSONB](#eloquent-casts-typed-dtos-from-jsonb) 6. [Avoiding the Containment Trap with Arrays](#avoiding-the-containment-trap-with-arrays) 7. [Key Takeaways](#key-takeaways) ### Have a technical challenge? Tell me what you’re building. I reply within two working days. [Start a conversation](https://www.msaied.com/public#contact) Related articles ----------------- [ ![](https://cdn.msaied.com/743/8998fac3a41451ab3fe1588194e17a43.png) Filament · 3 min read### Securing Filament Plugins with Plumb: Automated Security Scoring for PHP Packages 5 Oct 2026 ](https://www.msaied.com/public/articles/securing-filament-plugins-with-plumb-automated-security-scoring-for-php-packages) [ ![](https://cdn.msaied.com/742/2d02018669cdeedccb5de2efb898f0ee.png) Filament · 3 min read### Filament v3.3.56 Released: File Hash Names and Livewire Upload Fix 5 Oct 2026 ](https://www.msaied.com/public/articles/filament-v3356-released-file-hash-names-and-livewire-upload-fix) [ ![](https://cdn.msaied.com/741/5b55c123ad08e4d34e1f4b99ad6a428b.png) · 3 min read### Filament v4 Schema-Based Forms, Infolists, and the Unified Schema API 5 Oct 2026 ](https://www.msaied.com/public/articles/filament-v4-schema-based-forms-infolists-and-the-unified-schema-api-5) Have a technical challenge? ---------------------------- Tell me what you’re building. I reply within two working days. [Discuss your project ↗](https://www.msaied.com/public#contact) © 2026 Mohamed Said · Built with Laravel, meant to last.Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. - [Home](https://www.msaied.com/public) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [GitHub](https://github.com/EG-Mohamed) - [LinkedIn](https://www.linkedin.com/in/msaiedm/) - [WhatsApp](https://wa.me/201094619204) - [Email Address](mailto:hello@msaied.com) - [My CV](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) - [Sitemap](https://www.msaied.com/public/sitemap.xml)