Function: Basic N1QL Prepared Select Statment
Goal: Iterate through a basic N1QL SELECT where Eventing interacts with the Data service via a prepared N1QL statement.
-
This function basicN1qlPreparedSelectStmt demonstrates how use a prepared N1QL SELECT statement with a passed parameter.
-
Typically, this is done for greater performance, the cluster will typically not prepare a statement if it is already prepared.
-
We have just one positional parameter $1 for "doc.iata", if we had a second parameter we would use the placeholder $2, and so on. Note positional parameters are passed in an array.
-
A commented out example of using named parameters is also shown. Note named parameters are passed and an object.
-
Requires the "travel-sample" sample dataset to be loaded.
-
Requires a metadata bucket, a source bucket of "travel-sample".
-
Deploy the Function with a Feed Boundary "From now" (Note you will log 187 lines if you use "Everything").
-
Assuming you deployed "From now" mutate any document in "travel-sample" to generate a log line.
-
For additional details refer to N1QL Statements and The N1QL() function call
// Deploy from now then mutate a document in `travel-sample`
function OnUpdate(doc, meta) {
// ignore information we don't care about
if (doc.type !== 'airline') return;
var route_cnt = 0; // we want to get the total routes per iata
// positional parameter(s)
var results = N1QL("SELECT COUNT(*) AS cnt " +
"FROM `travel-sample` " +
"WHERE type = \"route\" AND airline = $1",
[doc.iata], { isPrepared: true }
);
/*
// named parameter(s)
var max_dist = 120;
var results = N1QL("SELECT COUNT(*) AS cnt " +
"FROM `travel-sample` WHERE type = $mytype " +
"AND airline = $myairline AND distance <= $mydistance",
{ '$mytype': 'route', '$mydistance': max_dist, '$myairline': doc.iata },
{ 'consistency': 'none', isPrepared: true }
);
*/
for (var item of results) { // Stream results using 'for' iterator.
route_cnt = item.cnt;
}
results.close(); // End the query and free resources held
// Just log the KEY, AIRLINE and ROUTE_CNT it to the Application log
log("key: " + meta.id + ", airline: "+doc.iata+", route_cnt: "+route_cnt);
}
UPDATE `travel-sample` USE KEYS "airline_24" SET id = 24;
2020-08-25T14:19:13.589-07:00 [INFO] "key: airline_24, airline: AA, route_cnt: 2354"