1.3.6. /db/_find

POST /{db}/_find

Find documents using a declarative JSON querying syntax. Queries can use the built-in _all_docs index or custom indexes, specified using the _index endpoint.

The limit and skip values are exactly as you would expect. While skip exists, it is not intended to be used for paging. The reason is that the bookmark feature is more efficient.

Request:

Example request body for finding documents using an index:

POST /movies/_find HTTP/1.1Accept: application/jsonContent-Type: application/jsonContent-Length: 168Host: localhost:5984{    "selector": {        "year": {"$gt": 2010}    },    "fields": ["_id", "_rev", "year", "title"],    "sort": [{"year": "asc"}],    "limit": 2,    "skip": 0,    "execution_stats": true}

Response:

Example response when finding documents using an index:

HTTP/1.1 200 OKCache-Control: must-revalidateContent-Type: application/jsonDate: Thu, 01 Sep 2016 15:41:53 GMTServer: CouchDB (Erlang OTP)Transfer-Encoding: chunked{    "docs": [        {            "_id": "176694",            "_rev": "1-54f8e950cc338d2385d9b0cda2fd918e",            "year": 2011,            "title": "The Tragedy of Man"        },        {            "_id": "780504",            "_rev": "1-5f14bab1a1e9ac3ebdf85905f47fb084",            "year": 2011,            "title": "Drive"        }    ],    "execution_stats": {        "total_keys_examined": 0,        "total_docs_examined": 200,        "total_quorum_docs_examined": 0,        "results_returned": 2,        "execution_time_ms": 5.52    }}

1.3.6.1. Selector Syntax

Selectors are expressed as a JSON object describing documents of interest. Within this structure, you can apply conditional logic using specially named fields.

Whilst selectors have some similarities with MongoDB query documents, these arise from a similarity of purpose and do not necessarily extend to commonality of function or result.

1.3.6.1.1. Selector Basics

Elementary selector syntax requires you to specify one or more fields, and the corresponding values required for those fields. This selector matches all documents whose “director” field has the value “Lars von Trier”.

{    "director": "Lars von Trier"}A simple selector, inspecting specific fields

"selector": {  "title": "Live And Let Die"},"fields": [  "title",  "cast"]

You can create more complex selector expressions by combining operators. For best performance, it is best to combine ‘combination’ or ‘array logical’ operators, such as $regex, with an equality operators such as $eq, $gt, $gte, $lt, and $lte (but not $ne). For more information about creating complex selector expressions, see creating selector expressions.

1.3.6.1.2. Selector with 2 fields

This selector matches any document with a name field containing "Paul", and that also has a location field with the value "Boston".

{    "name": "Paul",    "location": "Boston"}

1.3.6.1.3. Subfields

A more complex selector enables you to specify the values for field of nested objects, or subfields. For example, you might use a standard JSON structure for specifying a field and subfield.

Example of a field and subfield selector, using a standard JSON structure:

{    "imdb": {        "rating": 8    }}

An abbreviated equivalent uses a dot notation to combine the field and subfield names into a single name.

{    "imdb.rating": 8}

1.3.6.1.4. Operators

Operators are identified by the use of a dollar sign ($) prefix in the name field.

There are two core types of operators in the selector syntax:

• Combination operators
• Condition operators

In general, combination operators are applied at the topmost level of selection. They are used to combine conditions, or to create combinations of conditions, into one selector.

Every explicit operator has the form:

{"$operator": argument}

A selector without an explicit operator is considered to have an implicit operator. The exact implicit operator is determined by the structure of the selector expression.

1.3.6.1.5. Implicit Operators

There are two implicit operators:

• Equality
• And

In a selector, any field containing a JSON value, but that has no operators in it, is considered to be an equality condition. The implicit equality test applies also for fields and subfields.

Any JSON object that is not the argument to a condition operator is an implicit $and operator on each field.

In the below example, we use an operator to match any document, where the "year" field has a value greater than 2010:

{    "year": {        "$gt": 2010    }}

In this next example, there must be a field "director" in a matching document, and the field must have a value exactly equal to "Lars von Trier".

{    "director": "Lars von Trier"}

You can also make the equality operator explicit.

{    "director": {        "$eq": "Lars von Trier"    }}

In the next example using subfields, the required field "imdb" in a matching document must also have a subfield "rating" and the subfield must have a value equal to 8.

Example of implicit operator applied to a subfield test

{    "imdb": {        "rating": 8    }}

Again, you can make the equality operator explicit.

{    "imdb": {        "rating": { "$eq": 8 }    }}

An example of the $eq operator used with full text indexing

{  "selector": {    "year": {      "$eq": 2001    }  },  "sort": [    "title:string"  ],  "fields": [    "title"  ]}

An example of the $eq operator used with database indexed on the field "year"

{  "selector": {    "year": {      "$eq": 2001    }  },  "sort": [    "year"  ],  "fields": [    "year"  ]}

In this example, the field "director" must be present and contain the value "Lars von Trier" and the field "year" must exist and have the value 2003.

{    "director": "Lars von Trier",    "year": 2003}

You can make both the $and operator and the equality operator explicit.

Example of using explicit $and and $eq operators

{    "$and": [        {            "director": {                "$eq": "Lars von Trier"            }        },        {            "year": {                "$eq": 2003            }        }    ]}

1.3.6.1.6. Explicit Operators

All operators, apart from ‘Equality’ and ‘And’, must be stated explicitly.

1.3.6.1.7. Combination Operators

Combination operators are used to combine selectors. In addition to the common boolean operators found in most programming languages, there are three combination operators ($all, $elemMatch, and $allMatch) that help you work with JSON arrays and one that works with JSON maps ($keyMapMatch).

A combination operator takes a single argument. The argument is either another selector, or an array of selectors.

The list of combination operators:

The $and operator

$and operator used with two fields

{  "selector": {    "$and": [      {        "title": "Total Recall"      },      {        "year": {          "$in": [1984, 1991]        }      }    ]  },  "fields": [    "year",    "title",    "cast"  ]}

The $and operator matches if all the selectors in the array match. Below is an example using the primary index (_all_docs):

{    "$and": [        {            "_id": { "$gt": null }        },        {            "year": {                "$in": [2014, 2015]            }        }    ]}

The $or operator

The $or operator matches if any of the selectors in the array match. Below is an example used with an index on the field "year":

{    "year": 1977,    "$or": [        { "director": "George Lucas" },        { "director": "Steven Spielberg" }    ]}

The $not operator

The $not operator matches if the given selector does not match. Below is an example used with an index on the field "year":

{    "year": {        "$gte": 1900    },    "year": {        "$lte": 1903    },    "$not": {        "year": 1901    }}

The $nor operator

The $nor operator matches if the given selector does not match. Below is an example used with an index on the field "year":

{    "year": {        "$gte": 1900    },    "year": {        "$lte": 1910    },    "$nor": [        { "year": 1901 },        { "year": 1905 },        {  "year": 1907 }    ]}

The $all operator

The $all operator matches an array value if it contains all the elements of the argument array. Below is an example used with the primary index (_all_docs):

{    "_id": {        "$gt": null    },    "genre": {        "$all": ["Comedy","Short"]    }}

The $elemMatch operator

The $elemMatch operator matches and returns all documents that contain an array field with at least one element matching the supplied query criteria. Below is an example used with the primary index (_all_docs):

{    "_id": { "$gt": null },    "genre": {        "$elemMatch": {            "$eq": "Horror"        }    }}

The $allMatch operator

The $allMatch operator matches and returns all documents that contain an array field with all its elements matching the supplied query criteria. Below is an example used with the primary index (_all_docs):

{    "_id": { "$gt": null },    "genre": {        "$allMatch": {            "$eq": "Horror"        }    }}

The $keyMapMatch operator

The $keyMapMatch operator matches and returns all documents that contain a map that contains at least one key that matches all the specified query criteria. Below is an example used with the primary index (_all_docs):

{    "_id": { "$gt": null },    "cameras": {        "$keyMapMatch": {            "$eq": "secondary"        }    }}

1.3.6.1.8. Condition Operators

Condition operators are specific to a field, and are used to evaluate the value stored in that field. For instance, the basic $eq operator matches when the specified field contains a value that is equal to the supplied argument.

The basic equality and inequality operators common to most programming languages are supported. Strict type matching is used.

In addition, some ‘meta’ condition operators are available. Some condition operators accept any valid JSON content as the argument. Other condition operators require the argument to be in a specific JSON format.

1.3.6.1.9. Creating Selector Expressions

We have seen examples of combining selector expressions, such as using explicit $and and $eq operators.

In general, whenever you have an operator that takes an argument, that argument can itself be another operator with arguments of its own. This enables us to build up more complex selector expressions.

However, only equality operators such as $eq, $gt, $gte, $lt, and $lte (but not $ne) can be used as the basis of a query. You should include at least one of these in a selector.

For example, if you try to perform a query that attempts to match all documents that have a field called afieldname containing a value that begins with the letter A, this will trigger a warning because no index could be used and the database performs a full scan of the primary index:

Request

POST /movies/_find HTTP/1.1Accept: application/jsonContent-Type: application/jsonContent-Length: 112Host: localhost:5984{    "selector": {        "afieldname": {"$regex": "^A"}    }}

Response:

HTTP/1.1 200 OKCache-Control: must-revalidateContent-Type: application/jsonDate: Thu, 01 Sep 2016 17:25:51 GMTServer: CouchDB (Erlang OTP)Transfer-Encoding: chunked{    "warning":"no matching index found, create an index to optimize    query time",    "docs":[    ]}

Most selector expressions work exactly as you would expect for the given operator. But it is not always the case: for example, comparison of strings is done with ICU and can can give surprising results if you were expecting ASCII ordering. See Views Collation for more details.

1.3.6.2. Sort Syntax

The sort field contains a list of field name and direction pairs, expressed as a basic array. The first field name and direction pair is the topmost level of sort. The second pair, if provided, is the next level of sort.

The field can be any field, using dotted notation if desired for sub-document fields.

The direction value is "asc" for ascending, and "desc" for descending. If you omit the direction value, the default "asc" is used.

Example, sorting by 2 fields:

[{"fieldName1": "desc"}, {"fieldName2": "desc" }]

Example, sorting by 2 fields, assuming default direction for both :

["fieldNameA", "fieldNameB"]

A typical requirement is to search for some content using a selector, then to sort the results according to the specified field, in the required direction.

To use sorting, ensure that:

• At least one of the sort fields is included in the selector.
• There is an index already defined, with all the sort fields in the same order.
• Each object in the sort array has a single key.

If an object in the sort array does not have a single key, the resulting sort order is implementation specific and might change.

Find does not support multiple fields with different sort orders, so the directions must be either all ascending or all descending.

For field names in text search sorts, it is sometimes necessary for a field type to be specified, for example:

{ ":string": "asc"}

If possible, an attempt is made to discover the field type based on the selector. In ambiguous cases the field type must be provided explicitly.

The sorting order is undefined when fields contain different data types. This is an important difference between text and view indexes. Sorting behavior for fields with different data types might change in future versions.

A simple query, using sorting:

{    "selector": {"Actor_name": "Robert De Niro"},    "sort": [{"Actor_name": "asc"}, {"Movie_runtime": "asc"}]}

1.3.6.3. Filtering Fields

It is possible to specify exactly which fields are returned for a document when selecting from a database. The two advantages are:

• Your results are limited to only those parts of the document that are required for your application.
• A reduction in the size of the response.

The fields returned are specified as an array.

Only the specified filter fields are included, in the response. There is no automatic inclusion of the _id or other metadata fields when a field list is included.

Example of selective retrieval of fields from matching documents:

{    "selector": { "Actor_name": "Robert De Niro" },    "fields": ["Actor_name", "Movie_year", "_id", "_rev"]}

1.3.6.4. Pagination

Mango queries support pagination via the bookmark field. Every _find response contains a bookmark - a token that CouchDB uses to determine where to resume from when subsequent queries are made. To get the next set of query results, add the bookmark that was received in the previous response to your next request. Remember to keep the selector the same, otherwise you will receive unexpected results. To paginate backwards, you can use a previous bookmark to return the previous set of results.

Note that the presence of a bookmark doesn’t guarantee that there are more results. You can to test whether you have reached the end of the result set by comparing the number of results returned with the page size requested - if results returned total_keys_examined | Number of index keys examined. Currently always 0. | | total_docs_examined | Number of documents fetched from the database / index, equivalent to using include_docs=true in a view. These may then be filtered in-memory to further narrow down the result set based on the selector. | | total_quorum_docs_examined | Number of documents fetched from the database using an out-of-band document fetch. This is only non-zero when read quorum > 1 is specified in the query parameters. | | results_returned | Number of results returned from the query. Ideally this should not be significantly lower than the total documents / keys examined. | | execution_time_ms | Total execution time in milliseconds as measured by the database. |

1.3.7. /db/_index

Mango is a declarative JSON querying language for CouchDB databases. Mango wraps several index types, starting with the Primary Index out-of-the-box. Mango indexes, with index type json, are built using MapReduce Views.

POST /{db}/_index

Create a new index on a database

The Index object is a JSON object with the following fields:

Example of creating a new index for a field called foo:

Request:

POST /db/_index HTTP/1.1Content-Type: application/jsonContent-Length: 116Host: localhost:5984{    "index": {        "fields": ["foo"]    },    "name" : "foo-index",    "type" : "json"}

The returned JSON confirms the index has been created:

Response:

HTTP/1.1 200 OKCache-Control: must-revalidateContent-Length: 96Content-Type: application/jsonDate: Thu, 01 Sep 2016 18:17:48 GMTServer: CouchDB (Erlang OTP/18){    "result":"created",    "id":"_design/a5f4711fc9448864a13c81dc71e660b524d7410c",    "name":"foo-index"}

Example index creation using all available query parameters

Request:

POST /db/_index HTTP/1.1Content-Type: application/jsonContent-Length: 396Host: localhost:5984{    "index": {        "partial_filter_selector": {            "year": {                "$gt": 2010            },            "limit": 10,            "skip": 0        },        "fields": [            "_id",            "_rev",            "year",            "title"        ]    },    "ddoc": "example-ddoc",    "name": "example-index",    "type": "json",    "partitioned": false}

By default, a JSON index will include all documents that have the indexed fields present, including those which have null values.

1.3.7.1. Partial Indexes

Partial indexes allow documents to be filtered at indexing time, potentially offering significant performance improvements for query selectors that don’t map cleanly to a range query on an index.

Let’s look at an example query:

{  "selector": {    "status": {      "$ne": "archived"    },    "type": "user"  }}

Without a partial index, this requires a full index scan to find all the documents of "type":"user" that do not have a status of "archived". This is because a normal index can only be used to match contiguous rows, and the "$ne" operator cannot guarantee that.

To improve response times, we can create an index which excludes documents where "status": { "$ne": "archived" } at index time using the "partial_filter_selector" field:

POST /db/_index HTTP/1.1Content-Type: application/jsonContent-Length: 144Host: localhost:5984{  "index": {    "partial_filter_selector": {      "status": {        "$ne": "archived"      }    },    "fields": ["type"]  },  "ddoc" : "type-not-archived",  "type" : "json"}

Partial indexes are not currently used by the query planner unless specified by a "use_index" field, so we need to modify the original query:

{  "selector": {    "status": {      "$ne": "archived"    },    "type": "user"  },  "use_index": "type-not-archived"}

Technically, we don’t need to include the filter on the "status" field in the query selector - the partial index ensures this is always true - but including it makes the intent of the selector clearer and will make it easier to take advantage of future improvements to query planning (e.g. automatic selection of partial indexes).

GET /{db}/_index

When you make a GET request to /db/_index, you get a list of all indexes in the database. In addition to the information available through this API, indexes are also stored in design documents . Design documents are regular documents that have an ID starting with _design/. Design documents can be retrieved and modified in the same way as any other document, although this is not necessary when using Mango.

• Format of index objects:

ddoc: ID of the design document the index belongs to. This ID can be used to retrieve the design document containing the index, by making a GET request to /db/ddoc, where ddoc is the value of this field.

• name: Name of the index.
• type: Type of the index. Currently “json” is the only supported type.
• def: Definition of the index, containing the indexed fields and the sort order: ascending or descending.

Request:

GET /db/_index HTTP/1.1Accept: application/jsonHost: localhost:5984

Response:

HTTP/1.1 200 OKCache-Control: must-revalidateContent-Length: 238Content-Type: application/jsonDate: Thu, 01 Sep 2016 18:17:48 GMTServer: CouchDB (Erlang OTP/18){    "total_rows": 2,    "indexes": [    {        "ddoc": null,        "name": "_all_docs",        "type": "special",        "def": {            "fields": [                {                    "_id": "asc"                }            ]        }    },    {        "ddoc": "_design/a5f4711fc9448864a13c81dc71e660b524d7410c",        "name": "foo-index",        "type": "json",        "def": {            "fields": [                {                    "foo": "asc"                }            ]        }    }  ]}

DELETE /{db}/_index/{designdoc}/json/{name}

Request:

DELETE /db/_index/_design/a5f4711fc9448864a13c81dc71e660b524d7410c/json/foo-index HTTP/1.1Accept: */*Host: localhost:5984

Response:

HTTP/1.1 200 OKCache-Control: must-revalidateContent-Length: 12Content-Type: application/jsonDate: Thu, 01 Sep 2016 19:21:40 GMTServer: CouchDB (Erlang OTP/18){    "ok": true}

1.3.8. /db/_explain

POST /{db}/_explain

Shows which index is being used by the query. Parameters are the same as _find

Request:

POST /movies/_explain HTTP/1.1Accept: application/jsonContent-Type: application/jsonContent-Length: 168Host: localhost:5984{    "selector": {        "year": {"$gt": 2010}    },    "fields": ["_id", "_rev", "year", "title"],    "sort": [{"year": "asc"}],    "limit": 2,    "skip": 0}

Response:

HTTP/1.1 200 OKCache-Control: must-revalidateContent-Type: application/jsonDate: Thu, 01 Sep 2016 15:41:53 GMTServer: CouchDB (Erlang OTP)Transfer-Encoding: chunked{    "dbname": "movies",    "index": {        "ddoc": "_design/0d61d9177426b1e2aa8d0fe732ec6e506f5d443c",        "name": "0d61d9177426b1e2aa8d0fe732ec6e506f5d443c",        "type": "json",        "def": {            "fields": [                {                    "year": "asc"                }            ]        }    },    "selector": {        "year": {            "$gt": 2010        }    },    "opts": {        "use_index": [],        "bookmark": "nil",        "limit": 2,        "skip": 0,        "sort": {},        "fields": [            "_id",            "_rev",            "year",            "title"        ],        "r": [            49        ],        "conflicts": false    },    "limit": 2,    "skip": 0,    "fields": [        "_id",        "_rev",        "year",        "title"    ],    "range": {        "start_key": [            2010        ],        "end_key": [            {}        ]    }}

1.3.8.1. Index selection

_find chooses which index to use for responding to a query, unless you specify an index at query time.

The query planner looks at the selector section and finds the index with the closest match to operators and fields used in the query. If there are two or more json type indexes that match, the index with the smallest number of fields in the index is preferred. If there are still two or more candidate indexes, the index with the first alphabetical name is chosen.