Jordan Soo Yen Yih

Posted on Oct 31, 2021

Query in Apache CouchDB: Mango Query

#database #node #beginners

In previous articles, we talked about design documents and how to use views to query in CouchDB. Besides Javascript query server, CouchDB also has a built-in Mango query server for us to query documents. Therefore in this article, I will talk about what is Mango Query, and when to use Mango Query?

What is Mango 🥭?

Mango is a MongoDB inspired query language interface for Apache CouchDB.

Mango provides a single HTTP API endpoint that accepts JSON bodies via HTTP POST. These bodies provide a set of instructions that will be handled with the results being returned to the client in the same order as they were specified. The general principle of this API is to be simple to implement on the client side while providing users a more natural conversion to Apache CouchDB than would otherwise exist using the standard RESTful HTTP interface that already exists.

How to use mango query in CouchDB?

We use back the same use case example in previous articles (A list of blog posts):

[
  {
    _id: "c2ec3b79-d9ac-45a8-8c68-0f05cb3adfac",
    title: "\"Post One Title\","
    content: "Post one content.",
    author: "John Doe",
    status: "submitted",
    date: "2021-10-30T14:57:05.547Z",
    type: "post"
  },
  {
    _id: "ea885d7d-7af2-4858-b7bf-6fd01bcd4544",
    title: "\"Post Two Title\","
    content: "Post two content.",
    author: "Jane Doe",
    status: "draft",
    date: "2021-09-29T08:37:05.547Z",
    type: "post"
  },
  {
    _id: "4a2348ca-f27c-427f-a490-e29f2a64fdf2",
    title: "\"Post Three Title\","
    content: "Post three content.",
    author: "John Doe",
    status: "submitted",
    date: "2021-08-02T05:31:05.547Z",
    type: "post"
  },
  ...
]

If we want to query the posts with status draft, we can define the mango query as below:

{
    "selector": {
        "status": { "$eq": "draft" }
    },
    "fields": ["_id", "_rev", "title", "content", "date", "author"],
    "sort": [],
    "limit": 10,
    "skip": 0,
    "execution_stats": true
}

Let's us break down line by line before we submit our mango query.

1. Selector 🔎

This is the place you define your query condition, you can give it a document property key that you want to query and the result. With the example above we want to query documents with status "draft", so we can make use of the operator equal $eq .

"selector": {
   "status": { "$eq": "draft" }
}

// If it is an equal operator, we also can define as below too

"selector": {
   "status": "draft"
}

2. Fields 🎁

Sometimes you might just required a property value, or your document might be a big JSON document or you are working for mobile client that you want to optimize the query result download size. Therefore, fields is handy for us to tell CouchDB just return what property fields to us. Just like GraphQL, get what you needed.

"fields": ["_id", "_rev", "title", "content", "date", "author"],

Tips: Fields is Optional, if you didn't define fields, CouchDB will just return the whole document to you.

3. Sort, Limit, Skip

These are normal useful feature that you can do in other normal database. They are optional too.

"sort": [],
"limit": 10,
"skip": 0,

*Note: For limit by default is 25, however there is an internal maximum limit which is around 250 number of documents with a Mango Query request. Therefore, if you didn't define the limit or even set the limit to 1k, it will still return around 250 documents. To solve this issue, either use CouchDB Views for this particular query or use Bookmark (We will talk about bookmark later).

4. Execution Statistics

This is a nice feature for developer to know the basic execution statistics for the specific mango query request. It is Optional too.

And then we can post our mango query to

POST /{YOUR_DATABASE_NAME}/_find

Result:

{ 
   "docs": [
     {
         "_id": "ea885d7d-7af2-4858-b7bf-6fd01bcd4544",
         "_rev": "1-f9397a0bc5b6150270b5309db35ec4b9",
         "title": "Post Two Title",
         "content": "Post two content.",
         "date":"2021-09-29T08:37:05.547Z",
         "author":"Jane Doe"
      }
    ],
    "bookmark": "g1AAAAB4eJzLYWBgYMpgSmHgKy5JLCrJTq2MT8lPzkzJBYqrpCZaWJimmKfomiemGemaWJha6CaZJ6XpmqWlGBgmJaeYmJqYgPRywPQSrSsLAKuSIMM",
    "execution_stats": {
       "total_keys_examined":0,
       "total_docs_examined":1,
       "total_quorum_docs_examined":0,
       "results_returned":1,
       "execution_time_ms":2.253
    },
    "warning": "no matching index found, create an index to optimize query time"
}

Let's us take a look at the result.

1. Docs 📃

Here is the result we got from Mango Query.

"docs": [
     {
         "_id": "ea885d7d-7af2-4858-b7bf-6fd01bcd4544",
         "_rev": "1-f9397a0bc5b6150270b5309db35ec4b9",
         "title": "Post Two Title",
         "content": "Post two content.",
         "date":"2021-09-29T08:37:05.547Z",
         "author":"Jane Doe"
      }
    ],

2. Bookmark 🔖

This is the bookmark we mentioned earlier. Bookmark from official document is

A string that enables us to specify which page of results we require. Used for paging through result sets. Every query returns an opaque string under the bookmark key that can then be passed back in a query to get the next page of results. If any part of the selector query changes between requests, the results are undefined.

"bookmark": "g1AAAAB4eJzLYWBgYMpgSmHgKy5JLCrJTq2MT8lPzkzJBYqrpCZaWJimmKfomiemGemaWJha6CaZJ6XpmqWlGBgmJaeYmJqYgPRywPQSrSsLAKuSIMM"

As I mentioned earlier there is a maximum number of documents for the CouchDB Mango Query return result per request. So if you have result more than 250 and you want the next page result starts from 251, we can just get the current bookmark and put into our next Mango query.

For example:

{
    "selector": {
        "status": { "$eq": "draft" }
    },
    "bookmark": "g1AAAAB4eJzLYWBgYMpgSmHgKy5JLCrJTq2MT8lPzkzJBYqrpCZaWJimmKfomiemGemaWJha6CaZJ6XpmqWlGBgmJaeYmJqYgPRywPQSrSsLAKuSIMM"
}

3. Execution Statistics Result

As our above Mango Query "execution_stats" is set to true, so CouchDB will return the execution statistic report of this mango query request.

"execution_stats": {
   "total_keys_examined":0,
   "total_docs_examined":1,
   "total_quorum_docs_examined":0,
   "results_returned":1,
   "execution_time_ms":2.253
},

4. Warning

This is a kindly reminder from CouchDB that we didn't create an index for this mango query. Just like any other databases. It's always recommended that to create an appropriate index when deploying in production.

"warning": "no matching index found, create an index to optimize query time"

How to create a Mango Index?

Since we are getting the reminder from the above example, now we can create a Mango Index to optimize the query above. This is how a Mango Index looks like:

{
    "index": {
        "fields": ["status"]
    },
    "ddoc" : "posts-by-status",
    "type" : "json"
}

POST /{YOUR_DATABASE_NAME}/_index

After created our index, just define the design document name of the mango index in our mango query.

{
    "selector": {
        "status": { "$eq": "draft" }
    },
    "use_index": "posts-by-status"
}

Then you will no longer see the "warning" message from the return result.

Tips: To check or debug whether your mango index has create/use properly. use /{YOUR_DATABASE_NAME}/_explain endpoint for your mango query.

Another Tips: If you wish to index all fields of your document. You can define fields with empty array when creating the mango index.

When to use Mango Query or CouchDB Views?

For my opinion, I personally think that Mango Query is useful for ad-hoc search / sort / filtering. CouchDB Views is useful for reporting/statistics involve Sum, Count, Median or fixed recurring query. Therefore, depending on your requirement to pick which is the most suitable. But most of the time you will be using both of them within a project.

In Conclusion.

This is a simple guide on using Mango Query in Apache CouchDB. Hope you find these useful. Actually there are more you can do with Mango Query. Check it out.

Thank you for reading. 😊