DEV Community

Cover image for Query in Apache CouchDB: Mango Query
Jordan Soo Yen Yih
Jordan Soo Yen Yih

Posted on

Query in Apache CouchDB: Mango Query

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"
  },
  ...
]
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

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"],
Enter fullscreen mode Exit fullscreen mode

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,
Enter fullscreen mode Exit fullscreen mode

*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
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

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"
      }
    ],
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

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
},
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode
POST /{YOUR_DATABASE_NAME}/_index
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

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. 😊

Top comments (2)

Collapse
 
hamidb80 profile image
Hamid Bluri

Hey, i made a library that you can write a mango query like SQL!

github.com/hamidb80/mycouch#sql-li...

Collapse
 
yenyih profile image
Jordan Soo Yen Yih

Nice~πŸ‘