Overview of indexes and queries in Cloudant
This video introduces you to the different types of indexes that you can create to query the data in your database, and explains the typical use cases for each type of index.
To learn more, read the indexes documentation.
Index Feature Comparison
|Feature||CRUD Document||Primary Index||View (MapReduce)||Search Index||Geospatial Index||Cloudant Query (json)||Cloudant Query (text)|
|Primary Key Lookup||X||X||X||X||X||X|
|Primary Key Range||X||X||X||X||X||X|
|Secondary Key Lookup||X||X||X||X|
|Secondary Key Range||X||X||X||X|
|List of Keys||X||X||X||X||X|
|Complex (ex: array) Keys||X||X|
|Adhoc lookup/range on multiple keys||X||X|
|Boolean operations (AND, OR, NOT, etc.)||X||X||X|
|Lucene: wildcards, fuzzy, boosting terms, proximity, facets, analyzers, etc.||X|
|Geo: bounding box||X||X|
|Geo: radius, ellipse, point, linestring, polygon, multipoint, multilinestring, multipolygon, geometrycollection||X|
|Joins (via Map-side “linked” keys)||X|
|Group by (count)||X||X|
|Group by range/facet||X|
|Group by (analytics)||X|
|Group by (hierarchical analytics → group_level)||X|
|Sort by key(s)||X||X||X||X||X|
|Adhoc sort on multiple keys||X||X|
|Sort by distance||X||X|
|200 results max||X||X||X|
|Stream large result set (2000+ docs)||X||X|
|Filter on fields at query time||X||X|
The Primary Index
The primary index is the fastest way to retrieve data from your database and is available out-of-the-box which means you don’t have to write any code before you can use it. Watch this short video to see how to query the primary index.
Before starting the tutorial, if you haven’t created a Cloudant account yet, complete the steps on the Get Started page.
Tutorial: Query the primary index
- The primary index, often referred to as _all_docs, returns an id, a key and a value for every document in the database. The id and key are the same (Cloudant makes an index keyed by doc id), while the value is the _rev of the document.
_all_docs also reports on the total number of documents and any offset used to query the index.
- Replicate the animaldb database from the http://education.cloudant.com account. If you need help creating the replication request, refer to the Replication video and Tutorial on the Create a Replication Job page.
- Execute the following query from a browser. You’ll find your accountname on the Services Credentials tab on the Cloudant service launch page in IBM Cloud.
All indexes are sorted by their key. The sort order is:
- text, cases sensitive – lower case first
- arrays, sorted element by element
The full specification is documented in the CouchDB Wiki.
Limit and Skip
The generic _all_docs request above returns all the documents in the database. That’s fine for this example database, but in a realistic scenario you’ll probably want a more manageable result set. That’s where API options come in.
- Limit: Add the limit parameter to keep your result set to a certain size. Issue the following query where you limit the result set to 2 rows.
- Skip:If you want to offset your result set (for example to paginate through some rows) you can also pass in a skip parameter. Issue the following query where you skip the first 3 rows.
Use slicing to pull out row ranges from the index by using start and end keys in your query.
- Here we are looking for animals with names that begin with letters greater than the startkey up to and including the endkey. Issue the following query:
- If you don’t want to include documents that match the end key, add the inclusive_end parameter with a value of false.
- View slicing with starkey and endkey can be combined with skip, limit and inclusive_end to further constrain your result set.
Single key matching
Cloudant’s primary index automatically turns a document’s _id into its key. If you want a document matching a single key, find it with the key parameter.
- Here, we’re looking for a document indexed with the key of “llama”.
- You can also hit the document directly, without additional parameters, at its unique URL. The result is similar to the single key request we made above, but different in that all fields are included in the result.
Retrieving document contents
Use include_docs=true when you want all of the contents of the document you’re requesting (not just the id).
- This API call uses include_docs=true along with limit and skip.
As we’ve seen, the _all_docs index can be a very useful view into your database, especially if your application has a natural unique identifier that you can use for your documents.
Back to top
As your data grows, you’ll want to explore Cloudant Query, which allow you to build additional indexes over your database.