N1QL to Node.js

Node.js SDK
1

How can we write the following N1QL query using node.js?

I’m using Express server and connected to Couchbase database. In the documentation it’s mentioned that N1QL is not the only query option in Couchbase. Please suggest.

select timestamp,
       ARRAY data FOR data in bucketName.data 
       when
          {data.stockSymbol,data.businessDate}={"stockSymbol":"ADBL","businessDate":"2020-02-11"}
          END
          as data 
    from
       bucketName
    WHERE
       ANY d IN data SATISFIES {d.stockSymbol,d.businessDate}={"stockSymbol":"ADBL","businessDate":"2020-02-11"}
       END;

My script for now is written below, I need to make the "filter" variable working. Currently, it returns me the entire dataset whereas I need data based on those filters

.

    function retrieveData(param, callback) {
        var filter = {
            stockSymbol: param.stockSymbol,
            businessDate: param.businessDate
        }
         bucket.get(`documentId`, filter, function (error, result) {
            if (error) {
                 callback(error, null);
                  return;
               }
         callback(null, { message: "success", data: result });
      })
}

Thank you in advance.
Ankit Lalan

2

I’m not certain I understand the question. Generally speaking, if you have a working N1QL query that meets your needs, you should just use that from the Node.js SDK. Why are you looking to do it another way?

From the API reference, there is no “filter”, but just an open set of options.

3

Yeah, I have a working query that works perfectly fine with the Node.js SDK. Writing SQL queries in my code base looks tedious, so wondering if there was a similar solution.

Here is the snippet:
queries.js

    import config from '../config';
    export default {
        n1qlFloorSheet: {
            filterBySymbolAndBusinessDate:
                `
                                select
                                timestamp,
                                ARRAY data FOR data in ${config.couchbase.bucket}.data
                                when
                                    data.stockSymbol = $stockSymbol
                                    AND data.businessDate = $businessDate
                                    END
                                    AS data
                                from
                                ${config.couchbase.bucket}
                                WHERE
                                ANY d IN data SATISFIES [d.stockSymbol,d.businessDate] = [$stockSymbol,$businessDate]
                                END;
                                `
        }
    }

model.js

import db from '../bin/server';
import query from '../couchbase/queries';
function RecordFloorsheet() { };

RecordFloorsheet.get = function (param, callback) {
    db.bucket.query(db.N1qlQuery.fromString(query.n1qlFloorSheet.filterBySymbolAndBusinessDate), param, function (err, rows) {
        if (err) {
            callback(err, null);
            return;
        }
        callback(null, rows);
    })
}
export default RecordFloorsheet;

Controller.js

import fsModel from '../../model/floorsheet';
function searchFloorSheet(req, res, next) {
    var filter = {
        businessDate: req.query.date,
        stockSymbol: req.query.sym
    }
    fsModel.get(filter, function (err, response) {
        if (err)
            res.status(500).send({ code: err.code, error: err.message })
        if (response.message = "success") {
            if (response.length == 0)
                res.send({ data: "no results found" });
            res.send(response[0]);
        }
    })
}

Thank you
Ankit Lalan