Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

A newer version of this documentation is available.

View Latest

Function: Basic N1QL Prepared Select Statment

    March 16, 2025
    + 12

    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 Eventing Storage (or metadata collection) and a "source" collection of travel-sample.inventory.airline.

    • 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

    • [Optional] if Feed Boundary is "Everything" you can use SQL++ (N1QL) to add an index for performance:

      • CREATE INDEX adv_airline_type ON default:`travel-sample`.inventory.route(airline) WHERE (type = 'route')

    javascript
    Copy
    // To run configure the settings for this Function, basicN1qlPreparedSelectStmt, as follows:
//
// Version 7.1+
//   "Function Scope"
//     *.* (or try travel-sample.inventory if non-privileged)
// Version 7.0+
//   "Listen to Location"
//     travel-sample.inventory.airline
//   "Eventing Storage"
//     rr100.eventing.metadata
//   Binding(s) - none
//
// Version 6.X
//   "Source Bucket"
//     travel-sample
//   "MetaData Bucket"
//     metadata
//   Binding(s) - none

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`.`inventory`.`route` " +
        "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`.`inventory`.`route` 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);
}