Using JSON Columns in Eloquent: The Hybrid SQL/NoSQL Approach

· 4 min read
#laravel #database #json #eloquent

Traditional relational database design (Normalization) says: "If you have a list of items, create a separate table".

But sometimes you just want to store "Settings", "Metadata", or dynamic attributes without creating 10 new tables.

Enter the JSON column type.

When to Use JSON Columns

JSON columns shine in specific scenarios:

  1. User preferences: Theme, notifications, dashboard layout
  2. Product attributes: Size, color, material (varies by product type)
  3. API response caching: Store external API data temporarily
  4. Feature flags: Store A/B test configurations
  5. Metadata: Additional info that varies by record

Avoid JSON columns for:

  • Data you query frequently (use regular columns)
  • Relationships (use foreign keys)
  • Data with consistent structure across all records

The Migration

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->json('attributes'); // { "color": "red", "size": "XL" }
    $table->json('meta')->nullable();
    $table->timestamps();
});

Eloquent Casting

Tell Eloquent to automatically convert the JSON string to an Array (or Object).

protected $casts = [
    'attributes' => 'array',
    'meta' => 'object', // Use StdClass instead of array
];

Now you can use it like a native PHP array:

$product->attributes['color'] = 'blue';
$product->save();

AsArrayObject Cast (Laravel 9+)

For better mutation tracking, use AsArrayObject:

use Illuminate\Database\Eloquent\Casts\AsArrayObject;

protected $casts = [
    'attributes' => AsArrayObject::class,
];

This automatically detects nested changes:

$product->attributes['dimensions']['width'] = 100;
$product->save(); // Changes are tracked!

Querying JSON

Laravel makes this incredibly easy using the -> syntax.

// Find products where color is red
$products = Product::where('attributes->color', 'red')->get();

// Find large size
$products = Product::where('attributes->size', 'XL')->get();

// Nested keys
$users = User::where('meta->settings->notifications->email', true)->get();

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

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

Updating JSON Partial

You can update a single key without overwriting the whole JSON blob.

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

// Or use the query builder
Product::where('id', 1)
    ->update(['attributes->color' => 'green']);

Default Values

Set defaults for your JSON columns:

class Product extends Model
{
    protected $attributes = [
        'meta' => '{"views": 0, "featured": false}',
    ];

    // Or use attribute accessor
    public function getAttributesAttribute($value)
    {
        return array_merge([
            'color' => 'default',
            'size' => 'medium',
        ], json_decode($value, true) ?? []);
    }
}

Validation

Validate JSON data in your requests:

public function rules()
{
    return [
        'attributes' => 'required|array',
        'attributes.color' => 'required|string|max:50',
        'attributes.size' => 'required|in:S,M,L,XL,XXL',
        'attributes.dimensions' => 'nullable|array',
        'attributes.dimensions.width' => 'nullable|numeric|min:0',
        'attributes.dimensions.height' => 'nullable|numeric|min:0',
    ];
}

Performance Warning

Querying JSON is slower than standard columns. If you query attributes->color on every page load, extract it to a real column.

However, you can index JSON keys using "Generated Columns" in MySQL 5.7+ / MariaDB.

// Migration
$table->string('color')
    ->virtualAs('JSON_UNQUOTE(JSON_EXTRACT(attributes, "$.color"))')
    ->index();

// Now this is fast!
Product::where('color', 'red')->get();

For PostgreSQL, use GIN indexes:

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

JSON in Factories

Generate realistic JSON data in your factories:

class ProductFactory extends Factory
{
    public function definition()
    {
        return [
            'name' => $this->faker->productName(),
            'attributes' => [
                'color' => $this->faker->randomElement(['red', 'blue', 'green']),
                'size' => $this->faker->randomElement(['S', 'M', 'L', 'XL']),
                'weight' => $this->faker->randomFloat(2, 0.1, 10),
            ],
            'meta' => [
                'views' => $this->faker->numberBetween(0, 1000),
                'featured' => $this->faker->boolean(20),
            ],
        ];
    }
}

Best Practices

  1. Document your JSON structure: Use PHPDoc or separate schema files
  2. Validate thoroughly: Never trust user input for JSON fields
  3. Consider DTOs: Cast to custom classes for complex JSON structures
  4. Monitor query performance: Watch for slow queries on JSON columns
  5. Set reasonable size limits: JSON columns can grow unexpectedly

JSON columns give you the flexibility of NoSQL while keeping the reliability of your SQL database. Use them wisely, and they'll save you from schema bloat without sacrificing query capabilities.

Comments