開始使用 SQL 查詢Getting started with SQL queries

適用於: SQL API

在 Azure Cosmos DB SQL API 帳戶中,有兩種方式可以讀取資料:In Azure Cosmos DB SQL API accounts, there are two ways to read data:

點讀取 -您可以針對單一 專案識別碼 和資料分割索引鍵進行索引鍵/值查閱。Point reads - You can do a key/value lookup on a single item ID and partition key. 專案識別碼 和資料分割索引鍵組合是索引鍵,而專案本身是值。The item ID and partition key combination is the key and the item itself is the value. 若是 1 KB 檔,點讀取通常是成本 1 要求單位 ,延遲低於10毫秒。For a 1 KB document, point reads typically cost 1 request unit with a latency under 10 ms. 點讀取會傳回單一專案。Point reads return a single item.

以下是如何使用每個 SDK 來執行 端點讀取 的一些範例:Here are some examples of how to do Point reads with each SDK:

Sql 查詢 -您可以使用結構化查詢語言 (SQL) (SQL) 作為 JSON 查詢語言來撰寫查詢,以查詢資料。SQL queries - You can query data by writing queries using the Structured Query Language (SQL) as a JSON query language. 查詢一律會產生至少2.3 的要求單位,而且通常會有比點讀取更高且更有變化的延遲。Queries always cost at least 2.3 request units and, in general, will have a higher and more variable latency than point reads. 查詢可能會傳回許多專案。Queries can return many items.

Azure Cosmos DB 上大部分的大量讀取工作負載都會使用點讀取和 SQL 查詢的組合。Most read-heavy workloads on Azure Cosmos DB use a combination of both point reads and SQL queries. 如果您只需要讀取單一專案,則點讀取比查詢更便宜且更快。If you just need to read a single item, point reads are cheaper and faster than queries. 點讀取不需要使用查詢引擎來存取資料,而且可以直接讀取資料。Point reads don't need to use the query engine to access data and can read the data directly. 當然,所有工作負載都無法使用點讀取來獨佔讀取資料,因此支援 SQL 作為查詢語言,以及不 限架構的索引編制 ,可提供更有彈性的方式來存取您的資料。Of course, it's not possible for all workloads to exclusively read data using point reads, so support of SQL as a query language and schema-agnostic indexing provide a more flexible way to access your data.

以下是如何使用每個 SDK 進行 SQL 查詢 的一些範例:Here are some examples of how to do SQL queries with each SDK:

本檔的其餘部分將說明如何開始在 Azure Cosmos DB 中撰寫 SQL 查詢。The remainder of this doc shows how to get started writing SQL queries in Azure Cosmos DB. 您可以透過 SDK 或 Azure 入口網站執行 SQL 查詢。SQL queries can be run through either the SDK or Azure portal.

上傳範例資料Upload sample data

在您的 SQL API Cosmos DB 帳戶中,開啟 資料總管 以建立名為的容器 FamiliesIn your SQL API Cosmos DB account, open the Data Explorer to create a container called Families. 建立容器之後,請使用資料結構瀏覽器來尋找並開啟它。After the container is created, use the data structures browser, to find and open it. 在您的 Families 容器中,您會在 Items 容器的名稱下方看到選項。In your Families container, you will see the Items option right below the name of the container. 開啟此選項,您會在畫面中央的功能表列中看到一個按鈕,以建立「新專案」。Open this option and you'll see a button, in the menu bar in center of the screen, to create a 'New Item'. 您將使用這項功能來建立下列 JSON 專案。You will use this feature to create the JSON items below.

建立 JSON 專案Create JSON items

下列2個 JSON 專案是 Andersen 和 Wakefield 系列的相關檔。The following 2 JSON items are documents about the Andersen and Wakefield families. 其中包括家長、子女及其寵物、位址和註冊資訊。They 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
}

第二個專案會使用 givenName 和, familyName 而不是 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 AndersenFamilyThe 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. 此查詢 Family 會使用兩個選取的欄位來投影新的 JSON 物件, NameCity 當地址城市與狀態相同時,則為。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 容器是 JSON 專案的無架構集合。A Cosmos 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. 這項功能對於 Azure Cosmos DB中的聯結所述的專案內聯接很重要。This feature is important for the intra-item joins that are described in Joins in Azure Cosmos DB.

下一步Next steps