Using CouchDB Mango Queries: Create Indexes, Run Queries, and Avoid Common Pitfalls

Why Mango Queries Matter

When you need to filter or sort documents in CouchDB, the native way is to use Mango – a declarative JSON‑based query language that replaces the older Map/Reduce view syntax for most use cases. The key to performance is creating a JSON index first; otherwise CouchDB will either error out or perform a full collection scan, which is unacceptable for production workloads.

Step‑by‑Step: Define an Index and Run a Query

1. Start a CouchDB instance (e.g., Docker):

   docker run -d -p 5984:5984 --name couchdb -e COUCHDB_USER=admin -e COUCHDB_PASSWORD=admin couchdb:3
   

2. Create a database (replace movies with your name):

   curl -X PUT http://127.0.0.1:5984/movies
   

3. Define a JSON index in a design document. The index must list the fields you want to query or sort on. CouchDB stores indexes in the _design namespace.

   curl -X PUT http://127.0.0.1:5984/movies/_design/movie_idx \
        -H "Content-Type: application/json" \
        -d '{"index":{"fields":["title","year"]},"type":"json"}'
   

   After the request, CouchDB will asynchronously build the index. For small datasets the index is ready in a few seconds; for large collections it can take longer.

4. Run a Mango query that uses the index. Specify the selector (filter), sort, and pagination options.

   curl -X POST http://127.0.0.1:5984/movies/_find \
        -H "Content-Type: application/json" \
        -d '{"selector":{"year":{"$gt":2000}},"sort":[{"year":"asc"}],"limit":5}'
   

   Check the response headers for X-CouchDB-Index-Id – its presence confirms that CouchDB used the index you created. If the header is missing, the database performed a full scan.

5. Verify index usage in Fauxton (optional). Open http://127.0.0.1:5984/_utils, navigate to the database, click “Indexes”, and confirm that movie_idx appears under “JSON indexes”.

Limitations and Common Mistakes

• Missing Index: Running a query without an index returns a 400 error or causes a full scan. Always create the index first.
• Eventual Consistency: Newly inserted or updated documents may not appear in a query until the index is refreshed. For real‑time use, allow a brief delay or trigger a manual index refresh.
• Deep Pagination: Using skip for large offsets consumes memory and CPU. Prefer bookmark‑based or key‑based pagination (e.g., startkey and endkey).
• No Joins or Aggregations: Mango does not support multi‑document joins or complex aggregation functions. Embed related data or perform multiple queries if needed.
• $text Operator: Full‑text search requires the search plugin and is limited to simple term queries. For advanced search, integrate a dedicated engine like Lucene or ElasticSearch.

Practical Checklist

• Define a JSON index covering all fields used in selector and sort.
• Inspect X-CouchDB-Index-Id header after a query.
• Use limit and skip sparingly; prefer key-based pagination.
• Monitor index build time on large datasets and consider /_index/_design/_view/ for manual refresh.
• Plan for eventual consistency if your application requires immediate visibility.