Mango search in Arrays

Question

My document has a structure like this:

{
  "Calibration": {
    "Presettings": {
      "Date": [
        {
          "Value": "2016-09-02 10:11",
          "Type": "generated"
        },
        {
          "Value": "2016-09-05",
          "Type": "schedule",
          "Duration": "5"
        }
      ]
    }
  }
}

How must I define the selector part of a query object to get all documents with dates (Value) less or equal to a given date and with Type=='generated'?

Adriaadriaens

Answer 1

First, you need to create your index. I suggest that you create an index on the Calibration.Presettings.Date field.

You can use the following JSON object to create it:

{
  "index": {
    "fields": [
      "_id",
      "Calibration.Presettings.Date.[].Type"
    ]
  },
  "type": "json"
}

So the selector would be like this :

{
  "selector": {
    "Calibration.Presettings.Date": {
      "$elemMatch": {
        "$and": [
          {
            "Type": "generated"
          },
          {
            "Value": {
              "$gte": "2016-09-01"
            }
          }
        ]
      }
    }
  }
}

We execute the query on the field Calibration.Pressettings.Date which is an Array. Since it's an array, we have to use the $elemMatch operator.

Then, we have a $and condition for the Value and the Type.

The Type of the Date has to be generated. With can either use the $eq operator or simply use this simple syntax: {"field":"value"}.

Finally, the Date`s Value must be greater or equal to X date. We can use the $gte operator.

Jota

Many thanks. This helps and I understand much more than before. – Adriaadriaens

Please accept this answer if it helped you. It may help others in the futur – Verticillaster

Where did you find documentation for the notation used to index arrays? In my own tests the index is never used in the query. – Cookout

I used cloudant documentation which is pretty similar to CouchDB. I know that there are some bugs with index selection with CouchDB 2.1. Perhaps you should post another question or create an issue on the Couch repo – Verticillaster

When I do that my answer in the selector query is: "warning": "no matching index found, create an index to optimize query time" ¿so what is wrong? – Dulci

It means that the query planner can't find an index usable for your query. Either your query is too complex or the index wrong. – Verticillaster

This query will work, but like @Cookout and Julian pointed out, I don't think that the index will be used. Quoting the Cloudant documentation : « However, for IBM Cloudant Query indexes of type json, you can't use "combination" or "array logical" operators [...], Only the equality operators such as $eq, $gt, $gte, $lt, and $lte - but not $ne - can be used as the basis of a more complex query. » For this index to work you'll need to use "text" indexes, or create a view that emit a key for each element of the array. – Highams