SQL 查询入门Getting started with SQL queries

Azure Cosmos DB SQL API 帐户支持使用 结构化查询语言 (SQL) 作为 JSON 查询语言来查询项。Azure Cosmos DB SQL API accounts support querying items using Structured Query Language (SQL) as a JSON query language. Azure Cosmos DB 查询语言的设计目标是:The design goals of the Azure Cosmos DB query language are to:

  • 支持用户最熟悉的、最流行的 SQL 查询语言,而不是要发明一种新的查询语言。Support SQL, one of the most familiar and popular query languages, instead of inventing a new query language. SQL 提供正式的编程模型用于对 JSON 项进行丰富查询。SQL provides a formal programming model for rich queries over JSON items.

  • 使用 JavaScript 的编程模型作为查询语言的基础。Use JavaScript's programming model as the foundation for the query language. JavaScript 的类型系统、表达式计算和函数调用是 SQL API 的根。JavaScript's type system, expression evaluation, and function invocation are the roots of the SQL API. 这些根为关系投影、跨 JSON 项的分层导航、自联接、空间查询以及调用完全采用 JavaScript 编写的用户定义的函数 (UDF) 等功能提供自然编程模型。These roots provide a natural programming model for features like relational projections, hierarchical navigation across JSON items, self-joins, spatial queries, and invocation of user-defined functions (UDFs) written entirely in JavaScript.

上传示例数据Upload sample data

在 Cosmos DB SQL API 帐户中,创建名为 Families 的容器。In your SQL API Cosmos DB account, create a container called Families. 在容器中创建两个简单的 JSON 项。Create two simple JSON items in the container. 可以使用此数据集运行 Azure Cosmos DB 查询文档中的大多数示例查询。You can run most of the sample queries in the Azure Cosmos DB query docs using this data set.

创建 JSON 项Create JSON items

以下代码创建两个有关家庭的简单 JSON 项。The following code creates two simple JSON items about families. Andersen 和 Wakefield 家庭的简单 JSON 项包括父母、孩子及其宠物、地址和注册信息。The simple JSON items for the Andersen and Wakefield families include parents, children and their pets, address, and registration information. 第一个项包含字符串、数字、布尔、数组和嵌套属性。The first item has strings, numbers, Booleans, arrays, and nested properties.

{
  "id": "AndersenFamily",
  "lastName": "Andersen",
  "parents": [
     { "firstName": "Thomas" },
     { "firstName": "Mary Kay"}
  ],
  "children": [
     {
         "firstName": "Henriette Thaulow",
         "gender": "female",
         "grade": 5,
         "pets": [{ "givenName": "Fluffy" }]
     }
  ],
  "address": { "state": "WA", "county": "King", "city": "Seattle" },
  "creationDate": 1431620472,
  "isRegistered": true
}

第二个项使用 givenNamefamilyName,而不是使用 firstNamelastNameThe second item uses givenName and familyName instead of firstName and lastName.

{
  "id": "WakefieldFamily",
  "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
  ],
  "children": [
      {
        "familyName": "Merriam",
        "givenName": "Jesse",
        "gender": "female",
        "grade": 1,
        "pets": [
            { "givenName": "Goofy" },
            { "givenName": "Shadow" }
        ]
      },
      { 
        "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 8 }
  ],
  "address": { "state": "NY", "county": "Manhattan", "city": "NY" },
  "creationDate": 1431620462,
  "isRegistered": false
}

查询 JSON 项Query the JSON items

尝试对此 JSON 数据执行一些查询来了解 Azure Cosmos DB 的 SQL 查询语言的一些重要方面。Try a few queries against the JSON data to understand some of the key aspects of Azure Cosmos DB's SQL query language.

以下查询返回其中的 id 字段与 AndersenFamily 匹配的项。The following query returns the items where the id field matches AndersenFamily. 由于它是一个 SELECT * 查询,因此该查询的输出是完整的 JSON 项。Since it's a SELECT * query, the output of the query is the complete JSON item. 有关 SELECT 语法的详细信息,请参阅 SELECT 语句For more information about SELECT syntax, see SELECT statement.

    SELECT *
    FROM Families f
    WHERE f.id = "AndersenFamily"

查询结果为:The query results are:

    [{
        "id": "AndersenFamily",
        "lastName": "Andersen",
        "parents": [
           { "firstName": "Thomas" },
           { "firstName": "Mary Kay"}
        ],
        "children": [
           {
               "firstName": "Henriette Thaulow", "gender": "female", "grade": 5,
               "pets": [{ "givenName": "Fluffy" }]
           }
        ],
        "address": { "state": "WA", "county": "King", "city": "Seattle" },
        "creationDate": 1431620472,
        "isRegistered": true
    }]

以下查询将 JSON 输出的格式重新设置为不同的形式。The following query reformats the JSON output into a different shape. 当地址中的城市名称与州名称相同时,该查询将使用两个选定的字段 NameCity 来投影新的 JSON Family 对象。The query projects a new JSON Family object with two selected fields, Name and City, when the address city is the same as the state. “NY, NY”符合这种情况。"NY, NY" matches this case.

    SELECT {"Name":f.id, "City":f.address.city} AS Family
    FROM Families f
    WHERE f.address.city = f.address.state

查询结果为:The query results are:

    [{
        "Family": {
            "Name": "WakefieldFamily",
            "City": "NY"
        }
    }]

以下查询返回家庭中 id 匹配 WakefieldFamily 的所有孩子的名字,按城市排序。The following query returns all the given names of children in the family whose id matches WakefieldFamily, ordered by city.

    SELECT c.givenName
    FROM Families f
    JOIN c IN f.children
    WHERE f.id = 'WakefieldFamily'
    ORDER BY f.address.city ASC

其结果是:The results are:

    [
      { "givenName": "Jesse" },
      { "givenName": "Lisa"}
    ]

备注Remarks

上述示例演示了 Cosmos DB 查询语言的几个方面:The preceding examples show several aspects of the Cosmos DB query language:

  • 由于 SQL API 适用于 JSON 值,因此它可以处理三种形式的实体,而不是行和列。Since SQL API works on JSON values, it deals with tree-shaped entities instead of rows and columns. 可以引用任意深度的树节点(例如 Node1.Node2.Node3…..Nodem),类似于 ANSI SQL 中的 <table>.<column> 的两部分引用。You can refer to the tree nodes at any arbitrary depth, like Node1.Node2.Node3…..Nodem, similar to the two-part reference of <table>.<column> in ANSI SQL.

  • 由于查询语言适用于无架构数据,因此,必须动态绑定类型系统。Because the query language works with schemaless data, the type system must be bound dynamically. 相同的表达式在不同项上可能会产生不同的类型。The same expression could yield different types on different items. 查询的结果是有效的 JSON 值,但不保证它是固定的架构。The result of a query is a valid JSON value, but isn't guaranteed to be of a fixed schema.

  • Azure Cosmos DB 仅支持严格的 JSON 项。Azure Cosmos DB supports strict JSON items only. 类型系统和表达式仅限于处理 JSON 类型。The type system and expressions are restricted to deal only with JSON types. 有关详细信息,请参阅 JSON 规范For more information, see the JSON specification.

  • Cosmos DB 容器是 JSON 项的一个无架构集合。A Cosmos DB container is a schema-free collection of JSON items. 容器项内部以及跨容器项的关系是按包含关系隐式捕获的,而不是按主键和外键关系捕获的。The relations within and across container items are implicitly captured by containment, not by primary key and foreign key relations. 此特性对于本文稍后要讨论的项内联接非常重要。This feature is important for the intra-item joins discussed later in this article.

后续步骤Next steps