Cloudant Fundamentals 8/10

June 25, 2018 | Glynn Bird | Fundamentals Query

In the previous part of this series we discovered the _find endpoint which allows us to formulate queries in JSON and ask Cloudant to answer them.

In this post, we’ll look to doing the same thing but using Node.js code. Again we’ll lean on the cloudant-quickstart library.

Making queries

Using the query we made last time, we can pass the selector directly to the query function of cloudant-quickstart object to get an array of matching documents back.

var q = {
  "dob": {
    "$lt": "1970-01-01"
  }
}

db.query(q).then(console.log)
// outputs an array of documents

We can do the same queries with additional clauses:

var q = {
  "$and": [
    {
      "dob": {
        "$lt": "1970-01-01"
      }
    },
    {
      "actor": "Marlon Brando"
    }
  ]
}
db.query(q).then(console.log)
// outputs an array of documents

Sorting

If we need to sort, we can pass the sort options in as a second parameter:

var q = {
  "$and": [
    {
      "dob": {
        "$lt": "1970-01-01"
      }
    },
    {
      "actor": "Marlon Brando"
    }
  ]
}
db.query(q, { sort: { 'date': 'asc'} }).then(console.log)
// outputs an a sorted array of documents

SQL too

If you prefer to express your queries in Structured Query Language (SQL), then the cloudant-quickstart library can understand that too:

var q = "SELECT * from newdb WHERE dob < '1970-01-01' AND actor = 'Marlon Brando' SORY BY date"
db.query(q).then(console.log)

The cloudant-quickstart converts your SQL into the equivalent Cloudant Query object for you. You can see the object by calling the explain function:

console.log(db.explain(q))
// { selector: { "$and": [ { "dob":  { "$lt": "1970-01-01" },  { "actor": "Marlon Brando" } ] }, sort: { 'date': 'asc'} }

To be clear, this SQL-to-query conversion only works for non-aggregating SELECT queries, but it is useful way to express your query and to understand what the equivalent Cloudant Query looks like.

Next time

Querying is only one half of the story. In order to have your queries run efficiently, your database needs its data indexing too. We’ll see what that means in the next installment.