User-Defined Functions

    +

    In N1QL for Analytics, user-defined functions enable you to name and reuse complex or repetitive expressions, including subqueries, in order to simplify your queries.

    Each user-defined function belongs to an Analytics scope. Within an Analytics scope, a function is uniquely identified by its function signature: that is, its name and the number of parameters it takes. You can create more than one function with the same name in the same Analytics scope, as long as each function has a different number of parameters.

    You can query the Function Analytics collection within the Metadata Analytics scope to get metadata about all existing user-defined functions. Note that FUNCTION is a reserved keyword, so you need to delimit the name of the Function collection with backticks (`).

    You can’t create a function in the Metadata scope.

    Creating a Function

    CreateFunction
    CreateInternalFunction | CreateExternalFunction

    The CREATE ANALYTICS FUNCTION command enables you to create a user-defined function. There are two types of user-defined function: internal functions, which are defined using N1QL for Analytics expressions; and external functions, which are defined using an external language.

    Internal Functions

    CreateInternalFunction
    "CREATE" ( "OR" "REPLACE" )? "ANALYTICS" "FUNCTION" QualifiedName "(" FunctionParameters? ")" ("IF" "NOT" "EXISTS")? "{" Query "}"

    This syntax enables you to create an internal user-defined function.

    Function Name

    QualifiedName
    (ScopeName ".")? Identifier
    ScopeName
    (Identifier ".")? Identifier

    The QualifiedName specifies the name of the function to create. It consists of an optional scope name, followed by an identifier which represents the local name of the function.

    The optional ScopeName specifies the Analytics scope where the function is located. It may consist of one or two identifiers, separated by a dot.

    If you don’t specify an Analytics scope, then the scope containing the function is resolved according to the following rules:

    1. If there is a preceding USE statement, the function is located in the Analytics scope specified by the USE statement.

    2. Otherwise, if the query_context parameter is set, the function is located in the Analytics scope specified by the query context.

    3. Otherwise, the function is located in the Default Analytics scope.

    Parameter List

    FunctionParameters
    Identifier ( "," Identifier )* | "…​"

    The parameter list specifies parameters for the function. It is delimited by parentheses (). You can specify named parameters for the function using a comma-separated list of identifiers.

    If you specify named parameters for the function, then you must call the function with exactly the same number of arguments at execution time. If you specify no parameters, then you must call the function with no arguments.

    To create a variadic function, that is, a function which you can call with any number of arguments or none, specify ... as the only parameter.

    Function Body

    Query
    Expr | Selection

    The function body defines the function. It is delimited by braces {}. It may contain any valid expression or subquery.

    If you specified named parameters for the function, you can use these in the function body to represent arguments which are passed to the function at execution time. If you specified that the function is variadic, any arguments passed to the function at execution time are held in an array named args.

    The function body may refer to other Analytics collections, or to other functions in the same or other Analytics scopes. If an Analytics scope name was explicitly specified when creating the function, then that scope is the default scope for function calls or collection references within the function body.

    Note that recursive function invocation is not permitted. The function body may not refer to itself, nor to another user-defined function which calls this function indirectly.

    Checking for an Existing Function and Replacing a Function

    The optional IF NOT EXISTS keywords enable you to check whether a user-defined function exists before creating it, and the optional OR REPLACE keywords enable you to redefine the function.

    If a function with the same signature already exists within the specified Analytics scope, then:

    • If the OR REPLACE keywords are present, the existing function is replaced.

    • If the OR REPLACE keywords are not present, then:

      • If the IF NOT EXISTS keywords are present, the statement does nothing and completes without error.

      • If the IF NOT EXISTS keywords are not present, an error is generated.

    Note that if the statement contains both the OR REPLACE keywords and the IF NOT EXISTS keywords, an error is generated.

    Internal Function Examples

    For simplicity, none of these examples implement any data validation.

    Example 1: Variadic function

    This statement creates a function called to_celsius, which converts Fahrenheit to Celsius. The function is variadic.

    For purposes of illustration, this expression converts just the first argument supplied at execution time, which is stored in the first member in the args array. A more realistic variadic function would make use of all the supplied arguments.

    CREATE ANALYTICS FUNCTION to_celsius(...) { (args[0] - 32) * 5/9 };
    Test
    to_celsius(100);
    Result
    [
      37.77777777777778
    ]
    Example 2: Variadic function with extra arguments

    This statement creates a function called to_fahrenheit, which converts Celsius to Fahrenheit. The function is variadic.

    For purposes of illustration, this expression converts just the first argument supplied at execution time, which is stored in the first member in the args array. A more realistic variadic function would make use of all the supplied arguments.

    CREATE ANALYTICS FUNCTION to_fahrenheit(...) { (args[0] * 9/5) + 32 };
    Test
    to_fahrenheit(100, "ignore this");
    Result
    [
      212
    ]

    As the function is variadic, you can use any number of arguments when you call the function. Arguments which are not used by the function expression are ignored.

    Example 3: Function with fixed parameters

    The following statement creates a function called rstr, which returns the specified number of characters from the right of a string. The expression expects two named arguments: vString, which is the string to work with, and vLen, which is the number of characters to return.

    CREATE ANALYTICS FUNCTION rstr(vString, vLen) { substr(vString, length(vString) - vLen, vLen) };
    Test
    rstr("Couchbase", 4);
    Result
    [
      "base"
    ]
    Example 4: Function with fixed parameters and extra arguments

    The following statement creates a function called lstr, which returns the specified number of characters from the left of a string. The expression expects two named arguments: vString, which is the string to work with, and vLen, which is the number of characters to return.

    CREATE ANALYTICS FUNCTION lstr(vString, vLen) { substr(vString, 0, vLen) };
    Test
    lstr("Couchbase", 5, "ignore this");

    This function returns an error. As the arguments were specified by the function definition, you must use the same number of arguments when you call the function. If you supply the wrong number of arguments, an error is generated.

    Example 5: Function with no parameters

    This statement creates a function which returns the mathematical constant φ. The function takes no arguments.

    CREATE ANALYTICS FUNCTION phi() { 2 * sin(radians(54)) };
    Test
    phi();
    Result
    [
      1.618033988749895
    ]
    Example 6: Replace a function

    The following statement redefines the function so that it calculates φ using a different method.

    CREATE OR REPLACE ANALYTICS FUNCTION phi() { (1 + sqrt(5)) / 2 };
    Test
    phi();
    Result
    [
      1.618033988749895
    ]

    External Functions

    Developer Preview

    In Couchbase Server 7.0 you can create external user-defined functions using Python code. This is a Developer Preview feature. For further details see Appendix 5.

    CreateExternalFunction
    "CREATE" ( "OR" "REPLACE" )? "ANALYTICS" "FUNCTION" QualifiedName "(" FunctionParameters? ")" ("IF" "NOT" "EXISTS")? ("RETURNS" TypeExpr)? "AS" StringLiteral("," StringLiteral)* "AT" QualifiedName("WITH" ObjectConstructor)?

    The syntax for the function name, the parameter list, and the OR REPLACE and IF NOT EXISTS keywords is the same as for creating an internal function.

    StringLiteral

    The external language specific identifier for the function to be bound to the created N1QL for Analytics function. For example, a function name or part of a fully qualified method name. The exact usage will depend on each external language’s features and requirements.

    QualifiedName

    The name of a previously created N1QL for Analytics library created via the Libraries REST API. This is parsed as an identifier and should not be quoted.

    ObjectConstructor

    An object constructor with one or more of the following keys and values:

    • null-call: true if the function should be called if one or more arguments are unknown, false otherwise. Defaults to false.

    • deterministic: true if the function returns the same output for the same input always and as such can be cached. false if it should be computed every time. Defaults to true.

    Python UDFs allow you to utilize your Python code from N1QL for Analytics as a normal function. Each Python function is defined from within a Library. Libraries belong to Scopes and are created by uploads to the UDF API endpoint. They contain a set of Python modules along with the dependencies of those modules.

    External Function Examples

    Developer Preview

    Example 7: Create an external Python function

    Python UDFs use an identifier in the form of module, function or module, class.method. This example is of the latter form.

    CREATE ANALYTICS FUNCTION sentiment(a)
    RETURNS TweetType
      AS "sentiment_mod", "sent_model.sentiment" AT pylib;
    Test
    sentiment("beef");
    Result
    [
      "eh"
    ]
    Example 8: Create an external Python function with options

    The same example as above, but with null-call set to true.

    CREATE ANALYTICS FUNCTION sentiment(a)
    RETURNS TweetType
      AS "sentiment_mod", "sent_model.sentiment" AT pylib;
      WITH { "null-call": true }
    Test
    sentiment(null);
    Result
    [
      null
    ]

    Calling a Function

    OrdinaryFunctionCall

    You can invoke a user-defined function in the same way as any other ordinary function. You may optionally prefix the name of the function with the ScopeName of the Analytics scope containing the function.

    If you don’t specify an Analytics scope when calling a function, then the scope containing the function is resolved according to the following rules:

    1. If the function call is enclosed within the function body of another user-defined function, the scope is that of the enclosing function.

    2. Otherwise, if there is a preceding USE statement, the Analytics scope is specified by the USE statement.

    3. Otherwise, if the query_context parameter is set, the Analytics scope is specified by the query context.

    4. Otherwise, use the Default Analytics scope.

    Having determined the scope, Analytics tries to find a user-defined function with the same function signature within that scope. If a user-defined function cannot be found, Analytics tries to find a built-in function with the same function name. If a built-in function cannot be found, the function call fails.

    Examples

    The examples in this section assume that you are using an Analytics scope called Commerce. Refer to Appendix 4: Example Data to install this example data.

    You can use the USE statement to set the default scope for the statement immediately following.

    Example
    USE Commerce;

    Alternatively, use the query context drop-down list at the top right of the Query Editor to select Commerce as the default scope for the following examples.

    The query context drop-down menu with 'Commerce' selected
    Example 9: Function with subquery

    The following statement creates a function called nameSearch, which selects the customer name from all documents with the specified ID in the customers collection.

    CREATE ANALYTICS FUNCTION nameSearch(customerId) {
      (SELECT VALUE c.name
      FROM customers AS c
      WHERE c.custid = customerId)[0]
    }
    Test
    SELECT VALUE nameSearch("C25");
    Result
    [
      "M. Sinclair"
    ]
    Example 10: Call a user-defined function

    The following query uses the nameSearch function as a projection expression in a SELECT query. Compare this with example Q3.29 in the section on Subqueries.

    SELECT o.orderno, o.custid,
          nameSearch(o.custid) AS name
    FROM orders AS o, o.items AS i
    WHERE i.itemno = 120;
    Result
    [
        {
            "orderno": 1003,
            "custid": "C31",
            "name": "B. Pruitt"
        },
        {
            "orderno": 1006,
            "custid": "C41",
            "name": "R. Dodge"
        }
    ]

    Dropping a Function

    DropFunction
    "DROP" "ANALYTICS" "FUNCTION" QualifiedName "(" FunctionParameters? ")" ( "IF" "EXISTS" )?

    The DROP ANALYTICS FUNCTION statement enables you to delete a user-defined function.

    You cannot delete a user-defined function if there are any other user-defined functions which call this function, in this Analytics scope or any other Analytics scope.

    Function Name

    QualifiedName
    (ScopeName ".")? Identifier
    ScopeName
    (Identifier ".")? Identifier

    The QualifiedName specifies the name of the function to delete. It consists of an optional scope name, followed by an identifier which represents the local name of the function.

    The optional ScopeName specifies the Analytics scope where the function is located. It may consist of one or two identifiers, separated by a dot.

    If you don’t specify an Analytics scope, then the scope containing the function is resolved according to the same rules that are used when creating a user-defined function.

    Parameter List

    FunctionParameters
    Identifier ( "," Identifier )* | "…​"

    When you drop a function, you must specify the same number of parameters that you specified when you created the function, or specify ... if you are dropping a variadic function.

    Checking for an Existing Function

    The optional IF EXISTS keywords enable you to check whether the specified function or scope exists before dropping it. If these keywords are present, and the function scope or function name are unknown, the statement does nothing and completes without error.

    If these keywords are not present, and the function scope or function name are unknown, an error is generated.

    Examples

    Again, the examples in this section assume that you are using an Analytics scope called Commerce. Refer to Appendix 4: Example Data to install this example data.

    You can use the USE statement to set the default scope for the statement immediately following.

    Example
    USE Commerce;

    Alternatively, use the query context drop-down list at the top right of the Query Editor to select Commerce as the default scope for the following example.

    The query context drop-down menu with 'Commerce' selected
    Example 11: Drop a Function

    This statement drops the function named nameSearch, if it exists.

    DROP ANALYTICS FUNCTION nameSearch(customerId) IF EXISTS;

    You can run the following query to check that the function is no longer available.

    SELECT * FROM Metadata.`Function`;