PostgreSQL JSON in Laravel

PostgreSQL JSONB columns combine document flexibility with relational power. Laravel provides fluent methods for JSON querying. At ZIRA Software, JSONB powers flexible schemas while maintaining query performance.

JSON vs JSONB

| Feature | JSON | JSONB | |---------|------|-------| | Storage | Text as-is | Binary, parsed | | Write speed | Faster | Slower | | Read speed | Slower | Faster | | Indexing | No | Yes (GIN) | | Key ordering | Preserved | Not preserved |

Recommendation: Use JSONB for most cases.

Migration Setup

// database/migrations/create_products_table.php
Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->decimal('price', 10, 2);
    $table->jsonb('attributes')->nullable();
    $table->jsonb('metadata')->default('{}');
    $table->timestamps();
});

// Add GIN index for fast queries
Schema::table('products', function (Blueprint $table) {
    $table->index('attributes', 'products_attributes_gin')
        ->algorithm('gin');
});

Model Configuration

// app/Models/Product.php
class Product extends Model
{
    protected $casts = [
        'attributes' => 'array',
        'metadata' => 'object',
    ];

    protected $fillable = [
        'name',
        'price',
        'attributes',
        'metadata',
    ];
}

Basic JSON Queries

// Query JSON key
$products = Product::where('attributes->color', 'red')->get();

// Nested keys
$products = Product::where('attributes->dimensions->width', '>', 100)->get();

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

// Array contains value
$products = Product::whereJsonContains('attributes->tags', 'electronics')->get();

// Multiple array values
$products = Product::whereJsonContains('attributes->tags', ['featured', 'sale'])->get();

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

Advanced PostgreSQL Operators

// Using raw queries for PostgreSQL-specific operators
use Illuminate\Support\Facades\DB;

// Contains operator @>
$products = Product::whereRaw(
    "attributes @> ?",
    [json_encode(['brand' => 'Apple', 'category' => 'electronics'])]
)->get();

// Key exists operator ?
$products = Product::whereRaw("attributes ? 'warranty'")->get();

// Any key exists ?|
$products = Product::whereRaw(
    "attributes ?| array['color', 'size']"
)->get();

// All keys exist ?&
$products = Product::whereRaw(
    "attributes ?& array['color', 'size', 'weight']"
)->get();

Indexing Strategies

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

// GIN index with jsonb_path_ops (smaller, faster for @> queries)
DB::statement('CREATE INDEX products_attributes_path ON products USING GIN (attributes jsonb_path_ops)');

// Expression index for specific key
DB::statement("CREATE INDEX products_brand ON products ((attributes->>'brand'))");

// Partial index for filtered queries
DB::statement("CREATE INDEX products_featured ON products USING GIN (attributes) WHERE attributes @> '{\"featured\": true}'");

Building Dynamic Filters

// app/Http/Controllers/ProductController.php
class ProductController extends Controller
{
    public function index(Request $request)
    {
        $query = Product::query();

        // Dynamic JSON filters
        if ($filters = $request->get('filters')) {
            foreach ($filters as $key => $value) {
                if (is_array($value)) {
                    $query->whereJsonContains("attributes->{$key}", $value);
                } else {
                    $query->where("attributes->{$key}", $value);
                }
            }
        }

        // Price range in JSON
        if ($request->has('min_price')) {
            $query->whereRaw(
                "(attributes->>'price')::numeric >= ?",
                [$request->min_price]
            );
        }

        return $query->paginate(20);
    }
}

Updating JSON Data

// Update specific key
$product->update([
    'attributes->color' => 'blue',
]);

// Merge with existing data
$product->attributes = array_merge(
    $product->attributes ?? [],
    ['new_key' => 'new_value']
);
$product->save();

// PostgreSQL jsonb_set for atomic updates
DB::table('products')
    ->where('id', $product->id)
    ->update([
        'attributes' => DB::raw(
            "jsonb_set(attributes, '{stock}', '100'::jsonb)"
        ),
    ]);

// Remove key
DB::table('products')
    ->where('id', $product->id)
    ->update([
        'attributes' => DB::raw("attributes - 'deprecated_key'"),
    ]);

Aggregations on JSON

// Count by JSON key value
$brandCounts = DB::table('products')
    ->select(DB::raw("attributes->>'brand' as brand, COUNT(*) as count"))
    ->groupBy(DB::raw("attributes->>'brand'"))
    ->get();

// Sum values in JSON
$totalStock = DB::table('products')
    ->selectRaw("SUM((attributes->>'stock')::integer) as total")
    ->first();

// Distinct JSON values
$colors = DB::table('products')
    ->selectRaw("DISTINCT attributes->>'color' as color")
    ->whereNotNull(DB::raw("attributes->>'color'"))
    ->pluck('color');

Performance Tips

// 1. Always use JSONB, not JSON
$table->jsonb('data'); // Good
$table->json('data');  // Avoid

// 2. Index frequently queried paths
DB::statement("CREATE INDEX ON products ((attributes->>'category'))");

// 3. Use contains for complex matches (uses GIN index)
Product::whereRaw("attributes @> ?", [json_encode($criteria)])->get();

// 4. Avoid full-text search on JSON - extract to columns instead

Conclusion

PostgreSQL JSONB with Laravel provides powerful document storage within a relational database. Proper indexing and query patterns ensure excellent performance for flexible schemas.

Need database optimization? Contact ZIRA Software for PostgreSQL consulting.