Add Index on JSON Field #1230

AzSiAz · 2020-12-22T15:23:06Z

Is your feature request related to a problem? Please describe.
You can't add an index on json field like you would do in sql

CREATE INDEX "test_on_json_index" ON test((data->>'somefield'), "userId");

Describe the solution you'd like
A small update to @Index() to be able to do something like this

import { BaseEntity, Entity, Index, JsonType, PrimaryKey, Property } from '@mikro-orm/core'

@Index({ properties: ["data->>'somefield'", 'userId'] })
@Entity()
export class Test extends BaseEntity<Test, 'id'> {
    @PrimaryKey({ columnType: 'uuid', defaultRaw: 'uuid_generate_v4()' })
    id!: string

    @Property({ columnType: 'timestamptz', defaultRaw: 'CURRENT_TIMESTAMP' })
    createdAt = new Date()

    @Property({ columnType: 'timestamptz', defaultRaw: 'CURRENT_TIMESTAMP', onUpdate: () => new Date() })
    updatedAt = new Date()

    // Or here
    @Index("data->>'somefield'")
    @Property({ type: JsonType })
    data: PossibleData

    @Property()
    @Index()
    userId: UserId
}

Or something like this

import { BaseEntity, Entity, Index, JsonType, PrimaryKey, Property } from '@mikro-orm/core'

@Index({ properties: [{data: { somefield: true }}, 'userId'] })
@Entity()
export class Test extends BaseEntity<Test, 'id'> {
    @PrimaryKey({ columnType: 'uuid', defaultRaw: 'uuid_generate_v4()' })
    id!: string

    @Property({ columnType: 'timestamptz', defaultRaw: 'CURRENT_TIMESTAMP' })
    createdAt = new Date()

    @Property({ columnType: 'timestamptz', defaultRaw: 'CURRENT_TIMESTAMP', onUpdate: () => new Date() })
    updatedAt = new Date()

     // Or here
    @Index({ properties: {data: { somefield: true }} })
    @Property({ type: JsonType })
    data: PossibleData

    @Property()
    @Index()
    userId: UserId
}

Which when you try to create a migration return
MetadataError: Entity Test has wrong index definition: 'data->>'somefield'' does not exist. You need to use property name, not column name.

Describe alternatives you've considered
Manual migration but mikro-orm try to delete my index with every new migration created, but maybe I missed something

The text was updated successfully, but these errors were encountered:

B4nan · 2021-01-15T12:32:34Z

Manual migration but mikro-orm try to delete my index with every new migration created, but maybe I missed something

A workaround is to define any index with some name, index diffing works just based on index names, so it is enough to have some index with the name and it will be kept intact.

B4nan · 2021-11-28T13:03:48Z

In v5 you can use custom index expressions:

@Index({ name: 'test_on_json_index', expression: `CREATE INDEX "test_on_json_index" ON test((data->>'somefield'), "userId");` })

This works both on entity level and property level and you have absolute control over the index definition.

The diffing works again based on index name (that can be either explicitly defined or inferred from the property name). The previous workaround probably won't work anymore as indexes are now properly diffed not just based on name, but also based on fields and their order.

I still see a room for the proposed solution, probably with dot notation instead of objects or platform specific syntax, like in #2129:

@Index({ properties: ['data.somefield', 'userId'] })
@Entity()
export class Test extends BaseEntity<Test, 'id'> {

    @Index({ properties: 'data.somefield' }) // can be a string too
    @Property({ type: JsonType })
    data: PossibleData

}

AzSiAz · 2021-11-28T16:10:20Z

Didn't know about the upcoming v5 change, thanks for the heads up

The idea with object syntax was to provide autocompletion and more importantly TS type error if something change in the future

B4nan · 2021-11-28T16:12:18Z

The idea with object syntax was to provide autocompletion and more importantly TS type error if something change in the future

v5 supports type safe dot notation paths, but in general you can't have anything type safe inside decorators unless you explicitly provide the generic argument (it can't be inferred from the class where the decorator is used)

AzSiAz · 2021-11-28T16:32:17Z

Yeah, I was speaking more with using something like

@Index({ properties: { data: { field: true | 'asc' | 'desc' } } } as IndexType<JSONType>)

But if dot notation in paths is typesafe I'm all in 💯

My only downside from using mikro-orm v4 despite all the amazing work you and contributor put in is type safe usage compared to prisma (still lacking micro-orm flexibility)

SteveMelons · 2022-03-07T12:57:24Z

What would be the current workaround in v5 for nested Index using the mongo driver?

SteveMelons · 2022-03-08T17:48:24Z

What would be the current workaround in v5 for nested Index using the mongo driver?

Found the solution which does not allow for extra options to be passed as far as I understand though

@Index({ options: { "address.location": "2dsphere" } })

SevenWaysDP · 2022-03-16T07:48:55Z

What would be the current workaround in v5 for nested Index using the mongo driver?

Found the solution which does not allow for extra options to be passed as far as I understand though
@Index({ options: { "address.location": "2dsphere" } })

It works, on entity level! Thanks!

To create an index on a JSON property, use an entity-level `@Index()` decorator with a dot path: ```ts @entity() @Index({ properties: 'metaData.foo' }) @Index({ properties: ['metaData.foo', 'metaData.bar'] }) // compound index export class Book { @Property({ type: 'json', nullable: true }) metaData?: { foo: string; bar: number }; } ``` In PostgreSQL, this will generate a query like the following: ```sql create index "book_meta_data_foo_index" on "book" (("meta_data"->>'foo')); ``` To create a unique index, use the `@Unique()` decorator: ```ts @entity() @unique({ properties: 'metaData.foo' }) @unique({ properties: ['metaData.foo', 'metaData.bar'] }) // compound unique index export class Book { @Property({ type: 'json', nullable: true }) metaData?: { foo: string; bar: number }; } ``` In MySQL, you can also set the type explicitly: ```ts @entity() @Index({ properties: 'metaData.foo', options: { returning: 'char(200)' } }) export class Book { @Property({ type: 'json', nullable: true }) metaData?: { foo: string; bar: number }; } ``` This will generate a query like the following: ```sql alter table `book` add index `book_meta_data_foo_index`((json_value(`meta_data`, '$.foo' returning char(200)))); ``` > MariaDB driver does not support this feature. Closes #1230

B4nan · 2023-09-23T16:17:10Z

Closing as implemented in v6 via #4735

To create an index on a JSON property, use an entity-level `@Index()` decorator with a dot path: ```ts @entity() @Index({ properties: 'metaData.foo' }) @Index({ properties: ['metaData.foo', 'metaData.bar'] }) // compound index export class Book { @Property({ type: 'json', nullable: true }) metaData?: { foo: string; bar: number }; } ``` In PostgreSQL, this will generate a query like the following: ```sql create index "book_meta_data_foo_index" on "book" (("meta_data"->>'foo')); ``` To create a unique index, use the `@Unique()` decorator: ```ts @entity() @unique({ properties: 'metaData.foo' }) @unique({ properties: ['metaData.foo', 'metaData.bar'] }) // compound unique index export class Book { @Property({ type: 'json', nullable: true }) metaData?: { foo: string; bar: number }; } ``` In MySQL, you can also set the type explicitly: ```ts @entity() @Index({ properties: 'metaData.foo', options: { returning: 'char(200)' } }) export class Book { @Property({ type: 'json', nullable: true }) metaData?: { foo: string; bar: number }; } ``` This will generate a query like the following: ```sql alter table `book` add index `book_meta_data_foo_index`((json_value(`meta_data`, '$.foo' returning char(200)))); ``` > MariaDB driver does not support this feature. Closes #1230

AzSiAz added the enhancement New feature or request label Dec 22, 2020

B4nan added this to the 6.0 milestone Dec 28, 2022

B4nan mentioned this issue Sep 23, 2023

feat(core): add support for indexes on JSON properties #4735

Merged

B4nan closed this as completed Sep 23, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add Index on JSON Field #1230

Add Index on JSON Field #1230

AzSiAz commented Dec 22, 2020 •

edited

Loading

B4nan commented Jan 15, 2021

B4nan commented Nov 28, 2021 •

edited

Loading

AzSiAz commented Nov 28, 2021

B4nan commented Nov 28, 2021

AzSiAz commented Nov 28, 2021 •

edited

Loading

SteveMelons commented Mar 7, 2022

SteveMelons commented Mar 8, 2022

SevenWaysDP commented Mar 16, 2022

B4nan commented Sep 23, 2023

Add Index on JSON Field #1230

Add Index on JSON Field #1230

Comments

AzSiAz commented Dec 22, 2020 • edited Loading

B4nan commented Jan 15, 2021

B4nan commented Nov 28, 2021 • edited Loading

AzSiAz commented Nov 28, 2021

B4nan commented Nov 28, 2021

AzSiAz commented Nov 28, 2021 • edited Loading

SteveMelons commented Mar 7, 2022

SteveMelons commented Mar 8, 2022

SevenWaysDP commented Mar 16, 2022

B4nan commented Sep 23, 2023

AzSiAz commented Dec 22, 2020 •

edited

Loading

B4nan commented Nov 28, 2021 •

edited

Loading

AzSiAz commented Nov 28, 2021 •

edited

Loading