PostgreSQL JSON for Laravel

PostgreSQL's JSON support brings NoSQL flexibility to relational databases. Store and query semi-structured data while maintaining ACID compliance. At ZIRA Software, we use JSONB extensively for flexible data models.

JSON vs JSONB

JSON                          JSONB
├── Stores exact text         ├── Stores binary format
├── Preserves whitespace      ├── No whitespace preserved
├── Preserves key order       ├── Keys may reorder
├── Faster writes             ├── Faster reads
└── No indexing               └── Full indexing support

Recommendation: Use JSONB for most cases

Migration Setup

// Create table with JSONB column
Schema::create('products', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->jsonb('attributes'); // PostgreSQL JSONB
    $table->jsonb('metadata')->nullable();
    $table->timestamps();
});

// Add JSONB column to existing table
Schema::table('users', function (Blueprint $table) {
    $table->jsonb('preferences')->default('{}');
});

Model Configuration

// app/Models/Product.php
class Product extends Model
{
    protected $fillable = ['name', 'attributes', 'metadata'];

    protected $casts = [
        'attributes' => 'array',
        'metadata' => 'array',
    ];
}

// Usage
$product = Product::create([
    'name' => 'Laptop',
    'attributes' => [
        'brand' => 'Dell',
        'ram' => '16GB',
        'storage' => '512GB SSD',
        'ports' => ['USB-C', 'HDMI', 'USB-A'],
    ],
]);

// Access as array
echo $product->attributes['brand']; // Dell

Querying JSON Data

// Query by JSON key
$dellProducts = Product::where('attributes->brand', 'Dell')->get();

// Nested key query
$products = Product::where('attributes->specs->ram', '16GB')->get();

// Check if key exists
$products = Product::whereNotNull('attributes->warranty')->get();

// Query array contains
$products = Product::whereJsonContains('attributes->ports', 'USB-C')->get();

// Multiple contains
$products = Product::whereJsonContains('attributes->ports', ['USB-C', 'HDMI'])->get();

// JSON length
$products = Product::whereJsonLength('attributes->ports', '>', 2)->get();

Advanced PostgreSQL Queries

// Using raw PostgreSQL JSON operators
$products = Product::whereRaw("attributes->>'brand' ILIKE ?", ['%dell%'])->get();

// JSON path queries (PostgreSQL 12+)
$products = Product::whereRaw("attributes @? '$.ports[*] ? (@ == \"USB-C\")'");

// Aggregate JSON values
$brands = DB::table('products')
    ->selectRaw("attributes->>'brand' as brand, COUNT(*) as count")
    ->groupByRaw("attributes->>'brand'")
    ->get();

// Update specific JSON key
Product::where('id', 1)
    ->update([
        'attributes->price' => 999,
        'attributes->in_stock' => true,
    ]);

Indexing JSONB

// GIN index for general querying
Schema::table('products', function (Blueprint $table) {
    DB::statement('CREATE INDEX products_attributes_gin ON products USING GIN (attributes)');
});

// Index specific key for equality queries
DB::statement("CREATE INDEX products_brand_idx ON products ((attributes->>'brand'))");

// Partial index
DB::statement("CREATE INDEX products_active_idx ON products USING GIN (attributes) WHERE (attributes->>'active')::boolean = true");

Practical Examples

// E-commerce product variants
class Product extends Model
{
    protected $casts = ['variants' => 'array'];

    public function getVariantPrice($size, $color)
    {
        $variant = collect($this->variants)->first(function ($v) use ($size, $color) {
            return $v['size'] === $size && $v['color'] === $color;
        });

        return $variant['price'] ?? $this->base_price;
    }
}

// User preferences
class User extends Model
{
    protected $casts = ['preferences' => 'array'];

    public function getPreference($key, $default = null)
    {
        return data_get($this->preferences, $key, $default);
    }

    public function setPreference($key, $value)
    {
        $preferences = $this->preferences ?? [];
        data_set($preferences, $key, $value);
        $this->update(['preferences' => $preferences]);
    }
}

// Usage
$user->setPreference('notifications.email', true);
$user->setPreference('theme', 'dark');
echo $user->getPreference('notifications.email'); // true

Conclusion

PostgreSQL JSONB combines relational reliability with document flexibility. Use it for attributes, preferences, and any semi-structured data that varies between records.

Need database architecture help? Contact ZIRA Software for PostgreSQL consulting.