PostgreSQL JSONB in Laravel: Indexing & Casting | 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 let you store flexible data without sacrificing query performance. Learn how to index, query, and cast JSONB in Laravel using real Eloquent patterns that stay fast at scale. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 9 Aug 2026 · Updated 9 Aug 2026 · 4 min read ShareCopy linkCopied ![PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Bloat](https://cdn.msaied.com/529/5d6560d5f6f0a2cdc483fbfecc24707d.png) On this page +1. [PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Bloat](#postgresql-jsonb-in-laravel-indexing-querying-and-casting-without-the-bloat) 2. [When JSONB Makes Sense](#when-jsonb-makes-sense) 3. [Migration: Column + GIN Index](#migration-column-gin-index) 4. [Querying JSONB with Eloquent](#querying-jsonb-with-eloquent) 5. [Typed Casts: Stop Reading Raw Arrays](#typed-casts-stop-reading-raw-arrays) 6. [Updating Partial Paths](#updating-partial-paths) 7. [Takeaways](#takeaways) PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Bloat ------------------------------------------------------------------------------ JSONB is one of PostgreSQL's most practical features for product engineers. It lets you store semi-structured data alongside relational columns without spinning up a separate document store. But used carelessly, JSONB columns become black holes: unindexed, untyped, and impossible to query efficiently. This article covers the three things you actually need to get right: **indexing**, **querying via Eloquent**, and **casting to typed PHP objects**. --- ### When JSONB Makes Sense JSONB is not a replacement for normalized tables. Use it when: - The shape of the data varies per row (e.g., feature flags, metadata bags, third-party webhook payloads). - You need to query *into* the structure, not just store and retrieve it. - The alternative is an EAV table, which is almost always worse. Avoid JSONB for data you join on, aggregate with `GROUP BY`, or reference from foreign keys. --- ### Migration: Column + GIN Index ```php Schema::create('products', function (Blueprint $table) { $table->id(); $table->string('name'); $table->jsonb('attributes')->default('{}'); $table->timestamps(); }); // Separate migration for the index DB::statement( "CREATE INDEX products_attributes_gin ON products USING GIN (attributes)" ); ``` The GIN (Generalized Inverted Index) index supports the `@>` containment operator, which powers `whereJsonContains`. Without it, every JSONB query does a full sequential scan. If you only ever query a single key path, a partial B-tree index on an expression is cheaper: ```sql CREATE INDEX products_attributes_color ON products ((attributes->>'color')); ``` --- ### Querying JSONB with Eloquent Laravel's query builder has first-class JSONB support through a handful of methods: ```php // Containment: uses the GIN index via @> Product::whereJsonContains('attributes->tags', 'sale')->get(); // Key existence (also GIN-indexed with jsonb_ops) Product::whereRaw("attributes \?| array['color','size']"); // Scalar comparison on a path Product::where('attributes->stock', '>', 0)->get(); // Nested path Product::whereJsonContains('attributes->shipping->methods', 'express')->get(); ``` `whereJsonContains` compiles to `@>` under the hood when targeting PostgreSQL, so your GIN index is used automatically. The scalar comparison (`attributes->stock`) casts to text by default — use `->>'stock'` for text or `(attributes->>'stock')::int` for numeric comparisons via `whereRaw`. --- ### Typed Casts: Stop Reading Raw Arrays Returning a raw `array` from a JSONB column is a footgun. Define a typed cast instead: ```php // app/Casts/ProductAttributes.php use Illuminate\Contracts\Database\Eloquent\CastsAttributes; class ProductAttributes implements CastsAttributes { public function get($model, string $key, $value, array $attributes): Attributes { return Attributes::fromArray(json_decode($value, true) ?? []); } public function set($model, string $key, $value, array $attributes): string { return json_encode($value instanceof Attributes ? $value->toArray() : $value); } } ``` ```php // app/Data/Attributes.php readonly class Attributes { public function __construct( public readonly array $tags = [], public readonly ?string $color = null, public readonly int $stock = 0, ) {} public static function fromArray(array $data): self { return new self( tags: $data['tags'] ?? [], color: $data['color'] ?? null, stock: $data['stock'] ?? 0, ); } public function toArray(): array { return ['tags' => $this->tags, 'color' => $this->color, 'stock' => $this->stock]; } } ``` ```php // Product model protected $casts = [ 'attributes' => ProductAttributes::class, ]; ``` Now `$product->attributes->color` is typed, IDE-friendly, and never returns `null` unexpectedly. --- ### Updating Partial Paths Avoid re-serializing the entire column when you only change one key. Use `jsonb_set`: ```php DB::table('products') ->where('id', $product->id) ->update([ 'attributes' => DB::raw( "jsonb_set(attributes, '{stock}', '" . (int) $newStock . "')" ), ]); ``` This is a single atomic write and avoids a read-modify-write race condition. --- ### Takeaways - Always add a GIN index on JSONB columns you query with `whereJsonContains` or `@>`. - Use expression indexes (B-tree on a path) when querying a single scalar key repeatedly. - Wrap JSONB columns in a typed `CastsAttributes` class — raw arrays are untyped debt. - Use `jsonb_set` for partial updates to avoid overwriting concurrent changes. - JSONB is a tool for flexible metadata, not a substitute for relational modeling. - [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) - [performance](https://www.msaied.com/public/articles?search=performance) Frequently asked questions --------------------------- Does whereJsonContains use a GIN index automatically in PostgreSQL?Yes. Laravel compiles whereJsonContains to the @> containment operator on PostgreSQL, which is supported by a GIN index created with the default jsonb\_ops operator class. Without the index, the query falls back to a sequential scan. Should I use json or jsonb in PostgreSQL with Laravel?Always prefer jsonb. It stores data in a decomposed binary format, supports indexing, and allows operators like @> and ?. The json type stores raw text and cannot be indexed efficiently. The storage overhead of jsonb is negligible in practice. Can I use Spatie Laravel Data instead of a manual cast class?Yes. Spatie Laravel Data DTOs implement CastsAttributes automatically when you cast a column to a Data class. This is a clean alternative if you already have the package in your project, and it adds validation on top of casting. ![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-1) [Next articleLaravel Truss: Live Interactive Database ER Diagrams in the Browser](https://www.msaied.com/public/articles/laravel-truss-live-interactive-database-er-diagrams-in-the-browser) On this page ------------- 1. [PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Bloat](#postgresql-jsonb-in-laravel-indexing-querying-and-casting-without-the-bloat) 2. [When JSONB Makes Sense](#when-jsonb-makes-sense) 3. [Migration: Column + GIN Index](#migration-column-gin-index) 4. [Querying JSONB with Eloquent](#querying-jsonb-with-eloquent) 5. [Typed Casts: Stop Reading Raw Arrays](#typed-casts-stop-reading-raw-arrays) 6. [Updating Partial Paths](#updating-partial-paths) 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)