PostgreSQL JSONB in Laravel: Index, Query & Cast | Mohamed Said       [Skip to content](#main)  [ ![](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed SaidLaravel Backend Engineer ](https://msaied.com) - [Home](https://msaied.com)
- [Projects](https://msaied.com/projects)
- [Articles](https://msaied.com/articles)
- [Certificates](https://msaied.com/certificates)
- [About](https://msaied.com#about)

           [  Contact](https://msaied.com#contact) Menu 

Menu
----

Close 

 - [HomeStart here](https://msaied.com)
- [ProjectsCase studies](https://msaied.com/projects)
- [ArticlesEngineering notes](https://msaied.com/articles)
- [CertificatesCredentials](https://msaied.com/certificates)
- [AboutHow I work](https://msaied.com#about)
- [ContactGet in touch](https://msaied.com#contact)

  [Start a conversation](https://msaied.com#contact) [WhatsApp](https://wa.me/201094619204) [Email](mailto:hello@msaied.com) 

 1. [Home](https://msaied.com)
2. /
3. [Articles](https://msaied.com/articles)
4. /
5. PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Mess

 PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Mess
==============================================================================

 JSONB columns unlock flexible schemas, but misused they become performance traps. Learn how to index, query, and cast JSONB in Laravel with precision — no raw SQL sprawl.

 ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://msaied.com#person) Published 8 Oct 2026 · Updated 8 Oct 2026 · 4 min read

ShareCopy linkCopied

 ![PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Mess](https://cdn.msaied.com/755/2afbdc0a82de6bd61d3739c09554dda0.png) 

  On this page +1. [Why JSONB and Not JSON?](#why-jsonb-and-not-json)
2. [GIN Indexes: The Non-Negotiable First Step](#gin-indexes-the-non-negotiable-first-step)
3. [Querying JSONB with Eloquent](#querying-jsonb-with-eloquent)
4. [Custom Eloquent Casts for Typed JSONB](#custom-eloquent-casts-for-typed-jsonb)
5. [Avoiding Common Pitfalls](#avoiding-common-pitfalls)
6. [Pitfall 1: Querying un-indexed paths at scale](#pitfall-1-querying-un-indexed-paths-at-scale)
7. [Pitfall 2: Storing data you will always need to join on](#pitfall-2-storing-data-you-will-always-need-to-join-on)
8. [Pitfall 3: Forgetting jsonb\_set for partial updates](#pitfall-3-forgetting-codejsonb-setcode-for-partial-updates)
9. [Takeaways](#takeaways)

 Why JSONB and Not JSON?
-----------------------

PostgreSQL offers both `json` and `jsonb` column types. `json` stores raw text and re-parses on every read. `jsonb` stores a decomposed binary representation — faster to query, indexable, and the right default for any column you intend to filter or aggregate.

The migration is straightforward:

```php
Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->jsonb('attributes')->default('{}');
    $table->timestamps();
});

```

GIN Indexes: The Non-Negotiable First Step
------------------------------------------

Without an index, every JSONB predicate triggers a sequential scan. A GIN (Generalized Inverted Index) index covers containment (`@>`) and existence (`?`) operators across the entire document.

```php
// In a migration
$table->rawIndex(
    "attributes gin_trix_ops",
    'products_attributes_gin'
);
// Or the standard GIN:
DB::statement(
    'CREATE INDEX products_attributes_gin ON products USING GIN (attributes)'
);

```

For queries on a *specific* key path, a functional B-tree index is cheaper and more selective:

```sql
CREATE INDEX products_attributes_brand
  ON products ((attributes->>'brand'));

```

Add this via `DB::statement()` in a migration. Laravel's `Blueprint` does not yet expose a first-class API for expression indexes.

Querying JSONB with Eloquent
----------------------------

Laravel ships `whereJsonContains`, `whereJsonLength`, and arrow-operator paths out of the box.

```php
// Containment — uses the GIN index
Product::whereJsonContains('attributes->tags', 'wireless')->get();

// Exact key value — uses functional B-tree if defined
Product::where('attributes->brand', 'Acme')->get();

// Nested path
Product::where('attributes->dimensions->weight', '>', 1.5)->get();

// Array length
Product::whereJsonLength('attributes->tags', '>=', 3)->get();

```

For operators PostgreSQL exposes but Laravel does not wrap (`@>`, `?|`, `#>>`) drop to a raw expression:

```php
Product::whereRaw("attributes @> ?::jsonb", [json_encode(['color' => 'red'])])->get();

```

Always bind values through the parameter array — never interpolate user input into raw expressions.

Custom Eloquent Casts for Typed JSONB
-------------------------------------

Storing arbitrary arrays is fine for prototypes. In production, cast JSONB to a typed DTO or value object so the rest of your codebase never touches raw arrays.

```php
// app/Casts/ProductAttributesCast.php
use Illuminate\Contracts\Database\Eloquent\CastsAttributes;

class ProductAttributesCast implements CastsAttributes
{
    public function get($model, string $key, $value, array $attributes): ProductAttributes
    {
        return ProductAttributes::fromArray(json_decode($value, true) ?? []);
    }

    public function set($model, string $key, $value, array $attributes): string
    {
        $data = $value instanceof ProductAttributes ? $value->toArray() : $value;
        return json_encode($data);
    }
}

```

```php
// app/Models/Product.php
protected $casts = [
    'attributes' => ProductAttributesCast::class,
];

```

Now `$product->attributes` is always a `ProductAttributes` object — no `null` coalescing scattered across controllers.

Avoiding Common Pitfalls
------------------------

### Pitfall 1: Querying un-indexed paths at scale

Run `EXPLAIN ANALYZE` before shipping any JSONB filter. A `Seq Scan` on a large table is a production incident waiting to happen.

```sql
EXPLAIN ANALYZE
SELECT * FROM products WHERE attributes @> '{"brand":"Acme"}';

```

If you see `Seq Scan`, add the appropriate GIN or functional index.

### Pitfall 2: Storing data you will always need to join on

JSONB is not a substitute for normalized columns. If you filter by `brand` on every listing page, promote it to a real column with a standard B-tree index. Reserve JSONB for genuinely variable, sparse, or user-defined attributes.

### Pitfall 3: Forgetting `jsonb_set` for partial updates

Eloquent's `update(['attributes->color' => 'blue'])` rewrites the entire column. For high-write tables, use `jsonb_set` to patch a single key:

```php
DB::table('products')
    ->where('id', $id)
    ->update([
        'attributes' => DB::raw("jsonb_set(attributes, '{color}', '\"blue\"')"),
    ]);

```

Takeaways
---------

- Always use `jsonb`, never `json`, for any column you query or index.
- Add a GIN index for containment queries; add functional B-tree indexes for single-key filters.
- Use `whereJsonContains` and path syntax for clean Eloquent queries; drop to `whereRaw` only for unsupported operators.
- Cast JSONB columns to typed value objects — raw array access is a maintenance liability.
- Run `EXPLAIN ANALYZE` before every new JSONB filter reaches production.

- [laravel](https://msaied.com/articles?search=laravel)
- [postgresql](https://msaied.com/articles?search=postgresql)
- [eloquent](https://msaied.com/articles?search=eloquent)
- [jsonb](https://msaied.com/articles?search=jsonb)

 Frequently asked questions 
---------------------------

  Does Laravel's whereJsonContains use the GIN index automatically?Yes, when the generated SQL uses the @> containment operator and a GIN index exists on the column, PostgreSQL's query planner will use it. Verify with EXPLAIN ANALYZE — the planner may still choose a seq scan if the table is small or statistics are stale.

   Should I store every flexible attribute in JSONB?No. Columns you filter, sort, or join on frequently belong as real typed columns with standard indexes. JSONB shines for sparse, user-defined, or schema-variable data where you cannot enumerate all keys at design time.

   Can I use Laravel's AsArrayObject or AsCollection cast instead of a custom cast?AsArrayObject and AsCollection are convenient for simple cases, but they return generic structures with no type safety. For domain models, a custom cast that returns a typed DTO is worth the extra twenty lines.

   ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp)About the author
----------------

[Mohamed Said](https://msaied.com#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://msaied.com#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 articleLaravel Fake Assertions Now Accept Property Arrays in Laravel 13.35](https://msaied.com/articles/laravel-fake-assertions-now-accept-property-arrays-in-laravel-1335)  

   On this page
-------------

1. [Why JSONB and Not JSON?](#why-jsonb-and-not-json)
2. [GIN Indexes: The Non-Negotiable First Step](#gin-indexes-the-non-negotiable-first-step)
3. [Querying JSONB with Eloquent](#querying-jsonb-with-eloquent)
4. [Custom Eloquent Casts for Typed JSONB](#custom-eloquent-casts-for-typed-jsonb)
5. [Avoiding Common Pitfalls](#avoiding-common-pitfalls)
6. [Pitfall 1: Querying un-indexed paths at scale](#pitfall-1-querying-un-indexed-paths-at-scale)
7. [Pitfall 2: Storing data you will always need to join on](#pitfall-2-storing-data-you-will-always-need-to-join-on)
8. [Pitfall 3: Forgetting jsonb\_set for partial updates](#pitfall-3-forgetting-codejsonb-setcode-for-partial-updates)
9. [Takeaways](#takeaways)

 ###  Have a technical challenge?

 Tell me what you’re building. I reply within two working days.

[Start a conversation](https://msaied.com#contact) 

   Related articles
-----------------

 [ ![](https://cdn.msaied.com/754/1ab77f05a0bd841971617d39adabd22e.png) Laravel · 3 min read### Laravel Fake Assertions Now Accept Property Arrays in Laravel 13.35

7 Oct 2026 ](https://msaied.com/articles/laravel-fake-assertions-now-accept-property-arrays-in-laravel-1335) [ ![](https://cdn.msaied.com/753/fea306145a8fc693561ccd02ff95afa5.png) Laravel · 3 min read### Filament v5.10.1 Released: Bug Fixes for Tables, Select Search, and Dropdown Navigation

7 Oct 2026 ](https://msaied.com/articles/filament-v5101-released-bug-fixes-for-tables-select-search-and-dropdown-navigation) [ ![](https://cdn.msaied.com/751/f7f990b3f16958e2c8634c128cc211e6.png) Laravel · 3 min read### Filament v4.15.1 Released: Bug Fixes for Tables, Select Search, and Dropdown Navigation

7 Oct 2026 ](https://msaied.com/articles/filament-v4151-released-bug-fixes-for-tables-select-search-and-dropdown-navigation) 

  Have a technical challenge?
----------------------------

Tell me what you’re building. I reply within two working days.

 [Discuss your project ↗](https://msaied.com#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://msaied.com)
- [Articles](https://msaied.com/articles)
- [Certificates](https://msaied.com/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://msaied.com/sitemap.xml)