PostgreSQL JSONB in Laravel: Index, Query, Cast | 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. [ PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Pain ](#postgresql-jsonb-in-laravel-indexing-querying-and-casting-without-the-pain) 2. [ Why JSONB and Not JSON? ](#why-jsonb-and-not-json) 3. [ GIN Indexes: The Only Index That Matters for JSONB ](#gin-indexes-the-only-index-that-matters-for-jsonb) 4. [ Querying JSONB Through Eloquent ](#querying-jsonb-through-eloquent) 5. [ Custom Eloquent Casts for Typed JSONB ](#custom-eloquent-casts-for-typed-jsonb) 6. [ Partial GIN Indexes for High-Cardinality Columns ](#partial-gin-indexes-for-high-cardinality-columns) 7. [ Key Takeaways ](#key-takeaways) ![PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Pain](https://cdn.msaied.com/293/f392a5ea52536901eac9677ffa2d070d.png) #laravel #postgresql #eloquent #jsonb PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Pain =============================================================================== 25 Jun 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Pain ](#postgresql-jsonb-in-laravel-indexing-querying-and-casting-without-the-pain) 2. [ 02 Why JSONB and Not JSON? ](#why-jsonb-and-not-json) 3. [ 03 GIN Indexes: The Only Index That Matters for JSONB ](#gin-indexes-the-only-index-that-matters-for-jsonb) 4. [ 04 Querying JSONB Through Eloquent ](#querying-jsonb-through-eloquent) 5. [ 05 Custom Eloquent Casts for Typed JSONB ](#custom-eloquent-casts-for-typed-jsonb) 6. [ 06 Partial GIN Indexes for High-Cardinality Columns ](#partial-gin-indexes-for-high-cardinality-columns) 7. [ 07 Key Takeaways ](#key-takeaways) PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Pain ----------------------------------------------------------------------------- JSONB is one of PostgreSQL's most powerful features, but it is also one of the easiest to misuse. Teams reach for it when they want a flexible schema, then wonder why their queries are slow and their application code is littered with `json_decode` calls. This article covers the three layers you need to get right: **indexing**, **querying through Eloquent**, and **casting at the model layer**. --- ### Why JSONB and Not JSON? PostgreSQL stores `json` as plain text and re-parses it on every access. `jsonb` is stored in a decomposed binary format, supports indexing, and allows operators like `@>` (contains) and `?` (key exists) to use those indexes. Always use `jsonb` unless you need to preserve key order or duplicate keys — which you almost certainly do not. ```php // migration $table->jsonb('settings')->nullable(); $table->jsonb('metadata')->default('{}'); ``` --- ### GIN Indexes: The Only Index That Matters for JSONB A plain B-tree index on a `jsonb` column is useless for containment queries. You need a **GIN index**. ```php // migration — raw statement because Blueprint has no jsonb GIN helper DB::statement('CREATE INDEX users_metadata_gin ON users USING GIN (metadata)'); // For a specific path only (smaller, faster for known keys) DB::statement( "CREATE INDEX users_metadata_role ON users USING GIN ((metadata->'role'))" ); ``` A `jsonb_path_ops` GIN index is smaller and faster for `@>` queries but does not support `?` or `?|`: ```sql CREATE INDEX users_metadata_path_ops ON users USING GIN (metadata jsonb_path_ops); ``` Use `jsonb_path_ops` when you only need containment checks. Use the default operator class when you also need key-existence checks. --- ### Querying JSONB Through Eloquent Laravel ships with `whereJsonContains`, `whereJsonLength`, and arrow-notation column references. They cover most cases. ```php // Containment — uses GIN index User::whereJsonContains('metadata->roles', 'admin')->get(); // Key existence — also uses GIN index User::whereRaw("metadata ? 'verified'")-> get(); // Nested path extraction User::whereRaw("(metadata->>'plan') = ?", ['pro'])->get(); // Ordering by a JSONB value User::orderByRaw("metadata->>'last_login' DESC NULLS LAST")->get(); ``` Avoid casting inside the `WHERE` clause (`metadata::text LIKE '%admin%'`). That defeats every index and forces a sequential scan. --- ### Custom Eloquent Casts for Typed JSONB Raw arrays in application code are a maintenance hazard. A custom cast turns a JSONB column into a typed value object with zero overhead. ```php // app/Casts/UserSettings.php use Illuminate\Contracts\Database\Eloquent\CastsAttributes; final class UserSettingsCast implements CastsAttributes { public function get($model, string $key, $value, array $attributes): Settings { return Settings::fromArray(json_decode($value ?? '{}', true)); } public function set($model, string $key, $value, array $attributes): string { return json_encode( $value instanceof Settings ? $value->toArray() : $value ); } } ``` ```php // app/Models/User.php protected function casts(): array { return [ 'settings' => UserSettingsCast::class, 'metadata' => 'array', // fine for simple bags ]; } ``` The `Settings` value object can expose typed getters, enforce invariants, and be unit-tested independently of the database. --- ### Partial GIN Indexes for High-Cardinality Columns If only a subset of rows carry a particular key, a partial index keeps the index small and writes fast: ```sql CREATE INDEX users_metadata_enterprise ON users USING GIN (metadata) WHERE (metadata->>'plan') = 'enterprise'; ``` Eloquent can hit this with: ```php User::whereRaw("(metadata->>'plan') = 'enterprise'") ->whereJsonContains('metadata->features', 'sso') ->get(); ``` PostgreSQL's planner will select the partial index automatically. --- ### Key Takeaways - Always use `jsonb`, never `json`, for any column you intend to query or index. - Add a GIN index immediately; without it every containment query is a sequential scan. - Use `jsonb_path_ops` when you only need `@>` — it is smaller and faster. - Prefer `whereJsonContains` and `whereRaw` with path extraction over casting inside SQL. - Wrap JSONB columns in typed Eloquent casts to keep application code clean and testable. - Partial GIN indexes on high-cardinality JSONB columns reduce index bloat significantly. 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&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) Frequently Asked Questions ---------------------------- 3 questions Q01 Does `whereJsonContains` in Laravel actually use a GIN index? Yes, provided you have a GIN index on the column. Laravel compiles `whereJsonContains('metadata->roles', 'admin')` to a `@>` containment operator, which PostgreSQL can satisfy with a GIN index. Without the index the query still works but performs a sequential scan. Q02 When should I use a JSONB column instead of a proper relational table? Use JSONB for genuinely variable, sparse attributes — user preferences, feature flags, third-party webhook payloads — where the key set differs per row. If you find yourself querying the same key on every request or joining on JSONB values, extract it into a typed column or a related table instead. Q03 Can I use Laravel's built-in `array` cast instead of a custom cast? The built-in `array` cast works for simple key-value bags and is fine for write-heavy columns you rarely query. For columns with business logic, invariants, or complex nested structures, a custom cast backed by a value object gives you type safety and testability that a plain array cannot. Continue reading More Articles --------------- [ View all ](https://www.msaied.com/articles) [ ![Read/Write Splitting, Connection Pooling, and Sticky Reads in Laravel](https://cdn.msaied.com/295/d977bd189583149245c03d6d763d9db5.png) laravel database performance ### Read/Write Splitting, Connection Pooling, and Sticky Reads in Laravel Learn how Laravel's database layer handles read/write splitting, when sticky reads matter, and how to layer Pg... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 26 Jun 2026 4 min read Read ](https://www.msaied.com/articles/readwrite-splitting-connection-pooling-and-sticky-reads-in-laravel-2) [ ![Laravel Service Container: Contextual Binding, Tagging, and Method Injection](https://cdn.msaied.com/294/e5b9d047bd33c3f8b80069ef6a178884.png) laravel service-container dependency-injection ### Laravel Service Container: Contextual Binding, Tagging, and Method Injection Go beyond basic binding. Learn how contextual binding resolves different implementations per consumer, how tag... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 26 Jun 2026 3 min read Read ](https://www.msaied.com/articles/laravel-service-container-contextual-binding-tagging-and-method-injection-1) [ ![Cursor Pagination, Chunked Iteration, and Lazy Collections at Scale in Laravel](https://cdn.msaied.com/292/a09cfdc4dcc65660fb6ada3aae3fa264.png) laravel eloquent performance ### Cursor Pagination, Chunked Iteration, and Lazy Collections at Scale in Laravel Offset pagination breaks under large datasets. Learn how cursor pagination, chunked iteration, and lazy collec... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 25 Jun 2026 4 min read Read ](https://www.msaied.com/articles/cursor-pagination-chunked-iteration-and-lazy-collections-at-scale-in-laravel) [ ![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)