Skip to main content

使用 Json 字段

使用 Json Prisma ORM 字段类型对底层数据库中的 JSON 类型进行读取、写入和执行基本过滤。在以下示例中，User 模型有一个名为 extendedPetsData 的可选 Json 字段：

¥Use the Json Prisma ORM field type to read, write, and perform basic filtering on JSON types in the underlying database. In the following example, the User model has an optional Json field named extendedPetsData:

model User {
  id               Int     @id @default(autoincrement())
  email            String  @unique
  name             String?
  posts            Post[]
  extendedPetsData Json?
}

字段值示例：

¥Example field value:

{
  "pet1": {
    "petName": "Claudine",
    "petType": "House cat"
  },
  "pet2": {
    "petName": "Sunny",
    "petType": "Gerbil"
  }
}

Json 字段支持一些附加类型，例如 string 和 boolean。这些附加类型的存在是为了匹配 JSON.parse() 支持的类型：

¥The Json field supports a few additional types, such as string and boolean. These additional types exist to match the types supported by JSON.parse():

export type JsonValue =
  | string
  | number
  | boolean
  | null
  | JsonObject
  | JsonArray

JSON 字段的用例​

¥Use cases for JSON fields

将数据存储为 JSON 而不是将数据表示为相关模型的原因包括：

¥Reasons to store data as JSON rather than representing data as related models include:

  • 你需要存储不具有一致结构的数据

    ¥You need to store data that does not have a consistent structure

  • 你正在从另一个系统导入数据，并且不想将该数据映射到 Prisma 模型

    ¥You are importing data from another system and do not want to map that data to Prisma models

读取 Json 字段​

¥Reading a Json field

你可以使用 Prisma.JsonArray 和 Prisma.JsonObject 实用程序类来处理 Json 字段的内容：

¥You can use the Prisma.JsonArray and Prisma.JsonObject utility classes to work with the contents of a Json field:

const { PrismaClient, Prisma } = require('@prisma/client')

const user = await prisma.user.findFirst({
  where: {
    id: 9,
  },
})

// Example extendedPetsData data:
// [{ name: 'Bob the dog' }, { name: 'Claudine the cat' }]

if (
  user?.extendedPetsData &&
  typeof user?.extendedPetsData === 'object' &&
  Array.isArray(user?.extendedPetsData)
) {
  const petsObject = user?.extendedPetsData as Prisma.JsonArray

  const firstPet = petsObject[0]
}

也可以看看：高级示例：更新嵌套 JSON 键值

¥See also: Advanced example: Update a nested JSON key value

写入 Json 字段​

¥Writing to a Json field

以下示例将 JSON 对象写入 extendedPetsData 字段：

¥The following example writes a JSON object to the extendedPetsData field:

var json = [
  { name: 'Bob the dog' },
  { name: 'Claudine the cat' },
] as Prisma.JsonArray

const createUser = await prisma.user.create({
  data: {
    email: 'birgitte@prisma.io',
    extendedPetsData: json,
  },
})

注意：JavaScript 对象（例如，{ extendedPetsData: "none"}）会自动转换为 JSON。

¥Note: JavaScript objects (for example, { extendedPetsData: "none"}) are automatically converted to JSON.

也可以看看：高级示例：更新嵌套 JSON 键值

¥See also: Advanced example: Update a nested JSON key value

筛选 Json 字段（简单）​

¥Filter on a Json field (simple)

你可以过滤 Json 类型的行。

¥You can filter rows of Json type.

根据确切的字段值进行过滤​

¥Filter on exact field value

以下查询返回 extendedPetsData 的值与 json 变量完全匹配的所有用户：

¥The following query returns all users where the value of extendedPetsData matches the json variable exactly:

var json = { [{ name: 'Bob the dog' }, { name: 'Claudine the cat' }] }

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      equals: json,
    },
  },
})

以下查询返回 extendedPetsData 的值与 json 变量不完全匹配的所有用户：

¥The following query returns all users where the value of extendedPetsData does not match the json variable exactly:

var json = {
  extendedPetsData: [{ name: 'Bob the dog' }, { name: 'Claudine the cat' }],
}

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      not: json,
    },
  },
})

筛选 Json 字段（高级）​

¥Filter on a Json field (advanced)

你还可以按 Json 字段内的数据过滤行。我们称之为高级 Json 过滤。仅 PostgreSQL 和 MySQL 以及 path 选项的不同语法 支持此功能。

¥You can also filter rows by the data inside a Json field. We call this advanced Json filtering. This functionality is supported by PostgreSQL and MySQL only with different syntaxes for the path option.

warning

PostgreSQL 不支持 过滤数组中的对象键值。

¥PostgreSQL does not support filtering on object key values in arrays.

info

高级 Json 过滤的可用性取决于你的 Prisma 版本：

¥The availability of advanced Json filtering depends on your Prisma version:

path 语法取决于数据库​

¥path syntax depending on database

下面的过滤器使用 path 选项来选择要过滤的 Json 值的特定部分。连接器之间的过滤实现有所不同：

¥The filters below use a path option to select specific parts of the Json value to filter on. The implementation of that filtering differs between connectors:

例如，以下是有效的 MySQL path 值：

¥For example, the following is a valid MySQL path value:

$petFeatures.petName

以下是有效的 PostgreSQL path 值：

¥The following is a valid PostgreSQL path value:

["petFeatures", "petName"]

对对象属性进行过滤​

¥Filter on object property

你可以过滤 JSON 块内的特定属性。在以下示例中，extendedPetsData 的值是一维、未嵌套的 JSON 对象：

¥You can filter on a specific property inside a block of JSON. In the following examples, the value of extendedPetsData is a one-dimensional, unnested JSON object:

{
  "petName": "Claudine",
  "petType": "House cat"
}

以下查询返回 petName 值为 "Claudine" 的所有用户：

¥The following query returns all users where the value of petName is "Claudine":

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: ['petName'],
      equals: 'Claudine',
    },
  },
})

以下查询返回 petType 的值包含 "cat" 的所有用户：

¥The following query returns all users where the value of petType contains "cat":

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: ['petType'],
      string_contains: 'cat',
    },
  },
})

以下字符串过滤器可用：

¥The following string filters are available:

要使用不区分大小写的过滤器，你可以使用 mode 选项：

¥To use case insensitive filter with these, you can use the mode option:

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: ['petType'],
      string_contains: 'cat',
      mode: 'insensitive'
    },
  },
})

过滤嵌套对象属性​

¥Filter on nested object property

你可以过滤嵌套的 JSON 属性。在以下示例中，extendedPetsData 的值是一个具有多层嵌套的 JSON 对象。

¥You can filter on nested JSON properties. In the following examples, the value of extendedPetsData is a JSON object with several levels of nesting.

{
  "pet1": {
    "petName": "Claudine",
    "petType": "House cat"
  },
  "pet2": {
    "petName": "Sunny",
    "petType": "Gerbil",
    "features": {
      "eyeColor": "Brown",
      "furColor": "White and black"
    }
  }
}

以下查询返回 "pet2" → "petName" 为 "Sunny" 的所有用户：

¥The following query returns all users where "pet2" → "petName" is "Sunny":

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: ['pet2', 'petName'],
      equals: 'Sunny',
    },
  },
})

以下查询返回所有用户，其中：

¥The following query returns all users where:

  • "pet2" → "petName" 是 "Sunny"

    ¥"pet2" → "petName" is "Sunny"

  • "pet2" → "features" → "furColor" 包含 "black"

    ¥"pet2" → "features" → "furColor" contains "black"

const getUsers = await prisma.user.findMany({
  where: {
    AND: [
      {
        extendedPetsData: {
          path: ['pet2', 'petName'],
          equals: 'Sunny',
        },
      },
      {
        extendedPetsData: {
          path: ['pet2', 'features', 'furColor'],
          string_contains: 'black',
        },
      },
    ],
  },
})

对数组值进行过滤​

¥Filtering on an array value

你可以过滤标量数组（字符串、整数）中是否存在特定值。在以下示例中，extendedPetsData 的值是一个字符串数组：

¥You can filter on the presence of a specific value in a scalar array (strings, integers). In the following example, the value of extendedPetsData is an array of strings:

["Claudine", "Sunny"]

以下查询返回拥有名为 "Claudine" 的宠物的所有用户：

¥The following query returns all users with a pet named "Claudine":

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      array_contains: ['Claudine'],
    },
  },
})
info

Note: In PostgreSQL, the value of array_contains must be an array and not a string, even if the array only contains a single value.

可以使用以下数组过滤器：

¥The following array filters are available:

过滤嵌套数组值​

¥Filtering on nested array value

你可以过滤标量数组（字符串、整数）中是否存在特定值。在以下示例中，extendedPetsData 的值包括嵌套的名称标量数组：

¥You can filter on the presence of a specific value in a scalar array (strings, integers). In the following examples, the value of extendedPetsData includes nested scalar arrays of names:

{
  "cats": { "owned": ["Bob", "Sunny"], "fostering": ["Fido"] },
  "dogs": { "owned": ["Ella"], "fostering": ["Prince", "Empress"] }
}

标量值数组​

¥Scalar value arrays

以下查询返回养育名为 "Fido" 的猫的所有用户：

¥The following query returns all users that foster a cat named "Fido":

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: ['cats', 'fostering'],
      array_contains: ['Fido'],
    },
  },
})
info

Note: In PostgreSQL, the value of array_contains must be an array and not a string, even if the array only contains a single value.

以下查询返回饲养名为 "Fido" 和 "Bob" 的猫的所有用户：

¥The following query returns all users that foster cats named "Fido" and "Bob":

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: ['cats', 'fostering'],
      array_contains: ['Fido', 'Bob'],
    },
  },
})

JSON 对象数组​

¥JSON object arrays

const json = [{ status: 'expired', insuranceID: 92 }]

const checkJson = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: ['insurances'],
      array_contains: json,
    },
  },
})

  • 如果你使用 PostgreSQL，则必须传入一组要匹配的对象，即使该数组仅包含一个对象：

    ¥If you are using PostgreSQL, you must pass in an array of objects to match, even if that array only contains one object:

    [{ status: 'expired', insuranceID: 92 }]
    // PostgreSQL

    如果你使用 MySQL，则必须传入单个对象来匹配：

    ¥If you are using MySQL, you must pass in a single object to match:

    { status: 'expired', insuranceID: 92 }
    // MySQL

  • 如果你的过滤器数组包含多个对象，PostgreSQL 将仅在所有对象都存在时返回结果 - 如果至少存在一个对象则不会。

    ¥If your filter array contains multiple objects, PostgreSQL will only return results if all objects are present - not if at least one object is present.

  • 你必须将 array_contains 设置为 JSON 对象，而不是字符串。如果你使用字符串，Prisma Client 会转义引号，并且查询将不会返回结果。例如：

    ¥You must set array_contains to a JSON object, not a string. If you use a string, Prisma Client escapes the quotation marks and the query will not return results. For example:

    array_contains: '[{"status": "expired", "insuranceID": 92}]'

    被发送到数据库：

    ¥is sent to the database as:

    [{\"status\": \"expired\", \"insuranceID\": 92}]

通过索引定位数组元素​

¥Targeting an array element by index

你可以过滤特定位置的元素值。

¥You can filter on the value of an element in a specific position.

{ "owned": ["Bob", "Sunny"], "fostering": ["Fido"] }
const getUsers = await prisma.user.findMany({
  where: {
    comments: {
      path: ['owned', '1'],
      string_contains: 'Bob',
    },
  },
})

过滤数组内的对象键值​

¥Filtering on object key value inside array

根据你的提供程序，你可以过滤数组内对象的键值。

¥Depending on your provider, you can filter on the key value of an object inside an array.

warning

仅 MySQL 数据库连接器 支持对数组中的对象键值进行过滤。不过，你仍然可以 过滤整个 JSON 对象的存在。

¥Filtering on object key values within an array is only supported by the MySQL database connector. However, you can still filter on the presence of entire JSON objects.

在以下示例中，extendedPetsData 的值是一个带有嵌套 insurances 数组的对象数组，该数组包含两个对象：

¥In the following example, the value of extendedPetsData is an array of objects with a nested insurances array, which contains two objects:

[
  {
    "petName": "Claudine",
    "petType": "House cat",
    "insurances": [
      { "insuranceID": 92, "status": "expired" },
      { "insuranceID": 12, "status": "active" }
    ]
  },
  {
    "petName": "Sunny",
    "petType": "Gerbil"
  },
  {
    "petName": "Gerald",
    "petType": "Corn snake"
  },
  {
    "petName": "Nanna",
    "petType": "Moose"
  }
]

以下查询返回至少一只宠物是驼鹿的所有用户：

¥The following query returns all users where at least one pet is a moose:

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: '$[*].petType',
      array_contains: 'Moose',
    },
  },
})

  • $[*] 是 pet 对象的根数组

    ¥$[*] is the root array of pet objects

  • petType 与任何宠物对象中的 petType 键匹配

    ¥petType matches the petType key in any pet object

以下查询返回至少一只宠物的保险已过期的所有用户：

¥The following query returns all users where at least one pet has an expired insurance:

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: '$[*].insurances[*].status',
      array_contains: 'expired',
    },
  },
})

  • $[*] 是 pet 对象的根数组

    ¥$[*] is the root array of pet objects

  • insurances[*] 匹配任何宠物对象内的任何 insurances 数组

    ¥insurances[*] matches any insurances array inside any pet object

  • status 匹配任何保险对象中的任何 status 键

    ¥status matches any status key in any insurance object

高级示例：更新嵌套 JSON 键值​

¥Advanced example: Update a nested JSON key value

以下示例假设 extendedPetsData 的值是以下各项的某种变体：

¥The following example assumes that the value of extendedPetsData is some variation of the following:

{
  "petName": "Claudine",
  "petType": "House cat",
  "insurances": [
    { "insuranceID": 92, "status": "expired" },
    { "insuranceID": 12, "status": "active" }
  ]
}

下面的例子：

¥The following example:

  1. 获取所有用户

    ¥Gets all users

  2. 将每个保险对象的 "status" 更改为 "expired"

    ¥Change the "status" of each insurance object to "expired"

  3. 获取所有保险已过期且 ID 为 92 的用户

    ¥Get all users that have an expired insurance where the ID is 92

const userQueries: string | any[] = []

getUsers.forEach((user) => {
  if (
    user.extendedPetsData &&
    typeof user.extendedPetsData === 'object' &&
    !Array.isArray(user.extendedPetsData)
  ) {
    const petsObject = user.extendedPetsData as Prisma.JsonObject

    const i = petsObject['insurances']

    if (i && typeof i === 'object' && Array.isArray(i)) {
      const insurancesArray = i as Prisma.JsonArray

      insurancesArray.forEach((i) => {
        if (i && typeof i === 'object' && !Array.isArray(i)) {
          const insuranceObject = i as Prisma.JsonObject

          insuranceObject['status'] = 'expired'
        }
      })

      const whereClause = Prisma.validator<Prisma.UserWhereInput>()({
        id: user.id,
      })

      const dataClause = Prisma.validator<Prisma.UserUpdateInput>()({
        extendedPetsData: petsObject,
      })

      userQueries.push(
        prisma.user.update({
          where: whereClause,
          data: dataClause,
        })
      )
    }
  }
})

if (userQueries.length > 0) {
  console.log(userQueries.length + ' queries to run!')
  await prisma.$transaction(userQueries)
}

const json = [{ status: 'expired', insuranceID: 92 }]

const checkJson = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: ['insurances'],
      array_contains: json,
    },
  },
})

console.log(checkJson.length)

使用 null 值​

¥Using null Values

SQL 数据库中的 JSON 字段可能有两种类型的 null 值。

¥There are two types of null values possible for a JSON field in an SQL database.

  • 数据库 NULL：数据库中的值为 NULL。

    ¥Database NULL: The value in the database is a NULL.

  • JSON null：数据库中的值包含 JSON 值 null。

    ¥JSON null: The value in the database contains a JSON value that is null.

为了区分这些可能性，我们引入了三个你可以使用的空枚举：

¥To differentiate between these possibilities, we've introduced three null enums you can use:

  • JsonNull：表示 JSON 中的 null 值。

    ¥JsonNull: Represents the null value in JSON.

  • DbNull：代表数据库中的 NULL 值。

    ¥DbNull: Represents the NULL value in the database.

  • AnyNull：表示 null JSON 值和 NULL 数据库值。（仅当过滤时）

    ¥AnyNull: Represents both null JSON values and NULL database values. (Only when filtering)

info

从 v4.0.0 开始，JsonNull、DbNull 和 AnyNull 是对象。在 v4.0.0 之前，它们是字符串。

¥From v4.0.0, JsonNull, DbNull, and AnyNull are objects. Before v4.0.0, they were strings.

info

  • 使用任何空枚举进行过滤时，你不能使用简写并关闭 equals 运算符。

    ¥When filtering using any of the null enums you can not use a shorthand and leave the equals operator off.

  • 这些空枚举不适用于 MongoDB，因为 JSON null 和数据库 NULL 之间不存在差异。

    ¥These null enums do not apply to MongoDB because there the difference between a JSON null and a database NULL does not exist.

  • 空枚举不适用于所有数据库中的 array_contains 运算符，因为 JSON 数组中只能有 JSON null。由于 JSON 数组中不可能存在数据库 NULL，因此 { array_contains: null } 是明确的。

    ¥The null enums do not apply to the array_contains operator in all databases because there can only be a JSON null within a JSON array. Since there cannot be a database NULL within a JSON array, { array_contains: null } is not ambiguous.

例如：

¥For example:

model Log {
  id   Int  @id
  meta Json
}

以下是使用 AnyNull 的示例：

¥Here is an example of using AnyNull:

import { Prisma } from '@prisma/client'

prisma.log.findMany({
  where: {
    data: {
      meta: {
        equals: Prisma.AnyNull,
      },
    },
  },
})

插入 null 值​

¥Inserting null Values

这也适用于 create、update 和 upsert。要将 null 值插入 Json 字段，你可以编写：

¥This also applies to create, update and upsert. To insert a null value into a Json field, you would write:

import { Prisma } from '@prisma/client'

prisma.log.create({
  data: {
    meta: Prisma.JsonNull,
  },
})

要将数据库 NULL 插入到 Json 字段中，你可以编写：

¥And to insert a database NULL into a Json field, you would write:

import { Prisma } from '@prisma/client'

prisma.log.create({
  data: {
    meta: Prisma.DbNull,
  },
})

按 null 值过滤​

¥Filtering by null Values

要按 JsonNull 或 DbNull 过滤，你可以编写：

¥To filter by JsonNull or DbNull, you would write:

import { Prisma } from '@prisma/client'

prisma.log.findMany({
  where: {
    meta: {
      equals: Prisma.AnyNull,
    },
  },
})
info

这些空枚举不适用于 MongoDB，因为 MongoDB 不区分 JSON null 和数据库 NULL。它们也不适用于所有数据库中的 array_contains 运算符，因为 JSON 数组中只能有 JSON null。由于 JSON 数组中不可能存在数据库 NULL，因此 { array_contains: null } 是明确的。

¥These null enums do not apply to MongoDB because MongoDB does not differentiate between a JSON null and a database NULL. They also do not apply to the array_contains operator in all databases because there can only be a JSON null within a JSON array. Since there cannot be a database NULL within a JSON array, { array_contains: null } is not ambiguous.

类型 Json​

¥Typed Json

默认情况下，Prisma 模型中不会输入 Json 字段。要在这些字段内实现强类型，你将需要使用像 prisma-json-types-generator 这样的外部包来完成此操作。

¥By default, Json fields are not typed in Prisma models. To accomplish strong typing inside of these fields, you will need to use an external package like prisma-json-types-generator to accomplish this.

使用 prisma-json-types-generator​

¥Using prisma-json-types-generator

首先，安装并配置 prisma-json-types-generator 根据封装说明。

¥First, install and configure prisma-json-types-generator according to the package's instructions.

然后，假设你有一个如下所示的模型：

¥Then, assuming you have a model like the following:

model Log {
  id   Int  @id
  meta Json
}

你可以更新它并使用 抽象语法树注释 键入它

¥You can update it and type it by using abstract syntax tree comments

schema.prisma
model Log {
  id Int @id

  /// [LogMetaType]
  meta Json
}

然后，确保你在 tsconfig.json 中包含的类型声明文件中定义了上述类型

¥Then, make sure you define the above type in a type declaration file included in your tsconfig.json

types.ts
declare global {
  namespace PrismaJson {
    type LogMetaType = { timestamp: number; host: string }
  }
}

现在，当使用 Log.meta 时，它将是强类型的！

¥Now, when working with Log.meta it will be strongly typed!

Json 常见问题解答​

¥Json FAQs

你可以选择要返回的 JSON 键/值的子集吗？​

¥Can you select a subset of JSON key/values to return?

不 - 选择要返回的 JSON 元素 还不可能。Prisma 客户端返回整个 JSON 对象。

¥No - it is not yet possible to select which JSON elements to return. Prisma Client returns the entire JSON object.

你可以过滤特定键的存在吗？​

¥Can you filter on the presence of a specific key?

不 - 目前还无法过滤特定密钥的存在。

¥No - it is not yet possible to filter on the presence of a specific key.

是否支持不区分大小写的过滤？​

¥Is case insensitive filtering supported?

不 - 尚不支持 不区分大小写的过滤。

¥No - case insensitive filtering is not yet supported.

你可以对 JSON 值中的对象属性进行排序吗？​

¥Can you sort an object property within a JSON value?

不，目前不支持 在 JSON 值中对对象属性进行排序（按属性排序）。

¥No, sorting object properties within a JSON value (order-by-prop) is not currently supported.

如何为 JSON 字段设置默认值？​

¥How to set a default value for JSON fields?

当你想要将 @default 值设置为 Json 类型时，你需要将其用双引号括在 @default 属性内（并且可能使用反斜杠转义任何 "inner" 双引号），例如：

¥When you want to set a @default value the Json type, you need to enclose it with double-quotes inside the @default attribute (and potentially escape any "inner" double-quotes using a backslash), for example:

model User {
  id    Int  @id @default(autoincrement())
  json1 Json @default("[]")
  json2 Json @default("{ \"hello\": \"world\" }")
}