使用 Azure Cosmos DB 中的 JSONWorking with JSON in Azure Cosmos DB

在 Azure Cosmos DB 的 SQL (Core) API 中,项存储为 JSON。In Azure Cosmos DB's SQL (Core) API, items are stored as JSON. 类型系统和表达式仅限于处理 JSON 类型。The type system and expressions are restricted to deal only with JSON types. 有关详细信息,请参阅 JSON 规范For more information, see the JSON specification.

我们将总结一些使用 JSON 的重要方面:We'll summarize some important aspects of working with JSON:

  • 始终使用大括号 { } 将 JSON 对象括起来JSON objects always begin with a { left brace and end with a } right brace
  • 可以将 JSON 属性彼此嵌套You can have JSON properties nested within one another
  • JSON 属性值可以是数组JSON property values can be arrays
  • JSON 属性名称区分大小写JSON property names are case sensitive
  • JSON 属性名称可以是任何字符串值(包括空格或不是字母的字符)JSON property name can be any string value (including spaces or characters that aren't letters)

嵌套属性Nested properties

可以使用 Dot 访问器访问嵌套的 JSON。You can access nested JSON using a dot accessor. 可以在查询中使用嵌套的 JSON 属性,其方式与使用任何其他属性的方式相同。You can use nested JSON properties in your queries the same way that you can use any other properties.

下面是带有嵌套 JSON 的文档:Here's a document with nested JSON:

{
  "id": "AndersenFamily",
  "lastName": "Andersen",
  "address": {
      "state": "WA",
      "county": "King",
      "city": "Seattle"
      },
  "creationDate": 1431620472,
  "isRegistered": true
}

在这种情况下,statecountrycity 属性都嵌套在 address 属性中。In this case, the state, country, and city properties are all nested within the address property.

以下示例投影两个嵌套属性:f.address.statef.address.cityThe following example projects two nested properties: f.address.state and f.address.city.

    SELECT f.address.state, f.address.city
    FROM Families f
    WHERE f.id = "AndersenFamily"

结果有:The results are:

    [{
      "state": "WA",
      "city": "Seattle"
    }]

使用数组Working with arrays

除嵌套属性外,JSON 还支持数组。In addition to nested properties, JSON also supports arrays.

下面是一个包含数组的示例文档:Here's an example document with an array:

{
  "id": "WakefieldFamily",
  "children": [
      {
        "familyName": "Merriam",
        "givenName": "Jesse",
        "gender": "female",
        "grade": 1,
      },
      {
        "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 8
      }
  ],
}

使用数组时,可以通过引用数组中的特定元素的位置来访问该元素:When working with arrays, you can access a specific element within the array by referencing its position:

SELECT *
FROM Families f
WHERE f.children[0].givenName = "Jesse"

但在大多数情况下,在使用数组时,你将使用子查询自联接In most cases, however, you'll use a subquery or self-join when working with arrays.

例如,下面的文档显示客户银行帐户的每日余额。For example, here's a document that shows the daily balance of a customer's bank account.

{
  "id": "Contoso-Checking-Account-2020",
  "balance": [
      {
        "checkingAccount": 1000,
        "savingsAccount": 5000
      },
      {
        "checkingAccount": 100,
        "savingsAccount": 5000
      },
      {
        "checkingAccount": -10,
        "savingsAccount": 5000,
      },
      {
        "checkingAccount": 5000,
        "savingsAccount": 5000,
      }
         ...
  ]
}

如果你想要运行一个查询,该查询显示在某个时间点所有余额为负数的客户,则可以将 EXISTS 与一个子查询一起使用:If you wanted to run a query that showed all the customers that had a negative balance at some point, you could use EXISTS with a subquery:

SELECT c.id
FROM c
WHERE EXISTS(
    SELECT VALUE n
    FROM n IN c.balance
    WHERE n.checkingAccount < 0
)

JSON 中的保留关键字和特殊字符Reserved keywords and special characters in JSON

可以使用带引号的属性运算符 [] 访问属性。You can access properties using the quoted property operator []. 例如,由于再也无法解析标识符“Families”,因此 SELECT c.grade and SELECT c["grade"] 是等效的。For example, SELECT c.grade and SELECT c["grade"] are equivalent. 此语法很适合用于转义包含空格和特殊字符的属性,或者其名称与 SQL 关键字或保留字相同的属性。This syntax is useful to escape a property that contains spaces, special characters, or has the same name as a SQL keyword or reserved word.

例如,下面是一个文档,具有一个名为 order 的属性和一个包含特殊字符的属性 price($)For example, here's a document with a property named order and a property price($) that contains special characters:

{
  "id": "AndersenFamily",
  "order": {
         "orderId": "12345",
         "productId": "A17849",
         "price($)": 59.33
   },
  "creationDate": 1431620472,
  "isRegistered": true
}

如果运行包含 order 属性或 price($) 属性的查询,则会收到语法错误。If you run a queries that includes the order property or price($) property, you will receive a syntax error.

SELECT * FROM c where c.order.orderid = "12345"
SELECT * FROM c where c.order.price($) > 50

结果为:The result is:

Syntax error, incorrect syntax near 'order'

应按如下所示重写相同的查询:You should rewrite the same queries as below:

SELECT * FROM c WHERE c["order"].orderId = "12345"
SELECT * FROM c WHERE c["order"]["price($)"] > 50

JSON 表达式JSON expressions

投影也支持 JSON 表达式,如以下示例所示:Projection also supports JSON expressions, as shown in the following example:

    SELECT { "state": f.address.state, "city": f.address.city, "name": f.id }
    FROM Families f
    WHERE f.id = "AndersenFamily"

结果有:The results are:

    [{
      "$1": {
        "state": "WA",
        "city": "Seattle",
        "name": "AndersenFamily"
      }
    }]

在上述示例中,SELECT 子句需要创建一个 JSON 对象;由于该示例未提供键,因此子句使用了隐式参数变量名称 $1In the preceding example, the SELECT clause needs to create a JSON object, and since the sample provides no key, the clause uses the implicit argument variable name $1. 以下查询返回两个隐式参数变量:$1$2The following query returns two implicit argument variables: $1 and $2.

    SELECT { "state": f.address.state, "city": f.address.city },
           { "name": f.id }
    FROM Families f
    WHERE f.id = "AndersenFamily"

结果有:The results are:

    [{
      "$1": {
        "state": "WA",
        "city": "Seattle"
      },
      "$2": {
        "name": "AndersenFamily"
      }
    }]

别名Aliasing

可以显式为查询中的值指定别名。You can explicitly alias values in queries. 如果查询包含两个同名的属性,请使用别名来重命名其中一个或两个属性,以便可以在投影的结果中消除其歧义。If a query has two properties with the same name, use aliasing to rename one or both of the properties so they're disambiguated in the projected result.

示例Examples

如以下示例所示,将第二个值投影为 NameInfo 时,用于别名的 AS 关键字是可选的:The AS keyword used for aliasing is optional, as shown in the following example when projecting the second value as NameInfo:

    SELECT
           { "state": f.address.state, "city": f.address.city } AS AddressInfo,
           { "name": f.id } NameInfo
    FROM Families f
    WHERE f.id = "AndersenFamily"

结果有:The results are:

    [{
      "AddressInfo": {
        "state": "WA",
        "city": "Seattle"
      },
      "NameInfo": {
        "name": "AndersenFamily"
      }
    }]

带有保留关键字或特殊字符的别名Aliasing with reserved keywords or special characters

不能使用别名将值投影为带有空格、特殊字符或保留字的属性名称。You can't use aliasing to project a value as a property name with a space, special character, or reserved word. 例如,如果要将值的投影更改为带有空格的属性名称,则可以使用 JSON 表达式If you wanted to change a value's projection to, for example, have a property name with a space, you could use a JSON expression.

下面是一个示例:Here's an example:

    SELECT
           {"JSON expression with a space": { "state": f.address.state, "city": f.address.city }},
           {"JSON expression with a special character!": { "name": f.id }}
    FROM Families f
    WHERE f.id = "AndersenFamily"

后续步骤Next steps