PostgreSQL JSONB in Laravel: Index, Query, Cast | 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, but most Laravel apps leave performance on the table. Learn how to index, query, and cast JSONB properly so you get the flexibility without the full-table-scan tax. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 25 Jun 2026 · Updated 25 Jun 2026 · 4 min read ShareCopy linkCopied ![PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Pain](https://cdn.msaied.com/293/f392a5ea52536901eac9677ffa2d070d.png) 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 ----------------------------------------------------------------------------- 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. - [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) - [jsonb](https://www.msaied.com/public/articles?search=jsonb) Frequently asked questions --------------------------- 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. 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. 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. ![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, Chunked Iteration, and Lazy Collections at Scale in Laravel](https://www.msaied.com/public/articles/cursor-pagination-chunked-iteration-and-lazy-collections-at-scale-in-laravel) [Next articleLaravel Service Container: Contextual Binding, Tagging, and Method Injection](https://www.msaied.com/public/articles/laravel-service-container-contextual-binding-tagging-and-method-injection-1) 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) ### 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/740/cce86edc21eddcbdd2f2454fadaf9c70.png) · 3 min read### The Pipeline Pattern in Laravel: Custom Pipelines Beyond Middleware 5 Oct 2026 ](https://www.msaied.com/public/articles/the-pipeline-pattern-in-laravel-custom-pipelines-beyond-middleware-1) [ ![](https://cdn.msaied.com/739/2d6897fdcdcf090613f96f72a64b8a78.png) · 4 min read### MySQL Full-Text Search in Laravel: Indexes, Relevance Scoring, and Boolean Mode 4 Oct 2026 ](https://www.msaied.com/public/articles/mysql-full-text-search-in-laravel-indexes-relevance-scoring-and-boolean-mode) [ ![](https://cdn.msaied.com/738/073696a3fefe18bec825beec5ac658f5.png) · 4 min read### Laravel Queue Rate-Limited Middleware: Throttling Jobs Without Losing Work 4 Oct 2026 ](https://www.msaied.com/public/articles/laravel-queue-rate-limited-middleware-throttling-jobs-without-losing-work) 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)