How can I set index for json column in mysql 8 with laravel migration

user_8878756 0
Viewed 630

I'm creating a project with laravel 6. One of my table column type is json. The data format in the table column is like this:{age:30, gender:male, nation:china,...}. I am wondering if there is a way for me to set index for this column with laravel migration. my database version is mysql 8.0.21. thank you!

laravel indexing migration
1 Answers

I found this article very helpful for figuring this out. So for your example structure above, you might have a migration that looks like the following:

public function up(){
    Schema::create('my_table', function(Blueprint $table){
        $table->bigIncrements('id');
        $table->json('my_json_col')->nullable();
        $table->timestamps();

        // add stored columns with an index 
        // index in this is optional, but recommended if you will be filtering/sorting on these columns
        $table->unsignedInteger('age')->storedAs('JSON_UNQUOTE(my_json_col->>"$.age")')->index();
        $table->string('gender')->storedAs('JSON_UNQUOTE(my_json_col->>"$.gender")')->index();
        $table->string('nation')->storedAs('JSON_UNQUOTE(my_json_col->>"$.nation")')->index();
    });
}

And this is equivalent to the following mysql statement:

create table my_table
(
    id          bigint unsigned auto_increment primary key,
    my_json_col json      null,
    created_at  timestamp null,
    updated_at  timestamp null,
    age         int unsigned as (json_unquote(json_unquote(json_extract(`my_json_col`, _utf8mb4'$.age')))) stored,
    gender      varchar(255) as (json_unquote(json_unquote(json_extract(`my_json_col`, _utf8mb4'$.gender')))) stored,
    nation      varchar(255) as (json_unquote(json_unquote(json_extract(`my_json_col`, _utf8mb4'$.nation')))) stored
)
    collate = utf8mb4_unicode_ci;

create index my_table_age_index
    on my_table (age);

create index my_table_gender_index
    on my_table (gender);

create index my_table_nation_index
    on my_table (nation);

And a simple view of the table after creation:

enter image description here

This example created actual stored columns, which for this scenario i think is what you would want. But you can also make virtual columns, which are created at query time instead of persistent columns, and you would just use the virtualAs function instead of the storedAs function in the migration.

These functions are documented in the Column Modifiers section of the Laravel migration docs, but it doesn't go into detail on JSON columns, this requires a bit more mysql knowledge.
I also found this article helpful for the mysql side of things for the JSON columns (SemiSQL).

user_34357150
Related