{"id":9720,"date":"2021-08-25T00:00:09","date_gmt":"2021-08-25T07:00:09","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=9720"},"modified":"2025-06-13T23:34:39","modified_gmt":"2025-06-14T06:34:39","slug":"n1ql-user-defined-functions","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/pt\/n1ql-user-defined-functions\/","title":{"rendered":"O N1QL agora oferece suporte a fun\u00e7\u00f5es definidas pelo usu\u00e1rio"},"content":{"rendered":"

Declarative query languages have been a major gear shift<\/strong> in the world of database engines.<\/p>\n

The SQL++ query language<\/a> (formerly N1QL) is primarily a declarative query language. You tell the query what to get and N1QL works out the rest of the details of how to make it happen.<\/p>\n

Yet, the ability to programmatically instruct your database query comes in handy in several situations. After all, you know your own business logic \u2013 N1QL does not.<\/p>\n

Enter user-defined functions (UDFs)<\/em>. UDFs give you more control over a particular query and let you instruct the query language on how certain tasks are done. The Couchbase Server 7.0 release<\/a> includes user-defined functions for the N1QL query language.<\/p>\n

N1QL is rather agnostic as far as underlying programming languages go. Rather than specifying its own procedural language, N1QL employs a Language Manager<\/em>, meaning that it is already designed to support a multitude of guest languages.<\/p>\n

For now, the languages supported are inline<\/em>, an internal language that allows you to code any valid N1QL expression (including subqueries) and JavaScript<\/em>. Let\u2019s begin by comparing these two supported languages for N1QL UDFs.<\/p>\n

N1QL UDF Example: Basic Usage<\/h2>\n

In order to add your business logic, you have to create functions, like the inline<\/em> example below:<\/p>\n

CREATE FUNCTION add(arg1, arg2) { arg1 + arg2 }\r\n<\/pre>\n
<\/div>\n
You can then use your business logic freely wherever an expression is allowed, or directly through the `EXECUTE FUNCTION` statement:<\/p>\n
select add(1, 2)\r\nEXECUTE FUNCTION add(1, 2)\r\n<\/pre>\n
<\/div>\n
And when you no longer have a use for it, you just drop it:<\/p>\n
DROP FUNCTION add\r\n<\/pre>\n
<\/div>\n
N1QL UDF Example: JavaScript<\/h2>\n
Creating and dropping JavaScript user-defined functions is slightly more convoluted, for reasons that will become apparent in the next section.<\/p>\n
JavaScript UDFs are technically external<\/em> functions. That\u2019s because they\u2019re written in a different language and are executed in process different from the N1QL Query Service.<\/p>\n
Your first step is to create the JavaScript code:<\/p>\n
curl -v -X POST https:\/\/localhost:8093\/evaluator\/v1\/libraries\/math -u Administrator:password -d 'function add(a, b) { let data = a + b; return data; }'\r\n<\/pre>\n
<\/div>\n
Next, create the N1QL function:<\/p>\n
CREATE FUNCTION javascriptAdd(a, b) LANGUAGE javascript AS \"add\" AT \"math\"\r\n<\/pre>\n
<\/div>\n
Once created, you can create and drop JavaScript UDFs exactly like inline ones. When you no longer need the library, you can delete it using this command:<\/p>\n
curl -v -X DELETE https:\/\/localhost:8093\/evaluator\/v1\/libraries\/math -u Administrator:password\r\n<\/pre>\n
<\/div>\n
New in Couchbase 7.0: User-Defined Functions for Scopes & Collections<\/h2>\n
One of the major features of the Couchbase Server 7.0 release is the introduction of Scopes and Collections<\/a> to further help you organize your JSON<\/a> documents. Collections are storage units for a group of similar documents, and Scopes are larger storage units holding multiple Collections, allowing you to partition applications.<\/p>\n
With Scopes and Collections, 
\nCouchbase Server<\/a> \u2013 a document database \u2013 now offers all the strengths of the relational model \u2013 schema, tables, columns \u2013 without any of its weaknesses.<\/p>\n
Quick side-step:<\/em> In order to allow backward compatibility, the Query Service now has a `query_context` REST API parameter that indicates which Bucket and Scope should be used to resolve relative keyspace names, for example:<\/p>\n
cbq> \\set -query_context \"default:travel-sample.scope1\";\r\ncbq> select * from airlines;\r\n<\/pre>\n
<\/div>\n
In the statement above, the `SELECT` statement resolves `airlines` to the Collection named `default:travel-sample.scope1.airlines`.<\/p>\n
Okay, now back to user-defined functions:<\/p>\n
The logic behind Scopes and Collections with UDFs is that you could have a development, pre-production and production environments of the same application in three different Scopes, where three copies of the same application are deployed with the same logic. Conversely, you could have Scopes contain different deployments of the same application where different business logic is required (e.g., online shops where different discount or delivery structures are applied, or an accountancy application, where different taxation rules are applied).<\/p>\n
In the first case, having a global definition for individual functions suffices because the business logic is the same. But in order to support the second case, UDFs had to be partitioned across Scopes. Each Scope can have its own instance of the same UDF, each with different logic.<\/p>\n
It\u2019s also worth noting that Couchbase Server 7.0 does not force you to switch over to Scopes and Collections, so it was important that UDFs not be tied to the Collections feature.<\/p>\n
A Note on Global UDFs<\/h3>\n
Global UDFs are not dependent on Scopes, meaning that they are backward compatible with UDFs introduced in 6.5.<\/p>\n
If you are not using Collections, you don\u2019t have to do anything special to use them.
\nGlobal UDFs are referenced using a two-part fully qualified name, as below:<\/p>\n
CREATE FUNCTION default:bucket1.scope1.func1()  { 0 };\r\nEXECUTE FUNCTION default:bucket1.scope1.func1();\r\n<\/pre>\n
<\/div>\n
The Importance of the query_context Parameter<\/h3>\n
\u201cOkay, now I am confused,\u201d I hear you say. \u201cWhich functions were you using then in the previous examples? The names weren\u2019t fully qualified.\u201d<\/p>\n
The answer lies in the `query_context` REST API parameter setting: if the `query_context` is unset, the N1QL parser resolves the name to global<\/em> functions. If the `query_context` is set, the parser uses the `query_context` value to resolve the names to the relevant Scope functions, much like it did with non-qualified keyspace names.<\/p>\n
You can seamlessly deploy your application against Scopes or against Buckets just by changing the setting of the `query_context` REST API parameter.<\/p>\n
Object Resolution Inside Functions<\/h3>\n
When user-defined functions reference fully qualified objects, there\u2019s no ambiguity as to which object they mean:<\/p>\n
CREATE FUNCTION func { (SELECT * FROM default:bucket1.scope1.collection1) };\r\n<\/pre>\n
<\/div>\n
The next question is, how do functions resolve objects referenced inside them? For instance:<\/p>\n
CREATE FUNCTION func1() { func2() };\r\nCREATE FUNCTION default:func3 { (SELECT * FROM keyspace1) };\r\n<\/pre>\n
<\/div>\n
The key here is the principle of least surprise: during execution, user-defined functions switch query context to the path under which they were created and always reference objects inside that path.<\/p>\n
It does not matter if you called them with a relative or a fully qualified path, a function called with the same parameters always returns the same results, which is taken from the same objects.<\/p>\n
In the first example above, `func2()` would resolve to a global or a Scope function depending on the `query_context` setting at creation time.<\/p>\n
For `func3()`, `keyspace1` would be the Bucket `keyspace1`.<\/p>\n
Mixing Global vs. Scope Functions<\/h3>\n
There\u2019s nothing to stop you from using global UDFs alongside the Collections feature: just use the fully qualified names.<\/p>\n
Similarly, if you want to use a Scope function created in a different Scope, all you have to do is reference it directly.<\/p>\n
JavaScript Functions, Revisited<\/h3>\n
Here\u2019s why JavaScript functions should be split into two parts \u2013 i.e., a body and a separate function definition:<\/p>\n