PostgreSQL JSONB in Laravel: Indexes & Casting | 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 Chaos On this page 1. [ Why JSONB and Not Just JSON? ](#why-jsonb-and-not-just-json) 2. [ GIN Indexes: The Right Tool for JSONB ](#gin-indexes-the-right-tool-for-jsonb) 3. [ Querying JSONB from Eloquent ](#querying-jsonb-from-eloquent) 4. [ Generated Columns for Selective B-tree Indexes ](#generated-columns-for-selective-b-tree-indexes) 5. [ Eloquent Casts: Keeping PHP Types Honest ](#eloquent-casts-keeping-php-types-honest) 6. [ Avoiding the Silent Performance Traps ](#avoiding-the-silent-performance-traps) 7. [ Takeaways ](#takeaways) ![PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Chaos](https://cdn.msaied.com/526/bc43aae3afe723f9a29f47820735edf5.png) #laravel #postgresql #jsonb #eloquent #performance PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Chaos ================================================================================ 9 Aug 2026 3 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 Why JSONB and Not Just JSON? ](#why-jsonb-and-not-just-json) 2. [ 02 GIN Indexes: The Right Tool for JSONB ](#gin-indexes-the-right-tool-for-jsonb) 3. [ 03 Querying JSONB from Eloquent ](#querying-jsonb-from-eloquent) 4. [ 04 Generated Columns for Selective B-tree Indexes ](#generated-columns-for-selective-b-tree-indexes) 5. [ 05 Eloquent Casts: Keeping PHP Types Honest ](#eloquent-casts-keeping-php-types-honest) 6. [ 06 Avoiding the Silent Performance Traps ](#avoiding-the-silent-performance-traps) 7. [ 07 Takeaways ](#takeaways) Why JSONB and Not Just JSON? ---------------------------- PostgreSQL offers two JSON column types. `json` stores raw text and re-parses it on every read. `jsonb` stores a decomposed binary representation, supports indexing, and enables operator-based querying. For any column you will filter or index, always choose `jsonb`. ```sql -- migration $table->jsonb('meta')->nullable(); ``` --- GIN Indexes: The Right Tool for JSONB ------------------------------------- A plain B-tree index on a `jsonb` column is useless for containment queries. You need a **GIN** (Generalized Inverted Index) index. ```php // database/migrations/xxxx_add_gin_index_to_products.php public function up(): void { DB::statement( 'CREATE INDEX products_meta_gin ON products USING GIN (meta)' ); } ``` For queries that target a single known key path, a **GIN index with `jsonb_path_ops`** is smaller and faster: ```php DB::statement( 'CREATE INDEX products_meta_path_gin ON products USING GIN (meta jsonb_path_ops)' ); ``` Use `jsonb_path_ops` when you only need the `@>` containment operator. Use the default opclass when you also need `?`, `?|`, or `?&` key-existence operators. --- Querying JSONB from Eloquent ---------------------------- Laravel's query builder exposes `whereJsonContains`, `whereJsonLength`, and raw expressions for everything else. ```php // Containment — uses the GIN index Product::whereJsonContains('meta->tags', 'featured')->get(); // Key-path equality — add a generated column + B-tree for this pattern Product::whereJsonPath('meta', '$.status', '=', 'active')->get(); // Raw operator when you need full control Product::whereRaw("meta @> ?::jsonb", [json_encode(['tier' => 'pro'])])->get(); ``` > **Tip:** `whereJsonContains` emits the `@>` operator under the hood for PostgreSQL, so your GIN index will be hit. Verify with `EXPLAIN ANALYZE`. --- Generated Columns for Selective B-tree Indexes ---------------------------------------------- When you repeatedly filter on one stable key, a **generated (stored) column** plus a normal B-tree index beats a GIN index on cardinality-heavy data. ```php DB::statement( "ALTER TABLE products ADD COLUMN meta_status TEXT GENERATED ALWAYS AS (meta->>'status') STORED" ); DB::statement( 'CREATE INDEX products_meta_status_btree ON products (meta_status)' ); ``` Now `WHERE meta_status = 'active'` uses a tight B-tree scan instead of a GIN bitmap scan. --- Eloquent Casts: Keeping PHP Types Honest ---------------------------------------- Storing raw arrays is fine for prototypes, but production code deserves typed value objects. ```php // app/Casts/ProductMetaCast.php use Illuminate\Contracts\Database\Eloquent\CastsAttributes; class ProductMetaCast implements CastsAttributes { public function get($model, string $key, $value, array $attributes): ProductMeta { return ProductMeta::fromArray(json_decode($value, true) ?? []); } public function set($model, string $key, $value, array $attributes): string { return json_encode( $value instanceof ProductMeta ? $value->toArray() : $value ); } } ``` ```php // app/Models/Product.php protected $casts = [ 'meta' => ProductMetaCast::class, ]; ``` Your `ProductMeta` value object can enforce invariants, provide typed accessors, and keep business logic out of the model. --- Avoiding the Silent Performance Traps ------------------------------------- - **Never** use `->` or `->>` inside a `WHERE` without a supporting index or generated column — it triggers a sequential scan. - `whereJsonLength` does not use a GIN index; add a generated column if you filter by array length frequently. - Avoid storing deeply nested, frequently-updated structures in JSONB. Write amplification on updates is real. - Run `EXPLAIN (ANALYZE, BUFFERS)` — not just `EXPLAIN` — to confirm index usage and shared-buffer hits. --- Takeaways --------- - Always use `jsonb`, never `json`, for any column you will index or query. - GIN indexes with `jsonb_path_ops` are the default choice; fall back to the full opclass only when you need key-existence operators. - Generated stored columns + B-tree indexes outperform GIN for high-cardinality single-key filters. - Wrap JSONB columns in typed Eloquent casts to enforce invariants at the PHP layer. - Validate every JSONB query with `EXPLAIN (ANALYZE, BUFFERS)` before shipping to production. 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-chaos-2&text=PostgreSQL+JSONB+in+Laravel%3A+Indexing%2C+Querying%2C+and+Casting+Without+the+Chaos) [ ](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-chaos-2) Frequently Asked Questions ---------------------------- 3 questions Q01 Does `whereJsonContains` in Laravel use a GIN index on PostgreSQL? Yes. On PostgreSQL, `whereJsonContains` compiles to the `@>` containment operator, which is supported by a GIN index. Confirm with `EXPLAIN ANALYZE` to ensure the planner chooses the index over a sequential scan. Q02 When should I use a generated column instead of a GIN index for JSONB? Use a generated stored column with a B-tree index when you repeatedly filter on a single, stable JSONB key with high cardinality. B-tree lookups on a scalar column are faster and cheaper than GIN bitmap scans in those cases. Q03 Can I use PHP value objects as Eloquent casts for JSONB columns? Yes. Implement `CastsAttributes`, deserialize the JSON string into your value object in `get`, and serialize it back in `set`. This keeps type safety and business rules at the PHP layer without polluting the model. 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)