Creating an index on a json column with Laravel

Question

I have a db table with a JSON column. I now want to add an index to parts of that json.

It turns out you can only add an index on a json column when creating the table.

Here's what I tried in a migration:

DB::statement(DB::raw(<<<SQL
CREATE TABLE area_groups (
  title JSON, 
  `created_at` timestamp null, 
  `updated_at` timestamp null, 
  INDEX area_groups_title_de (
    (
      JSON_VALUE(title, '$.de')
    )
  ), 
  INDEX area_groups_title_en (
    (
      JSON_VALUE(title, '$.en')
    )
  )
) default character set utf8mb4 collate 'utf8mb4_unicode_ci'
SQL
));

Schema::table('area_groups', function (Blueprint $table) {
    $table->id()->change();
    $table->foreignId('area_id')->change()->constrained();
});

My idea was to create the json column and indexes in a raw db statement and do the rest with Laravel's migration helpers.

Creating the table seems to work, but running this migration fails with the following error message:

Argument 1 passed to Doctrine\DBAL\Schema\Index::_addColumn() must be of the type string, null given, called in vendor/doctrine/dbal/src/Schema/Index.php on line 72

Where exactly does it say that this works only when creating a table? — Alex
– Alex, Commented Nov 15, 2021 at 10:30
@Alex Doing something like create index area_groups_title_de on area_groups(JSON_VALUE(title, '$.de')); gives me a mysql syntax error. — kolaente
– kolaente, Commented Nov 15, 2021 at 10:31
And the mysql docs on json indexes only mention create table — kolaente
– kolaente, Commented Nov 15, 2021 at 10:31
Are you sure you're using a mysql version that supports what you're trying to do? Also, I don't think you need DB::raw inside DB::statement. — IGP
– IGP, Commented Nov 15, 2021 at 12:56
@IGP yes, when I run that create table statement manually on a console it works. — kolaente
– kolaente, Commented Nov 16, 2021 at 7:21

Masudul Hasan Shawon · Accepted Answer · 2021-11-15 11:37:04Z

-2

you can use json_encode(['id' => 1, 'name' => 'User 1']);

Laravel Schema does support JSON field types as well.

You can use text field type as well to store JSON data.

answered Nov 15, 2021 at 11:37

Masudul Hasan Shawon

5223 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kolaente Over a year ago

I'm looking for a way to add a mysql index to a column in my json column, not for a way to encode json.

Collectives™ on Stack Overflow

Creating an index on a json column with Laravel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related