PostgreSQL JSONB in Laravel: Indexing & Querying | 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 Schema-less Columns Done Right PostgreSQL JSONB in Laravel: Indexing, Querying, and Schema-less Columns Done Right ==================================================================================== JSONB columns can replace entire pivot tables or EAV nightmares — if you index them correctly and query through Eloquent without leaking raw SQL everywhere. Here is the practical playbook. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 3 Jul 2026 · Updated 3 Jul 2026 · 3 min read ShareCopy linkCopied ![PostgreSQL JSONB in Laravel: Indexing, Querying, and Schema-less Columns Done Right](https://cdn.msaied.com/356/592e07bf3b46443cb950d9bb31725ba6.png) 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) 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. - [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 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'. 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. 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. ![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-1) [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-2) 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) ### 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)