Cloudant Fundamentals 7/10

June 18, 2018 | Glynn Bird | Fundamentals Query

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:

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"
}

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.