PostgreSQL JSONB in Laravel: Indexing & Querying | Mohamed Said [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://msaied.com) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ ](https://github.com/EG-Mohamed) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ home ](https://msaied.com) [ articles ](https://msaied.com/articles) PostgreSQL JSONB in Laravel: Indexing, Querying, and Schema-less Columns Done Right On this page 1. [ Why JSONB Belongs in Your Laravel Toolkit ](#why-jsonb-belongs-in-your-laravel-toolkit) 2. [ Migration: Declaring the Column ](#migration-declaring-the-column) 3. [ GIN Indexes: The Non-Negotiable Step ](#gin-indexes-the-non-negotiable-step) 4. [ Querying Through Eloquent ](#querying-through-eloquent) 5. [ Custom Cast: Typed Value Object from JSONB ](#custom-cast-typed-value-object-from-jsonb) 6. [ Partial GIN Index for Sparse Data ](#partial-gin-index-for-sparse-data) 7. [ Takeaways ](#takeaways) ![PostgreSQL JSONB in Laravel: Indexing, Querying, and Schema-less Columns Done Right](https://cdn.msaied.com/356/592e07bf3b46443cb950d9bb31725ba6.png) #laravel #postgresql #eloquent #jsonb PostgreSQL JSONB in Laravel: Indexing, Querying, and Schema-less Columns Done Right ===================================================================================== 3 Jul 2026 3 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 Why JSONB Belongs in Your Laravel Toolkit ](#why-jsonb-belongs-in-your-laravel-toolkit) 2. [ 02 Migration: Declaring the Column ](#migration-declaring-the-column) 3. [ 03 GIN Indexes: The Non-Negotiable Step ](#gin-indexes-the-non-negotiable-step) 4. [ 04 Querying Through Eloquent ](#querying-through-eloquent) 5. [ 05 Custom Cast: Typed Value Object from JSONB ](#custom-cast-typed-value-object-from-jsonb) 6. [ 06 Partial GIN Index for Sparse Data ](#partial-gin-index-for-sparse-data) 7. [ 07 Takeaways ](#takeaways) Why JSONB Belongs in Your Laravel Toolkit ----------------------------------------- PostgreSQL's `jsonb` type stores JSON as a decomposed binary, making it faster to query than plain `json`. When your domain has genuinely variable attributes — product metadata, feature flags per tenant, user preferences — a `jsonb` column beats an EAV table or a pile of nullable columns. The catch: most Laravel codebases use it without indexes, then wonder why queries crawl at 100k rows. Migration: Declaring the Column ------------------------------- ```php Schema::table('products', function (Blueprint $table) { $table->jsonb('attributes')->default('{}'); }); ``` Always default to `'{}'` rather than `null` — it simplifies `whereJsonContains` logic and avoids null-coalescing in every query. GIN Indexes: The Non-Negotiable Step ------------------------------------ A full-column GIN index lets PostgreSQL answer containment (`@>`) and existence (`?`) operators in milliseconds: ```sql CREATE INDEX idx_products_attributes_gin ON products USING GIN (attributes); ``` In a migration: ```php DB::statement( 'CREATE INDEX idx_products_attributes_gin ON products USING GIN (attributes)' ); ``` For queries on a *specific* key, a functional B-tree index is cheaper: ```sql CREATE INDEX idx_products_brand ON products ((attributes->>'brand')); ``` Use GIN for "does this document contain this sub-object?" and functional B-tree for equality on a known key. Querying Through Eloquent ------------------------- Laravel's query builder wraps the most common JSONB operators cleanly: ```php // Containment: find products where attributes includes {"color": "red"} Product::whereJsonContains('attributes->color', 'red')->get(); // Key existence (raw, no built-in helper) Product::whereRaw("attributes \? 'warranty'"); // Nested path Product::whereJsonContains('attributes->dimensions->unit', 'cm')->get(); // Ordering by a JSONB key Product::orderByRaw("attributes->>'price_usd' DESC NULLS LAST")->get(); ``` `whereJsonContains` compiles to the `@>` operator, which the GIN index can satisfy. Raw `?` queries also hit the GIN index. Avoid `->>'key' LIKE '%value%'` — it forces a sequential scan. Custom Cast: Typed Value Object from JSONB ------------------------------------------ Raw arrays leak into your domain. A cast converts the column to a value object on read and back to JSON on write: ```php final class ProductAttributes { public function __construct( public readonly string $brand, public readonly string $color, public readonly ?float $weightKg = null, ) {} public static function fromArray(array $data): self { return new self( brand: $data['brand'] ?? '', color: $data['color'] ?? '', weightKg: isset($data['weight_kg']) ? (float) $data['weight_kg'] : null, ); } public function toArray(): array { return array_filter([ 'brand' => $this->brand, 'color' => $this->color, 'weight_kg' => $this->weightKg, ], fn ($v) => $v !== null); } } ``` ```php use Illuminate\Contracts\Database\Eloquent\CastsAttributes; class ProductAttributesCast implements CastsAttributes { public function get($model, $key, $value, $attributes): ProductAttributes { return ProductAttributes::fromArray( is_string($value) ? json_decode($value, true) : ($value ?? []) ); } public function set($model, $key, $value, $attributes): string { $array = $value instanceof ProductAttributes ? $value->toArray() : (array) $value; return json_encode($array, JSON_THROW_ON_ERROR); } } ``` Register it on the model: ```php protected $casts = [ 'attributes' => ProductAttributesCast::class, ]; ``` Now `$product->attributes->brand` is always a typed string, never `null` from a missing array key. Partial GIN Index for Sparse Data --------------------------------- If only 20% of rows have a `warranty` key, index only those rows: ```sql CREATE INDEX idx_products_warranty_gin ON products USING GIN (attributes) WHERE attributes ? 'warranty'; ``` Smaller index, faster maintenance, same query speed for the filtered subset. Takeaways --------- - Always add a GIN index before querying JSONB at scale; without it every query is a sequential scan. - Use functional B-tree indexes for equality on a single known key — they are smaller and faster than GIN for that case. - `whereJsonContains` compiles to `@>` and is index-friendly; raw `LIKE` on a JSONB path is not. - Wrap JSONB columns in a typed cast so domain code never touches raw arrays. - Partial GIN indexes cut index size dramatically when the key is sparse. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-jsonb-in-laravel-indexing-querying-and-schema-less-columns-done-right&text=PostgreSQL+JSONB+in+Laravel%3A+Indexing%2C+Querying%2C+and+Schema-less+Columns+Done+Right) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-jsonb-in-laravel-indexing-querying-and-schema-less-columns-done-right) Frequently Asked Questions ---------------------------- 3 questions Q01 Does whereJsonContains use the GIN index automatically? Yes. whereJsonContains compiles to the @> containment operator, which PostgreSQL's GIN index is designed to satisfy. Verify with EXPLAIN ANALYZE — you should see 'Bitmap Index Scan on idx_products_attributes_gin'. Q02 When should I use a functional B-tree index instead of GIN? When you query a single, well-known key with equality or range operators — for example (attributes->>'price_usd')::numeric > 100. A functional B-tree on that expression is smaller and faster for that specific access pattern than a full GIN index. Q03 Can I run Laravel schema migrations for GIN indexes without raw SQL? Not with Blueprint alone — there is no first-party GIN helper. Use DB::statement() inside your migration's up() method. Wrap it in a try/catch if you want idempotent re-runs, or check pg_indexes before creating. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Cursor Pagination, Chunked Iteration, and Lazy Collections at Scale in Laravel](https://cdn.msaied.com/355/3a6df23a2c16b740843260134fad7c63.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 3 Jul 2026 4 min read Read ](https://msaied.com/articles/cursor-pagination-chunked-iteration-and-lazy-collections-at-scale-in-laravel-1) [ ![Job Batching, Chaining, and Rate-Limited Middleware in Laravel Queues](https://cdn.msaied.com/353/89d47dc6b618d5435f9d7f333b75e922.png) laravel queues jobs ### Job Batching, Chaining, and Rate-Limited Middleware in Laravel Queues Go beyond basic dispatch: learn how to compose Laravel job batches with callbacks, chain dependent jobs safely... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 3 Jul 2026 3 min read Read ](https://msaied.com/articles/job-batching-chaining-and-rate-limited-middleware-in-laravel-queues-2) [ ![Laravel Reverb: Building Presence Channels with Per-User State and Typed Events](https://cdn.msaied.com/352/9b3c490b8303fdc84442671965a3ee8a.png) laravel reverb websockets ### Laravel Reverb: Building Presence Channels with Per-User State and Typed Events Presence channels in Laravel Reverb go far beyond simple pub/sub. Learn how to track per-user state, broadcast... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 3 Jul 2026 3 min read Read ](https://msaied.com/articles/laravel-reverb-building-presence-channels-with-per-user-state-and-typed-events) [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://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://msaied.com) - [Projects](https://msaied.com/projects) - [Articles](https://msaied.com/articles) - [Certificates](https://msaied.com/certificates) - [Contact](https://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)