PostgreSQL JSONB in Laravel: Indexes & Eloquent Casts | 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. [ Why JSONB Belongs in Your Laravel Stack ](#why-jsonb-belongs-in-your-laravel-stack) 2. [ Indexing JSONB Correctly ](#indexing-jsonb-correctly) 3. [ GIN for Containment Queries ](#gin-for-containment-queries) 4. [ Expression Index for a Specific Path ](#expression-index-for-a-specific-path) 5. [ Querying JSONB in Eloquent ](#querying-jsonb-in-eloquent) 6. [ A Reusable Scope ](#a-reusable-scope) 7. [ Custom Eloquent Cast for Typed JSONB ](#custom-eloquent-cast-for-typed-jsonb) 8. [ Updating Nested Keys Without Overwriting ](#updating-nested-keys-without-overwriting) 9. [ Takeaways ](#takeaways) ![PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Pain](https://cdn.msaied.com/532/0c1c122849d3f997950ffca44076f86c.png) #laravel #postgresql #eloquent #jsonb PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Pain =============================================================================== 10 Aug 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 9 sections 1. [ 01 Why JSONB Belongs in Your Laravel Stack ](#why-jsonb-belongs-in-your-laravel-stack) 2. [ 02 Indexing JSONB Correctly ](#indexing-jsonb-correctly) 3. [ 03 GIN for Containment Queries ](#gin-for-containment-queries) 4. [ 04 Expression Index for a Specific Path ](#expression-index-for-a-specific-path) 5. [ 05 Querying JSONB in Eloquent ](#querying-jsonb-in-eloquent) 6. [ 06 A Reusable Scope ](#a-reusable-scope) 7. [ 07 Custom Eloquent Cast for Typed JSONB ](#custom-eloquent-cast-for-typed-jsonb) 8. [ 08 Updating Nested Keys Without Overwriting ](#updating-nested-keys-without-overwriting) 9. [ 09 Takeaways ](#takeaways) Why JSONB Belongs in Your Laravel Stack --------------------------------------- PostgreSQL's `jsonb` type is not a document-store escape hatch — it is a first-class column type with binary storage, deduplication, and indexable paths. Used correctly it eliminates entire pivot tables and EAV nightmares. Used naively it becomes an unindexed black hole that kills query plans. This article covers the three layers you need to get right: **indexing strategy**, **query builder patterns**, and **Eloquent casts**. --- Indexing JSONB Correctly ------------------------ ### GIN for Containment Queries The default GIN index covers the `@>` (contains) and `?` (key exists) operators — the two you will use most. ```sql CREATE INDEX idx_users_meta_gin ON users USING GIN (meta); ``` In a migration: ```php $table->jsonb('meta')->nullable(); DB::statement('CREATE INDEX idx_users_meta_gin ON users USING GIN (meta)'); ``` ### Expression Index for a Specific Path When you always filter on `meta->>'plan'`, a targeted B-tree expression index is cheaper than a full GIN index: ```sql CREATE INDEX idx_users_meta_plan ON users ((meta->>'plan')); ``` This index is used by `WHERE meta->>'plan' = 'pro'` and nothing else — tight and fast. --- Querying JSONB in Eloquent -------------------------- Laravel's query builder has no native JSONB operator support, but `whereRaw` and `->` / `->>` operators are readable enough: ```php // Containment: users whose meta contains {"plan": "pro"} User::whereRaw("meta @> ?::jsonb", [json_encode(['plan' => 'pro'])])->get(); // Text extraction: uses expression index above User::whereRaw("meta->>'plan' = ?", ['pro'])->get(); // Key existence User::whereRaw("meta \? ?", ['onboarded'])->get(); ``` ### A Reusable Scope Wrap the noise in a query scope so call sites stay clean: ```php // app/Models/Concerns/HasJsonbMeta.php trait HasJsonbMeta { public function scopeWhereMetaContains( Builder $query, array $subset, string $column = 'meta' ): Builder { return $query->whereRaw( "{$column} @> ?::jsonb", [json_encode($subset)] ); } public function scopeWhereMetaPath( Builder $query, string $path, mixed $value, string $column = 'meta' ): Builder { return $query->whereRaw( "{$column}->>'$path' = ?", [(string) $value] ); } } ``` Usage: ```php User::whereMetaContains(['plan' => 'pro', 'trial' => false])->paginate(); User::whereMetaPath('plan', 'pro')->whereMetaPath('locale', 'en')->get(); ``` --- Custom Eloquent Cast for Typed JSONB ------------------------------------ Storing arbitrary arrays is fine for prototypes. In production, cast to a typed DTO so you get IDE completion and validation at the boundary. ```php // app/Casts/UserMetaCast.php use Illuminate\Contracts\Database\Eloquent\CastsAttributes; class UserMetaCast implements CastsAttributes { public function get($model, string $key, $value, array $attributes): UserMeta { $data = json_decode($value ?? '{}', true); return UserMeta::fromArray($data); } public function set($model, string $key, $value, array $attributes): string { if ($value instanceof UserMeta) { return json_encode($value->toArray()); } return json_encode($value); } } ``` ```php // app/Data/UserMeta.php readonly class UserMeta { public function __construct( public string $plan = 'free', public string $locale = 'en', public bool $trial = false, ) {} public static function fromArray(array $data): self { return new self( plan: $data['plan'] ?? 'free', locale: $data['locale'] ?? 'en', trial: $data['trial'] ?? false, ); } public function toArray(): array { return ['plan' => $this->plan, 'locale' => $this->locale, 'trial' => $this->trial]; } } ``` Register on the model: ```php protected $casts = [ 'meta' => UserMetaCast::class, ]; ``` Now `$user->meta->plan` is typed, and saving is automatic. --- Updating Nested Keys Without Overwriting ---------------------------------------- Avoid loading the full row just to change one key. Use PostgreSQL's `jsonb_set`: ```php DB::table('users') ->where('id', $userId) ->update([ 'meta' => DB::raw("jsonb_set(meta, '{plan}', '\"enterprise\"')"), ]); ``` This is an atomic server-side update — no race condition, no full-row read. --- Takeaways --------- - Use a **GIN index** for containment/key-existence queries; use an **expression B-tree index** when filtering a single known path. - Prefer `@>` with `::jsonb` cast over `->>` string comparisons when you need multi-key containment — one operator, one index scan. - Wrap raw JSONB operators in **query scopes** or **macro helpers** to keep Eloquent call sites readable. - Cast JSONB columns to **typed readonly DTOs** rather than plain arrays; you get validation, IDE support, and serialization in one place. - Use `jsonb_set` for surgical key updates instead of read-modify-write cycles in PHP. 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-1&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-1) Frequently Asked Questions ---------------------------- 3 questions Q01 Does Laravel's built-in `array` cast work with JSONB columns? Yes, but it casts to a plain PHP array with no type safety. For production code, a custom cast backed by a typed readonly DTO gives you IDE completion, validation, and a clean serialization contract. Q02 When should I choose a GIN index over an expression B-tree index on a JSONB column? Use GIN when you query multiple paths or use containment (`@>`) and key-existence (`?`) operators. Use an expression B-tree index when you always filter on one specific path with equality — it is smaller and faster for that single access pattern. Q03 Can I use Eloquent's `where` method directly on JSONB paths? Laravel's `where('meta->plan', 'pro')` syntax works for MySQL JSON columns but does not translate to PostgreSQL JSONB operators. Use `whereRaw` with `->>` or `@>` operators, ideally wrapped in a reusable query scope. Continue reading More Articles --------------- [ View all ](https://www.msaied.com/articles) [ ![Laravel AI SDK v0.10: 4 New Features Explained](https://cdn.msaied.com/540/e74363f048913d3782bc16a7292215db.png) Laravel AI SDK AI Agents Filesystem Tools ### Laravel AI SDK v0.10: 4 New Features Explained Laravel AI SDK v0.10 ships with filesystem tools for agents, human tool approval, and more. This walkthrough c... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 12 Aug 2026 3 min read Read ](https://www.msaied.com/articles/laravel-ai-sdk-v010-4-new-features-explained) [ ![NightOwl: Laravel Monitoring With Flat Pricing and Your Own PostgreSQL Storage](https://cdn.msaied.com/538/60582948c0339b19e872f4f3c03171f2.png) Laravel Monitoring PostgreSQL ### NightOwl: Laravel Monitoring With Flat Pricing and Your Own PostgreSQL Storage NightOwl redirects Laravel Nightwatch telemetry into a PostgreSQL database you own, replacing per-event billin... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 11 Aug 2026 3 min read Read ](https://www.msaied.com/articles/nightowl-laravel-monitoring-with-flat-pricing-and-your-own-postgresql-storage) [ ![Mock PHP Classes in Tests With the Double Library](https://cdn.msaied.com/537/be45e68dffd72aa9bd833c2f409fcb07.png) testing mocking phpunit ### Mock PHP Classes in Tests With the Double Library Double is a PHP 8.3 test double library by Jason McCreary that replaces mocks, spies, and partials with a sing... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 11 Aug 2026 4 min read Read ](https://www.msaied.com/articles/mock-php-classes-in-tests-with-the-double-library) [ ![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)