使用 JSON 属性

定义 JSON 属性

¥Defining JSON properties

每个数据库驱动程序在处理 JSON 属性时的行为略有不同：有些快照会自动解析值，而另一些则会将其作为 JSON 字符串返回。MikroORM 尝试通过 JsonType 统一体验。如果我们指定 type: 'json'，也将使用此类型。

¥Each database driver behaves a bit differently when it comes to JSON properties: Some will parse the values for you automatically, while others will return them as JSON strings. MikroORM tries to unify the experience via JsonType. This type will be also used if we specify type: 'json'.

@Entity()
export class Book {

  @Property({ type: 'json', nullable: true })
  meta?: { foo: string; bar: number };

}

通过 JSON 对象属性查询

¥Querying by JSON object properties

v4.4.2 中添加了对通过 JSON 对象属性进行查询的支持

¥Support for querying by JSON object properties was added in v4.4.2

我们可以轻松地通过 JSON 对象属性进行查询：

¥We can query by JSON object properties easily:

const b = await em.findOne(Book, {
  meta: {
    valid: true,
    nested: {
      foo: '123',
      bar: 321,
      deep: {
        baz: 59,
        qux: false,
      },
    },
  },
});

将生成以下查询（在 postgres 中）：

¥Will produce following query (in postgres):

select "e0".*
from "book" as "e0"
where ("meta"->>'valid')::bool = true
  and "meta"->'nested'->>'foo' = '123'
  and ("meta"->'nested'->>'bar')::float8 = 321
  and ("meta"->'nested'->'deep'->>'baz')::float8 = 59
  and ("meta"->'nested'->'deep'->>'qux')::bool = false
limit 1

目前支持所有驱动程序（包括 sqlite 和 mongo）。在 postgres 中，如果我们在右侧检测到数字或布尔值，我们也会尝试转换值。

¥All drivers are currently supported (including sqlite and mongo). In postgres we also try to cast the value if we detect number or boolean on the right-hand side.

JSON 属性上的索引

¥Indexes on JSON properties

要在 JSON 属性上创建索引，请使用带有点路径的实体级 @Index() 装饰器：

¥To create an index on a JSON property, use an entity-level @Index() decorator with a dot path:

@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 };

}

在 PostgreSQL 中，这将生成如下查询：

¥In PostgreSQL, this will generate a query like the following:

create index "book_meta_data_foo_index" on "book" (("meta_data"->>'foo'));

要创建唯一索引，请使用 @Unique() 装饰器：

¥To create a unique index, use the @Unique() decorator:

@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 };

}

在 MySQL 中，你还可以明确设置类型：

¥In MySQL, you can also set the type explicitly:

@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:

alter table `book`
  add index `book_meta_data_foo_index`((json_value(`meta_data`, '$.foo' returning char(200))));

MariaDB 驱动程序不支持此功能。

¥MariaDB driver does not support this feature.