In previous posts we’ve looked add adding and retrieving documents from a Cloudant database by their key fields - the _id field. There’s a good chance that you want your database to be able to do more than that which is where querying comes in.

Making a query

A Cloudant Query allows questions to be asked of your Cloudant data, questions such as:

• get me all the documents where the dob field is less than 1970-01-01
• get me all the documents where the dob field is less than 1970-01-01 and the actor field is Marlon Brando
• get the first fifty films staring Matthew Broderick in date order
• get the next 50 films matching the previous query

Queries are expressed as JSON documents such as:

{
  "selector": {
    "dob": {
      "$lt": "1970-01-01"
    }
  }
}

The selector object is the equivalent of the “WHERE” part of relational database query. It defines the values or ranges of fields that you are looking for - in this case, the $lt (“less than”) operator is used to perform our first query (other operators are available)

To perform the query we simply POST the JSON to the /db/_find endpoint:

$ curl -X POST \
    -H'Content-type:application/json' \
    -d@query.json \
    "$URL/newdb/_find"

The returned data will have the following form:

{
  "docs":[ ],
  "bookmark": "",
  "warning": "no matching index found, create an index to optimize query time"
}

• docs is an array of matching documents
• bookmark unlocks access to the next page of matches
• warning is cautioning us that we are performing a query which foreces Cloudant to scan the whole database to answer. We can improve performance with an index, a feature we’ll meet later.

More complex clauses

Our second query needs to use the $and operator which is fed an array of clauses. The first clause is the same as our first query and we add on a second clause to match by actor name.

{
  "selector": {
    "$and": [
      {
        "dob": {
          "$lt": "1970-01-01"
        }
      },
      {
        "actor": "Marlon Brando"
      }
    ]
  }
}

Only documents matching all of the $and clauses will make it to the result set.

Sorting

The third query adds a sort attribute to the query object:

{
  "selector": {
    "actor": "Matthew Broderick"
  }
  "sort": {
    "date": "asc"
  }
}

We can sort by one or more fields in ascending (asc) or descending (desc) order.

Next time

In the next post we’ll do all this again, but programmatically in Node.js and introduce the prospect of expressing our queries in SQL.