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
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
This syntax enables you to create an internal user-defined function.
Function Name
QualifiedName
ScopeName
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:
-
If there is a preceding USE statement, the function is located in the Analytics scope specified by the USE statement.
-
Otherwise, if the query_context parameter is set, the function is located in the Analytics scope specified by the query context.
-
Otherwise, the function is located in the
Default
Analytics scope.
Parameter List
FunctionParameters
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
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: Function with expression body
This statement creates a function called rstr
, which returns the specified number of characters from the right of a string.
The function expects two named arguments: vString
, which is the string to work with, and vLen
, which is the number of characters to return.
The body of the function is an expression based on the two arguments.
CREATE ANALYTICS FUNCTION rstr(vString, vLen) { substr(vString, length(vString) - vLen, vLen) };
rstr("Couchbase", 4);
[ "base" ]
Example 2: Function with subquery body
This statement creates a function called total_spending
based on the Commerce
data in Appendix 4.
The function takes one parameter, a customer id.
Using a subquery, the function returns the total spending of all orders placed by that customer.
CREATE ANALYTICS FUNCTION total_spending(id) { SELECT VALUE SUM(i.qty * i.price) FROM orders AS o UNNEST o.items AS i WHERE o.custid = id };
total_spending("C13");
[ 13036.8 ]
Example 3: Variadic function with expression body
This statement creates a function that can take any number of arguments. Using an expression, the function returns the number of arguments that are passed to it.
CREATE ANALYTICS FUNCTION count_my_args( ... ) { array_count(args) }
count_my_args("Hello", "Goodbye");
[ 2 ]
Example 4: Variadic function containing a subquery
The body of this function is an expression that contains a subquery. The function takes a variable number of strings and returns the total length of all the strings.
CREATE ANALYTICS FUNCTION total_length(...) { array_sum( (SELECT VALUE length(a) FROM args AS a) ) };
total_length("Hello", "Goodbye");
[ 12 ]
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)) };
phi();
[ 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 };
phi();
[ 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: Python UDFs.
CreateExternalFunction
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 areunknown
,false
otherwise. Defaults tofalse
. -
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 totrue
.
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) AS "sentiment_mod", "sent_model.sentiment" AT pylib;
sentiment("beef");
[ "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) AS "sentiment_mod", "sent_model.sentiment" AT pylib; WITH { "null-call": true }
sentiment(null);
[ 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:
-
If the function call is enclosed within the function body of another user-defined function, the scope is that of the enclosing function.
-
Otherwise, if there is a preceding USE statement, the Analytics scope is specified by the USE statement.
-
Otherwise, if the query_context parameter is set, the Analytics scope is specified by the query context.
-
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.
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.
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] }
SELECT VALUE nameSearch("C25");
[ "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;
[ { "orderno": 1003, "custid": "C31", "name": "B. Pruitt" }, { "orderno": 1006, "custid": "C41", "name": "R. Dodge" } ]
Dropping a Function
DropFunction
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
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
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.
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.
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`;