Using JSONB Columns in PostgreSQL with Eloquent ORM

Why Use JSONB in PostgreSQL with Laravel

Modern applications often need to store dynamic or semi-structured data. Think of user settings, product metadata, or API responses.

PostgreSQL’s JSONB column type provides a way to store JSON efficiently with indexing and powerful querying—perfect for Laravel developers who want schema flexibility with solid performance.

JSONB gives you the best of both worlds: structure when you want it, flexibility when you need it.

Key Takeaways

• JSONB lets you store structured JSON data in PostgreSQL
• Eloquent ORM can read/write to JSONB columns like any other column
• JSONB supports indexing, containment, and advanced querying
• Great for optional data, user preferences, dynamic forms, or APIs

Creating JSONB Columns in Laravel Migrations

Start by defining your column in a migration:

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

Run migration:

php artisan migrate

This attributes column can now store JSON structures like:

{
  "color": "red",
  "size": "L",
  "in_stock": true
}

Working with JSONB Using Eloquent

Writing JSON to the Column

Product::create([
  'name' => 'Hoodie',
  'attributes' => [
    'color' => 'black',
    'material' => 'cotton',
    'in_stock' => true,
  ]
]);

Reading JSON Values

$product = Product::find(1);
echo $product->attributes['color']; // black

Updating JSON Fields

$product->attributes['in_stock'] = false;
$product->save();

Querying JSONB Columns in Laravel

Using whereJsonContains

Product::whereJsonContains('attributes->color', 'black')->get();

Using where with PostgreSQL operator

Product::whereRaw("attributes @> '{\"color\":\"black\"}'")->get();

Partial Match or Key Exists

Product::whereRaw("attributes ? 'in_stock'")->get();

You can also use JSON path queries or index specific fields.

Indexing JSONB Columns

Add a GIN index for better performance:

DB::statement("CREATE INDEX products_attributes_gin ON products USING GIN (attributes);");

Or index a specific path:

CREATE INDEX idx_color ON products ((attributes->>'color'));

Use Laravel’s raw DB statements inside migrations or seeders.

When to Use JSONB in Your Schema

Use Case	Why JSONB Works
Product metadata	Not all products have same attributes
User preferences	Store UI settings, preferences
API logs	Flexible structure per request
Feature flags	Environment-specific dynamic flags
Survey answers	Dynamic question fields

Avoid JSONB for data that needs heavy joins or consistent structure—normalize instead.

Best Practices for JSONB in Laravel

• Use JSONB over JSON for performance and indexing
• Add GIN indexes on JSONB columns if you’ll query them often
• Avoid deeply nested JSON—keep keys flat where possible
• Keep logic in the model (e.g. accessors/mutators) for better readability
• Document expected structure in your codebase

Helpful Model Accessors and Mutators

Example: Convert booleans or enforce structure

public function setAttributesAttribute($value)
{
    $value['in_stock'] = (bool) $value['in_stock'];
    $this->attributes['attributes'] = json_encode($value);
}

Comparison Table: JSONB vs Traditional Columns

Feature	JSONB	Traditional Columns
Flexibility	✅ High	❌ Rigid
Query Speed	⚠️ Medium (needs index)	✅ Fast
Indexing	✅ GIN & Expression Index	✅ Default
Joins	❌ Limited	✅ Strong
Schema Enforcement	❌ None	✅ Strong

FAQ

Is JSONB better than JSON in PostgreSQL? Yes. JSONB is stored in binary and supports indexing and faster querying.

Can I sort by a JSONB field? Yes, using expression indexes or raw SQL:

Product::orderByRaw("attributes->>'color'")->get();

Can I store arrays in JSONB? Absolutely. You can store lists and even query with @> or ? operators.

Can I validate JSON structure in Laravel? Yes, use array and json rules in validation, or use custom validation logic.

Helpful Resources

• Laravel Docs – Database: JSON Columns
• PostgreSQL Docs – JSON Types
• Laravel Schema Builder

Conclusion

JSONB in PostgreSQL gives Laravel developers a huge advantage for projects with flexible or variable data. You get fast, indexed, semi-structured storage without reaching for a NoSQL database.

Used properly, JSONB unlocks scalable features like searchable product filters, dynamic metadata, or multilingual content in a single table.

Laravel’s support through Eloquent and raw SQL makes this feature both powerful and easy to implement.