PostgreSQL JSONB in Laravel: Indexing & Casting | Mohamed Said [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://www.msaied.com) [ Home ](https://www.msaied.com) [ Projects ](https://www.msaied.com/projects) [ Articles ](https://www.msaied.com/articles) [ Certificates ](https://www.msaied.com/certificates) [ Contact ](https://www.msaied.com#contact-section) [ ](https://github.com/EG-Mohamed) [ Home ](https://www.msaied.com) [ Projects ](https://www.msaied.com/projects) [ Articles ](https://www.msaied.com/articles) [ Certificates ](https://www.msaied.com/certificates) [ Contact ](https://www.msaied.com#contact-section) [ home ](https://www.msaied.com) [ articles ](https://www.msaied.com/articles) PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Pain 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) ![PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Pain](https://cdn.msaied.com/643/656efe6f0c30b559bcdb27456edcc366.png) #laravel #postgresql #eloquent #performance PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Pain =============================================================================== 8 Sep 2026 3 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 Why JSONB Deserves More Than a json\_encode Column ](#why-jsonb-deserves-more-than-a-codejson-encodecode-column) 2. [ 02 Migration: Declare the Column and Its Index Together ](#migration-declare-the-column-and-its-index-together) 3. [ 03 Querying JSONB in Eloquent ](#querying-jsonb-in-eloquent) 4. [ 04 Expression Index for Typed Comparisons ](#expression-index-for-typed-comparisons) 5. [ 05 Eloquent Casts: Typed DTOs from JSONB ](#eloquent-casts-typed-dtos-from-jsonb) 6. [ 06 Avoiding the Containment Trap with Arrays ](#avoiding-the-containment-trap-with-arrays) 7. [ 07 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. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fwww.msaied.com%2Farticles%2Fpostgresql-jsonb-in-laravel-indexing-querying-and-casting-without-the-pain-2&text=PostgreSQL+JSONB+in+Laravel%3A+Indexing%2C+Querying%2C+and+Casting+Without+the+Pain) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fwww.msaied.com%2Farticles%2Fpostgresql-jsonb-in-laravel-indexing-querying-and-casting-without-the-pain-2) Frequently Asked Questions ---------------------------- 3 questions Q01 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. Q02 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. Q03 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. Continue reading More Articles --------------- [ View all ](https://www.msaied.com/articles) [ ![Filament v5.8.0 Released: Deferred Schema Loading, Session Grouping & More](https://cdn.msaied.com/641/eccc2db7462422ed6a5dd3dbbf991a28.png) Filament Laravel PHP ### Filament v5.8.0 Released: Deferred Schema Loading, Session Grouping & More Filament v5.8.0 ships deferred schema loading, persistent table grouping, RichEditor height controls, a reusab... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 7 Sep 2026 4 min read Read ](https://www.msaied.com/articles/filament-v580-released-deferred-schema-loading-session-grouping-more) [ ![Filament v4.13.0 Released: Deferred Schema Loading, Session Grouping & More](https://cdn.msaied.com/642/400efa8dc790ea53fa0d52ed69e4f132.png) Filament Laravel PHP ### Filament v4.13.0 Released: Deferred Schema Loading, Session Grouping & More Filament v4.13.0 ships with deferred schema loading, persistent table grouping in the session, RichEditor min/... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 7 Sep 2026 4 min read Read ](https://www.msaied.com/articles/filament-v4130-released-deferred-schema-loading-session-grouping-more) [ ![Contextual Binding and Method Injection in Laravel's Service Container](https://cdn.msaied.com/639/ce580b3b521a5e965bf80bb1e7ba7ced.png) laravel service-container dependency-injection ### Contextual Binding and Method Injection in Laravel's Service Container Go beyond basic singleton registration. Learn how contextual binding, tagged services, and method injection le... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 7 Sep 2026 3 min read Read ](https://www.msaied.com/articles/contextual-binding-and-method-injection-in-laravels-service-container-3) [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://www.msaied.com)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. Explore - [Home](https://www.msaied.com) - [Projects](https://www.msaied.com/projects) - [Articles](https://www.msaied.com/articles) - [Certificates](https://www.msaied.com/certificates) - [Contact](https://www.msaied.com#contact-section) Connect - [ hello@msaied.com ](mailto:hello@msaied.com) - [ +20 109 461 9204 ](tel:+201094619204) © 2026 Mohamed Said. All rights reserved. [ ](https://github.com/EG-Mohamed) [ ](https://www.linkedin.com/in/msaiedm/) [ ](https://wa.me/201094619204) [ ](mailto:hello@msaied.com) [ ](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view)