It is efficient and intuitive to use JSON columns to store flexible data in Laravel and MySQL: 1. Use the json() method to define JSON fields during migration, such as $table->json('settings'); 2. Map fields to JSON through the $casts attribute in the model, and directly access the array data and update nested values using the -> syntax; 3. Use where('settings->theme', 'dark'), whereJsonContains and whereJsonLength methods to query JSON content; 4. Access is like an object or array attribute, such as $user->settings->theme; 5. Note that you always set JSON type conversion in the model, use nullable reasonably, avoid storing large or relational data, and index JSON fields by generating columns to improve performance. With correct use, JSON columns are very suitable for storing semi-structured data such as user settings and preferences, and Laravel makes it natural and easy to operate.

Using JSON columns in MySQL with Laravel is straightforward and powerful, especially when you need to store flexible or semi-structured data. Laravel provides excellent support for working with JSON fields in MySQL, allowing you to query and manipulate them easily.

Here's how to use JSON columns effectively in Laravel with MySQL:

1. Define JSON Columns in Migrations

When creating or updating a table, use the json() method in your migration to define a JSON column.

 Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->json('settings'); // JSON column
    $table->timestamps();
});

You can also add a JSON column to an existing table:

 Schema::table('users', function (Blueprint $table) {
    $table->json('preferences')->nullable();
});

? MySQL stores JSON natively and validates the format on insertion.

2. Insert and Update JSON Data

Laravel automatically casts arrays to JSON when saving to a JSON column (if the attribute is cast correctly in the model).

In your model, define a cast:

 // app/Models/User.php
protected $casts = [
    'settings' => 'json',
];

Now you can assign an array, and Laravel will store it as JSON:

 $user = User::create([
    'name' => 'John',
    'settings' => [
        'theme' => 'dark',
        'notifications' => ['email' => true, 'sms' => false]
    ]
]);

To update part of the JSON, use the -> operator:

 $user->update([
    'settings->theme' => 'light',
    'settings->language' => 'en'
]);

This updates only the specified keys inside the JSON.

3. Query JSON Columns

MySQL allows querying JSON fields using the -> operator. Laravel lets you use this in Eloquent queries.

Examples:

• Find users with a specific JSON value:

   $users = User::where('settings->theme', 'dark')->get();

• Check if a JSON key exists:

   $users = User::whereJsonContains('settings', ['theme' => 'dark'])->get();

• Search in JSON arrays:

   // If 'roles' is a JSON array
  $users = User::whereJsonContains('roles', 'admin')->get();

• Use whereJsonLength to check array/object length:

   // Users with 2 or more roles
  $users = User::whereJsonLength('roles', '>=', 2)->get();

4. Access JSON Attributes in Models

With the $casts defined, you can access JSON values like object properties:

 echo $user->settings->theme; // 'dark'
echo $user->settings['notifications']['email']; // true

Or use array access if preferred:

 $settings = $user->settings;
echo $settings['theme'];

? Laravel returns JSON as a Collection if cast to json , so you can use methods like toArray() .

5. Common Pitfalls & Tips

• Always cast JSON fields in the model to ensure proper encoding/decoding.
• Use nullable() if the JSON column can be null.
• Avoid storing large or highly relational data in JSON — it's best for user preferences, settings, metadata, etc.
• Indexing: MySQL supports functional indexes on JSON fields, but they're more complex. For high-performance queries, consider duplicating critical values in regular columns.

Example of a generated column for indexing:

 // In migration
DB::statement("ALTER TABLE users ADD COLUMN theme VARCHAR(20) AS (settings->>'$.theme')");
DB::statement("CREATE INDEX idx_users_theme ON users (theme)");

Summary

Using JSON columns in Laravel with MySQL is clean and efficient for flexible data:

• ? Use json() in migrations
• ? Cast attributes in the model
• ? Use -> syntax to query and update nested values
• ? Leverage Laravel's JSON query methods ( whereJsonContains , whereJsonLength )

It's a great fit for settings, preferences, or dynamic metadata — just don't overuse it for structured data that belongs in proper tables.

Basically, Laravel makes JSON columns feel natural and easy to work with.

The above is the detailed content of How to use JSON columns in MySQL with Laravel?. For more information, please follow other related articles on the PHP Chinese website!