6

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'?

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
rolfn
  • 163
  • 1
  • 4

1 Answers1

16

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.

Alexis Côté
  • 3,670
  • 2
  • 14
  • 30
  • Many thanks. This helps and I understand much more than before. – rolfn May 10 '17 at 17:47
  • Please accept this answer if it helped you. It may help others in the futur – Alexis Côté May 10 '17 at 19:30
  • Where did you find documentation for the notation used to index arrays? In my own tests the index is never used in the query. – Sam Jun 15 '18 at 04:51
  • I used [cloudant](https://console.bluemix.net/docs/services/Cloudant/api/cloudant_query.html#query) 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 – Alexis Côté Jun 16 '18 at 00:08
  • 1
    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? – Julian Solarte Jul 12 '19 at 13:40
  • 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. – Alexis Côté Jul 12 '19 at 15:14
  • This query will work, but like @Sam and Julian pointed out, I don't think that the index will be used. Quoting the [Cloudant documentation](https://cloud.ibm.com/docs/Cloudant?topic=Cloudant-query#selector-basics) : _« 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. – hulius Feb 11 '22 at 15:47