Documentdb Sql 简明教程

DocumentDB SQL - Order By Clause

Microsoft Azure DocumentDB 支持使用 JSON 文档中的 SQL 查询文档。您可以使用查询中的 ORDER BY 子句按照数字和字符串对集合中的文档进行排序。该子句可以包含一个可选的 ASC/DESC 参数，以指定必须检索结果的顺序。

Microsoft Azure DocumentDB supports querying documents using SQL over JSON documents. You can sort documents in the collection on numbers and strings using an ORDER BY clause in your query. The clause can include an optional ASC/DESC argument to specify the order in which results must be retrieved.

我们将考虑与前例中相同的文档。

We will consider the same documents as in the previous examples.

以下是 AndersenFamily 文档。

Following is the AndersenFamily document.

{
   "id": "AndersenFamily",
   "lastName": "Andersen",

   "parents": [
      { "firstName": "Thomas", "relationship":  "father" },
      { "firstName": "Mary Kay", "relationship":  "mother" }
   ],

   "children": [
      {
         "firstName": "Henriette Thaulow",
         "gender": "female",
         "grade": 5,
         "pets": [ { "givenName": "Fluffy", "type":  "Rabbit" } ]
      }
   ],

   "location": { "state": "WA", "county": "King", "city": "Seattle" },
   "isRegistered": true
}

以下是 SmithFamily 文档。

Following is the SmithFamily document.

{
   "id": "SmithFamily",

   "parents": [
      { "familyName": "Smith", "givenName": "James" },
      { "familyName": "Curtis", "givenName": "Helen" }
   ],

   "children": [
      {
         "givenName": "Michelle",
         "gender": "female",
         "grade": 1
      },

      {
         "givenName": "John",
         "gender": "male",
         "grade": 7,

         "pets": [
            { "givenName": "Tweetie", "type": "Bird" }
         ]
      }
   ],

   "location": {
      "state": "NY",
      "county": "Queens",
      "city": "Forest Hills"
   },

   "isRegistered": true
}

以下是 WakefieldFamily 文档。

Following is the WakefieldFamily document.

{
   "id": "WakefieldFamily",

   "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
   ],

   "children": [
      {
         "familyName": "Merriam",
         "givenName": "Jesse",
         "gender": "female",
         "grade": 6,

         "pets": [
            { "givenName": "Charlie Brown", "type": "Dog" },
            { "givenName": "Tiger", "type": "Cat" },
            { "givenName": "Princess", "type": "Cat" }
         ]
      },

      {
         "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 3,

         "pets": [
            { "givenName": "Jake", "type": "Snake" }
         ]
      }
   ],

   "location": { "state": "NY", "county": "Manhattan", "city": "NY" },
   "isRegistered": false
}

我们来看一个简单的例子。

Let’s take a look at a simple example.

以下是包含 ORDER BY 关键字的查询。

Following is the query which contains the ORDER BY keyword.

SELECT  f.id, f.children[0].givenName,f.children[0].grade
FROM Families f
ORDER BY f.children[0].grade

执行以上查询后，将产生以下输出。

When the above query is executed, it produces the following output.

[
   {
      "id": "SmithFamily",
      "givenName": "Michelle",
      "grade": 1
   },

   {
      "id": "AndersenFamily",
      "grade": 5
   },

   {
      "id": "WakefieldFamily",
      "givenName": "Jesse",
      "grade": 6
   }
]

我们再考虑另一个简单的例子。

Let’s consider another simple example.

以下是对包含可选 ORDER BY 关键字和 DESC 关键字的查询，在其后加上注释。

Following is the query which contains the ORDER BY keyword and DESC optional keyword.

SELECT f.id, f.parents[0].familyName
FROM Families f
ORDER BY f.parents[0].familyName DESC

执行上述查询时，将会产生以下输出。

When the above query is executed, it will produce the following output.

[
   {
      "id": "WakefieldFamily",
      "familyName": "Wakefield"
   },

   {
      "id": "SmithFamily",
      "familyName": "Smith"
   },

   {
      "id": "AndersenFamily"
   }
]