PostgreSQL JSONB in Laravel: Indexes, Queries & Casts | 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 Chaos PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Chaos =============================================================================== JSONB columns are powerful but easy to misuse. Learn how to index, query, and cast PostgreSQL JSONB data in Laravel without sacrificing type safety or query performance. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 14 Jun 2026 · Updated 14 Jun 2026 · 3 min read ShareCopy linkCopied ![PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Chaos](https://cdn.msaied.com/169/5efebaf19646869da7c6064340c7d09f.png) On this page +1. [Why JSONB Deserves More Than a json Column](#why-jsonb-deserves-more-than-a-codejsoncode-column) 2. [Choosing the Right GIN Index](#choosing-the-right-gin-index) 3. [Querying JSONB in Eloquent](#querying-jsonb-in-eloquent) 4. [Containment — hits the GIN index](#containment-hits-the-gin-index) 5. [Path extraction — hits the functional B-tree index](#path-extraction-hits-the-functional-b-tree-index) 6. [Encapsulate in a scope to avoid raw SQL leaking everywhere](#encapsulate-in-a-scope-to-avoid-raw-sql-leaking-everywhere) 7. [Type-Safe JSONB with a Custom Eloquent Cast](#type-safe-jsonb-with-a-custom-eloquent-cast) 8. [Avoiding the Silent Performance Trap](#avoiding-the-silent-performance-trap) 9. [Key Takeaways](#key-takeaways) Why JSONB Deserves More Than a `json` Column -------------------------------------------- PostgreSQL's `jsonb` type stores JSON in a decomposed binary format, enabling indexing and operator-based querying that the plain `json` type simply cannot match. Laravel's `json` cast gets you to the finish line for simple storage, but the moment you need to filter or sort on nested keys at scale, you need to think about what PostgreSQL is actually doing under the hood. --- Choosing the Right GIN Index ---------------------------- A generic GIN index on a `jsonb` column supports containment (`@>`) and existence (`?`) operators: ```sql CREATE INDEX idx_orders_meta ON orders USING GIN (meta); ``` For deep path queries (`meta->'shipping'->>'country'`), a functional B-tree index is often faster: ```sql CREATE INDEX idx_orders_shipping_country ON orders ((meta->'shipping'->>'country')); ``` In a Laravel migration: ```php Schema::table('orders', function (Blueprint $table) { // GIN for containment queries $table->rawIndex('meta', 'idx_orders_meta_gin', 'gin'); // Functional B-tree for a known path DB::statement( "CREATE INDEX idx_orders_shipping_country " . "ON orders ((meta->'shipping'->>'country'))" ); }); ``` > **Rule of thumb:** GIN when you query arbitrary keys; functional B-tree when you always query the same path. --- Querying JSONB in Eloquent -------------------------- Laravel's query builder exposes `whereJsonContains`, `whereJsonLength`, and raw expressions. Use them deliberately. ### Containment — hits the GIN index ```php // Find orders where meta contains a specific shipping country Order::whereJsonContains('meta->shipping->country', 'DE')->get(); ``` ### Path extraction — hits the functional B-tree index ```php Order::whereRaw("meta->'shipping'->>'country' = ?", ['DE'])->get(); ``` ### Encapsulate in a scope to avoid raw SQL leaking everywhere ```php // app/Models/Order.php public function scopeShippingCountry(Builder $query, string $country): Builder { return $query->whereRaw( "meta->'shipping'->>'country' = ?", [$country] ); } // Usage Order::shippingCountry('DE')->paginate(); ``` --- Type-Safe JSONB with a Custom Eloquent Cast ------------------------------------------- A plain `array` cast gives you an untyped array. A custom cast backed by a DTO gives you autocomplete, validation, and a clear contract. ```php // app/Casts/ShippingMetaCast.php use Illuminate\Contracts\Database\Eloquent\CastsAttributes; class ShippingMetaCast implements CastsAttributes { public function get($model, string $key, $value, array $attributes): ShippingMeta { $data = json_decode($value ?? '{}', true); return new ShippingMeta( country: $data['country'] ?? '', postalCode: $data['postal_code'] ?? '', carrier: $data['carrier'] ?? null, ); } public function set($model, string $key, $value, array $attributes): string { if ($value instanceof ShippingMeta) { return json_encode([ 'country' => $value->country, 'postal_code' => $value->postalCode, 'carrier' => $value->carrier, ]); } return json_encode($value); } } ``` ```php // app/Data/ShippingMeta.php readonly class ShippingMeta { public function __construct( public string $country, public string $postalCode, public ?string $carrier, ) {} } ``` ```php // app/Models/Order.php protected $casts = [ 'meta' => ShippingMetaCast::class, ]; // Now fully typed: $order->meta->country; // string ``` --- Avoiding the Silent Performance Trap ------------------------------------ The most common mistake: storing deeply nested, frequently queried data in JSONB and then filtering with `LIKE` on a cast text value. PostgreSQL cannot use any index for that. Run `EXPLAIN (ANALYZE, BUFFERS)` on any JSONB query before shipping: ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE meta->'shipping'->>'country' = 'DE'; ``` Look for `Bitmap Index Scan` or `Index Scan` on your functional index. If you see `Seq Scan`, your index is missing or the planner is ignoring it — check column statistics with `ANALYZE orders`. --- Key Takeaways ------------- - Use **GIN** for containment/existence queries; use **functional B-tree** for fixed-path equality filters. - Wrap raw JSONB expressions in **Eloquent scopes** to keep models readable and testable. - Replace the generic `array` cast with a **typed custom cast** backed by a readonly DTO. - Always verify index usage with `EXPLAIN (ANALYZE, BUFFERS)` — never assume the planner will do what you expect. - `ANALYZE` your table after bulk inserts so the planner has fresh statistics for JSONB columns. - [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 --------------------------- When should I use JSONB instead of dedicated columns in PostgreSQL?Use JSONB for genuinely variable or schema-less attributes — feature flags, third-party webhook payloads, or per-tenant configuration. If you query the same field in every WHERE clause, a dedicated typed column with a standard B-tree index will almost always outperform JSONB. Does `whereJsonContains` in Laravel use the GIN index automatically?Yes, if a GIN index exists on the column. `whereJsonContains` compiles to the `@>` containment operator, which PostgreSQL's GIN index supports natively. Verify with EXPLAIN that the planner chooses an index scan rather than a sequential scan. Can I use a custom JSONB cast alongside Filament form fields?Yes. Filament reads and writes through Eloquent, so your custom cast is transparent. You will need to map the DTO's properties to individual form fields using `afterStateHydrated` and `dehydrateStateUsing` callbacks, or flatten the DTO to an array before passing it to the form schema. ![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 articleCursor Pagination at Scale with Laravel Lazy Collections and Chunked Iteration](https://www.msaied.com/public/articles/cursor-pagination-at-scale-with-laravel-lazy-collections-and-chunked-iteration) [Next articleContextual Binding and Method Injection in Laravel's Service Container](https://www.msaied.com/public/articles/contextual-binding-and-method-injection-in-laravels-service-container) On this page ------------- 1. [Why JSONB Deserves More Than a json Column](#why-jsonb-deserves-more-than-a-codejsoncode-column) 2. [Choosing the Right GIN Index](#choosing-the-right-gin-index) 3. [Querying JSONB in Eloquent](#querying-jsonb-in-eloquent) 4. [Containment — hits the GIN index](#containment-hits-the-gin-index) 5. [Path extraction — hits the functional B-tree index](#path-extraction-hits-the-functional-b-tree-index) 6. [Encapsulate in a scope to avoid raw SQL leaking everywhere](#encapsulate-in-a-scope-to-avoid-raw-sql-leaking-everywhere) 7. [Type-Safe JSONB with a Custom Eloquent Cast](#type-safe-jsonb-with-a-custom-eloquent-cast) 8. [Avoiding the Silent Performance Trap](#avoiding-the-silent-performance-trap) 9. [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)