Azure Cosmos DB 中的用户定义函数 (UDF)User-defined functions (UDFs) in Azure Cosmos DB

SQL API 支持用户定义函数 (UDF)。The SQL API provides support for user-defined functions (UDFs). 使用标量 UDF,可以传入零个或多个参数,并返回单个参数结果。With scalar UDFs, you can pass in zero or many arguments and return a single argument result. API 会检查每个参数 JSON 值是否合法。The API checks each argument for being legal JSON values.

UDF 用例UDF use cases

API 扩展了 SQL 语法,支持使用 UDF 的自定义应用程序逻辑。The API extends the SQL syntax to support custom application logic using UDFs. 可将 UDF 注册到 SQL API,然后在 SQL 查询中引用它们。You can register UDFs with the SQL API, and reference them in SQL queries. 与存储过程和触发器不同,UDF 为只读。Unlike stored procedures and triggers, UDFs are read-only.

使用 UDF 可以扩展 Azure Cosmos DB 的查询语言。Using UDFs, you can extend Azure Cosmos DB's query language. UDF 是在查询投影中表达复杂业务逻辑的好方法。UDFs are a great way to express complex business logic in a query's projection.

但是,我们建议在以下情况下避免使用 UDF:However, we recommending avoiding UDFs when:

  • Azure Cosmos DB 中已存在等效的系统函数An equivalent system function already exists in Azure Cosmos DB. 系统函数将始终使用比等效 UDF 更少的 RU。System functions will always use fewer RU's than the equivalent UDF.
  • UDF 是查询的 WHERE 子句中唯一的筛选器。The UDF is the only filter in the WHERE clause of your query. UDF 不利用索引,因此,计算 UDF 需要加载文档。UDF's do not utilize the index so evaluating the UDF will require loading documents. WHERE 子句中将使用索引的附加筛选器谓词与 UDF 组合在一起可减少 UDF 处理的文档数。Combining additional filter predicates that use the index, in combination with a UDF, in the WHERE clause will reduce the number of documents processed by the UDF.

如果在查询中必须多次使用同一 UDF,则应该引用子查询中的 UDF,这样可使用 JOIN 表达式计算 UDF 一次,但引用它多次。If you must use the same UDF multiple times in a query, you should reference the UDF in a subquery, allowing you to use a JOIN expression to evaluate the UDF once but reference it many times.

示例Examples

以下示例在 Cosmos 数据库中的某个项容器下注册一个 UDF。The following example registers a UDF under an item container in the Cosmos database. 该示例创建了名为 REGEX_MATCH 的 UDF。The example creates a UDF whose name is REGEX_MATCH. 它接受两个 JSON 字符串值:inputpattern,并使用 JavaScript 的 string.match() 函数检查第一个值是否与第二个值中指定的模式相匹配。It accepts two JSON string values, input and pattern, and checks if the first matches the pattern specified in the second using JavaScript's string.match() function.

UserDefinedFunction regexMatchUdf = new UserDefinedFunction
{
   Id = "REGEX_MATCH",
   Body = @"function (input, pattern) {
              return input.match(pattern) !== null;
           };",
};

UserDefinedFunction createdUdf = client.CreateUserDefinedFunctionAsync(
   UriFactory.CreateDocumentCollectionUri("myDatabase", "families"),
   regexMatchUdf).Result;  

现在,请在查询投影中使用此 UDF。Now, use this UDF in a query projection. 从查询内部调用 UDF 时,必须使用区分大小写的前缀 udf. 来限定 UDF。You must qualify UDFs with the case-sensitive prefix udf. when calling them from within queries.

    SELECT udf.REGEX_MATCH(Families.address.city, ".*eattle")
    FROM Families

结果有:The results are:

    [
      {
        "$1": true
      },
      {
        "$1": false
      }
    ]

可以在筛选器中使用以 udf. 前缀限定的 UDF,如以下示例所示:You can use the UDF qualified with the udf. prefix inside a filter, as in the following example:

    SELECT Families.id, Families.address.city
    FROM Families
    WHERE udf.REGEX_MATCH(Families.address.city, ".*eattle")

结果有:The results are:

    [{
        "id": "AndersenFamily",
        "city": "Seattle"
    }]

从本质上来说,UDF 是可以在投影和筛选器中使用的有效标量表达式。In essence, UDFs are valid scalar expressions that you can use in both projections and filters.

为了进一步了解 UDF 的强大功能,让我们查看使用条件逻辑的另一个示例:To expand on the power of UDFs, look at another example with conditional logic:

UserDefinedFunction seaLevelUdf = new UserDefinedFunction()
{
   Id = "SEALEVEL",
   Body = @"function(city) {
           switch (city) {
               case 'Seattle':
                   return 520;
               case 'NY':
                   return 410;
               case 'Shanghai':
                   return 673;
               default:
                   return -1;
            }"
    };

    UserDefinedFunction createdUdf = await client.CreateUserDefinedFunctionAsync(
        UriFactory.CreateDocumentCollectionUri("myDatabase", "families"),
        seaLevelUdf);

以下示例运用了 UDF:The following example exercises the UDF:

    SELECT f.address.city, udf.SEALEVEL(f.address.city) AS seaLevel
    FROM Families f

结果有:The results are:

    [
      {
        "city": "Seattle",
        "seaLevel": 520
      },
      {
        "city": "NY",
        "seaLevel": 410
      }
    ]

如果 UDF 参数引用的属性在 JSON 值中未提供,则会将该参数视为未定义,因此会跳过 UDF 调用。If the properties referred to by the UDF parameters aren't available in the JSON value, the parameter is considered as undefined and the UDF invocation is skipped. 同样,如果未定义 UDF 的结果,则不会将此 UDF 包含在结果中。Similarly, if the result of the UDF is undefined, it's not included in the result.

如以上示例所示,UDF 将 JavaScript 语言的强大功能与 SQL API 相集成。As the preceding examples show, UDFs integrate the power of JavaScript language with the SQL API. UDF 提供丰富的可编程接口来执行复杂的过程,并借助内置的 JavaScript 运行时功能来执行条件逻辑。UDFs provide a rich programmable interface to do complex procedural, conditional logic with the help of built-in JavaScript runtime capabilities. SQL API 在当前的 WHERE 或 SELECT 子句处理阶段,为每个源项的 UDF 提供参数。The SQL API provides the arguments to the UDFs for each source item at the current WHERE or SELECT clause stage of processing. 结果将无缝整合到总体执行管道中。The result is seamlessly incorporated in the overall execution pipeline. 总而言之,UDF 是在查询过程中执行复杂业务逻辑的极佳工具。In summary, UDFs are great tools to do complex business logic as part of queries.

后续步骤Next steps