Documentdb Sql 简明教程

DocumentDB SQL - User-Defined Functions

DocumentDB SQL 提供对用户自定义函数 (UDF) 的支持。UDF 只是另一种你可以编写的 JavaScript 函数，且它们的工作方式几乎和你预期的一样。你可以创建 UDF 以使用可以在查询中引用的自定义业务逻辑来扩展查询语言。

DocumentDB SQL provides support for User-Defined Functions (UDFs). UDFs are just another kind of JavaScript functions you can write and these work pretty much as you’d expect. You can create UDFs to extend the query language with custom business logic that you can reference in your queries.

DocumentDB SQL 语法被扩展以使用这些 UDF 来支持自定义应用程序逻辑。UDF 可以向 DocumentDB 注册，然后被引用为 SQL 查询的一部分。

The DocumentDB SQL syntax is extended to support custom application logic using these UDFs. UDFs can be registered with DocumentDB and then be referenced as part of a SQL query.

为此示例，我们考虑以下三个文档。

Let’s consider the following three documents for this example.

AndersenFamily 文档如下。

AndersenFamily document is as follows.

{
   "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 文档如下。

SmithFamily document is as follows.

{
   "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 文档如下。

WakefieldFamily document is as follows.

{
   "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
}

我们来看一个我们将创建一些简单 UDF 的示例。

Let’s take a look at an example where we will create some simple UDFs.

以下是 CreateUserDefinedFunctions 的实现。

Following is the implementation of CreateUserDefinedFunctions.

private async static Task CreateUserDefinedFunctions(DocumentClient client) {
   Console.WriteLine();
   Console.WriteLine("**** Create User Defined Functions ****");
   Console.WriteLine();

   await CreateUserDefinedFunction(client, "udfRegEx");
}

我们有一个 udfRegEx，在 CreateUserDefinedFunction 中，我们从本地文件中获取其 JavaScript 代码。我们构造新 UDF 的定义对象，并使用集合的 SelfLink 和如下代码所示的 udfDefinition 对象调用 CreateUserDefinedFunctionAsync。

We have a udfRegEx, and in CreateUserDefinedFunction we get its JavaScript code from our local file. We construct the definition object for the new UDF, and call CreateUserDefinedFunctionAsync with the collection’s SelfLink and the udfDefinition object as shown in the following code.

private async static Task<UserDefinedFunction>
CreateUserDefinedFunction(DocumentClient client, string udfId) {
   var udfBody = File.ReadAllText(@"..\..\Server\" + udfId + ".js");

   var udfDefinition = new UserDefinedFunction {
      Id = udfId,
      Body = udfBody
   };

   var result = await client
      .CreateUserDefinedFunctionAsync(_collection.SelfLink, udfDefinition);
   var udf = result.Resource;

   Console.WriteLine("Created user defined function {0}; RID: {1}",
      udf.Id, udf.ResourceId);

   return udf;
}

我们从结果的 resource 属性中返回新的 UDF，并将其返回给调用者。为了显示现有的 UDF，以下是 ViewUserDefinedFunctions 的实现。我们调用 CreateUserDefinedFunctionQuery 并像往常一样遍历它们。

We get back the new UDF from the resource property of the result and return it back up to the caller. To display the existing UDF, following is the implementation of ViewUserDefinedFunctions. We call CreateUserDefinedFunctionQuery and loop through them as usual.

private static void ViewUserDefinedFunctions(DocumentClient client) {
   Console.WriteLine();
   Console.WriteLine("**** View UDFs ****");
   Console.WriteLine();

   var udfs = client
      .CreateUserDefinedFunctionQuery(_collection.UserDefinedFunctionsLink)
      .ToList();

   foreach (var udf in udfs) {
      Console.WriteLine("User defined function {0}; RID: {1}", udf.Id, udf.ResourceId);
   }
}

DocumentDB SQL 未提供用于搜索子字符串或正则表达式的内置函数，因此以下简洁的一行代码填补了缺口，它是一个 JavaScript 函数。

DocumentDB SQL doesn’t provide built-in functions to search for substrings or for regular expressions, hence the following little one-liner fills that gap which is a JavaScript function.

function udfRegEx(input, regex) {
   return input.match(regex);
}

鉴于第一个参数中的输入字符串，使用 JavaScript 的内置正则表达式支持，将第二个参数中的模式匹配字符串传递到 . match 中。我们可以运行一个子字符串查询，以在 lastName 属性中找到单词 Andersen 的所有商店。

Given the input string in the first parameter, use JavaScript’s built-in regular expression support passing in the pattern matching string in the second parameter into .match. We can run a substring query to find all stores with the word Andersen in their lastName property.

private static void Execute_udfRegEx(DocumentClient client) {
   var sql = "SELECT c.name FROM c WHERE udf.udfRegEx(c.lastName, 'Andersen') != null";

   Console.WriteLine();
   Console.WriteLine("Querying for Andersen");

   var documents = client.CreateDocumentQuery(_collection.SelfLink, sql).ToList();
   Console.WriteLine("Found {0} Andersen:", documents.Count);

   foreach (var document in documents) {
      Console.WriteLine("Id: {0}, Name: {1}", document.id, document.lastName);
   }
}

请注意，我们必须使用前缀 udf 限定每个 UDF 引用。就像对任何普通查询一样，我们刚刚将 SQL 传递给 CreateDocumentQuery 。最后，让我们从 CreateDocumentClient 任务中调用以上查询

Note that we must qualify every UDF reference with the prefix udf. We just passed the SQL along to CreateDocumentQuery like any ordinary query. Finally, let’s call the above queries from the CreateDocumentClient task

private static async Task CreateDocumentClient() {
   // Create a new instance of the DocumentClient

   using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)){
      database = client.CreateDatabaseQuery("SELECT * FROM c WHERE
         c.id = 'myfirstdb'").AsEnumerable().First();
      collection = client.CreateDocumentCollectionQuery(database.CollectionsLink,
         "SELECT * FROM c WHERE c.id = 'Families'").AsEnumerable().First();

      await CreateUserDefinedFunctions(client);

      ViewUserDefinedFunctions(client);

      Execute_udfRegEx(client);
   }
}

执行以上代码时，会产生以下输出。

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

**** Create User Defined Functions ****
Created user defined function udfRegEx; RID: kV5oANVXnwAlAAAAAAAAYA==
**** View UDFs ****
User defined function udfRegEx; RID: kV5oANVXnwAlAAAAAAAAYA==
Querying for Andersen
Found 1 Andersen:
 Id: AndersenFamily, Name: Andersen