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 Bloat PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Bloat =============================================================================== JSONB columns give you schema flexibility without sacrificing query performance. Learn how to index, query, and cast JSONB data in Laravel using real Eloquent patterns and raw PostgreSQL power. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 9 Aug 2026 · Updated 9 Aug 2026 · 3 min read ShareCopy linkCopied ![PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Bloat](https://cdn.msaied.com/528/3c1e1dba1e2f4ca6b8686cf670dcd3b8.png) On this page +1. [Why JSONB Over JSON (and Over EAV)](#why-jsonb-over-json-and-over-eav) 2. [GIN Indexes: The Key to Fast JSONB Queries](#gin-indexes-the-key-to-fast-jsonb-queries) 3. [Querying JSONB with Eloquent](#querying-jsonb-with-eloquent) 4. [Custom Eloquent Casts for Typed JSONB](#custom-eloquent-casts-for-typed-jsonb) 5. [Partial GIN Indexes for High-Cardinality Tables](#partial-gin-indexes-for-high-cardinality-tables) 6. [Avoiding Common Pitfalls](#avoiding-common-pitfalls) 7. [Takeaways](#takeaways) Why JSONB Over JSON (and Over EAV) ---------------------------------- PostgreSQL's `jsonb` type stores JSON in a decomposed binary format. Reads are faster than `json`, and — critically — you can index it. For Laravel applications that need flexible per-tenant settings, feature flags, or dynamic product attributes, `jsonb` is almost always the right call over an EAV table or a plain `text` column. ```sql -- Migration Schema::table('products', function (Blueprint $table) { $table->jsonb('attributes')->nullable(); }); ``` --- GIN Indexes: The Key to Fast JSONB Queries ------------------------------------------ Without an index, every JSONB query is a full table scan. A GIN (Generalized Inverted Index) index covers containment and existence operators. ```sql -- Raw migration statement DB::statement('CREATE INDEX products_attributes_gin ON products USING GIN (attributes)'); ``` For queries that target a single known key path, a functional B-tree index is cheaper: ```sql DB::statement( "CREATE INDEX products_attributes_color ON products ((attributes->>'color'))" ); ``` Use `EXPLAIN (ANALYZE, BUFFERS)` to confirm the planner picks your index. --- Querying JSONB with Eloquent ---------------------------- Laravel ships with first-class JSONB helpers that map to PostgreSQL operators. ```php // Containment: attributes @> '{"color": "red"}' Product::whereJsonContains('attributes->color', 'red')->get(); // Key existence: attributes ? 'warranty' Product::whereJsonContainsKey('attributes->warranty')->get(); // Numeric comparison via path extraction Product::whereRaw("(attributes->>'weight')::numeric > ?", [5.0])->get(); // Ordering by a JSONB path Product::orderByRaw("attributes->>'sort_order' ASC NULLS LAST")->get(); ``` `whereJsonContains` generates the `@>` containment operator, which the GIN index can satisfy. The `->>'key'` extraction casts to text; add `::numeric` or `::int` for numeric comparisons. --- Custom Eloquent Casts for Typed JSONB ------------------------------------- Raw arrays are fine for prototyping, but a typed cast keeps your domain clean. ```php // app/Casts/ProductAttributes.php use Illuminate\Contracts\Database\Eloquent\CastsAttributes; class ProductAttributes implements CastsAttributes { public function get($model, string $key, $value, array $attributes): \App\Data\ProductAttributesData { return \App\Data\ProductAttributesData::fromArray( json_decode($value ?? '{}', true) ); } public function set($model, string $key, $value, array $attributes): string { if ($value instanceof \App\Data\ProductAttributesData) { return json_encode($value->toArray()); } return json_encode($value); } } ``` ```php // app/Models/Product.php protected $casts = [ 'attributes' => ProductAttributes::class, ]; ``` Now `$product->attributes` returns a strongly typed value object, not a plain array. IDE autocompletion works, and you can add validation logic inside `ProductAttributesData`. --- Partial GIN Indexes for High-Cardinality Tables ----------------------------------------------- If only a subset of rows have meaningful JSONB data, a partial index reduces index size and write overhead: ```sql DB::statement( "CREATE INDEX products_attributes_active_gin ON products USING GIN (attributes) WHERE attributes IS NOT NULL AND status = 'active'" ); ``` The planner will use this index only when the `WHERE` clause matches, keeping it lean. --- Avoiding Common Pitfalls ------------------------ - **Type coercion**: JSONB stores numbers as numeric, but `->>'key'` always returns text. Cast explicitly in SQL. - **Deep nesting**: Deeply nested paths (`attributes->'specs'->'dimensions'->>'width'`) are harder to index. Flatten where possible. - **Migrations on large tables**: Adding a GIN index locks the table. Use `CREATE INDEX CONCURRENTLY` via `DB::statement` in a separate migration. - **Eloquent `update` with JSONB**: `$model->update(['attributes->color' => 'blue'])` uses PostgreSQL's `jsonb_set` under the hood in Laravel 10+. Verify with query logging. --- Takeaways --------- - Use `jsonb`, not `json`; the binary format enables indexing. - Add a GIN index for containment queries; use functional B-tree indexes for single-key lookups. - `whereJsonContains` maps to `@>` and is index-aware. - Wrap JSONB columns in a custom `CastsAttributes` implementation for type safety. - Use `CREATE INDEX CONCURRENTLY` on production tables to avoid locks. - Profile every JSONB query with `EXPLAIN (ANALYZE, BUFFERS)` before shipping. - [laravel](https://www.msaied.com/public/articles?search=laravel) - [postgresql](https://www.msaied.com/public/articles?search=postgresql) - [jsonb](https://www.msaied.com/public/articles?search=jsonb) - [eloquent](https://www.msaied.com/public/articles?search=eloquent) - [performance](https://www.msaied.com/public/articles?search=performance) Frequently asked questions --------------------------- Does Laravel's whereJsonContains use a GIN index automatically?Yes, when you have a GIN index on the JSONB column, PostgreSQL's query planner will use it for containment queries generated by whereJsonContains. Always verify with EXPLAIN ANALYZE, as the planner may still choose a sequential scan on small tables. Should I use jsonb or a separate relational table for dynamic attributes?Use jsonb when the attribute schema varies per row and you rarely need to join or aggregate on individual attribute keys. Use a relational table when you need foreign keys, strong typing, or frequent cross-row aggregations on specific attributes. How do I update a single JSONB key without overwriting the whole column in Laravel?In Laravel 10+, you can use dot-notation: $model->update(\['attributes->color' => 'blue'\]). Laravel compiles this to a jsonb\_set call, so only the targeted key is modified. Check your query log to confirm the generated SQL. ![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 articlePostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Bloat](https://www.msaied.com/public/articles/postgresql-jsonb-in-laravel-indexing-querying-and-casting-without-the-bloat) [Next articlePostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Bloat](https://www.msaied.com/public/articles/postgresql-jsonb-in-laravel-indexing-querying-and-casting-without-the-bloat-2) On this page ------------- 1. [Why JSONB Over JSON (and Over EAV)](#why-jsonb-over-json-and-over-eav) 2. [GIN Indexes: The Key to Fast JSONB Queries](#gin-indexes-the-key-to-fast-jsonb-queries) 3. [Querying JSONB with Eloquent](#querying-jsonb-with-eloquent) 4. [Custom Eloquent Casts for Typed JSONB](#custom-eloquent-casts-for-typed-jsonb) 5. [Partial GIN Indexes for High-Cardinality Tables](#partial-gin-indexes-for-high-cardinality-tables) 6. [Avoiding Common Pitfalls](#avoiding-common-pitfalls) 7. [Takeaways](#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)