Query based on ranges applied to each array element

[Estanislao Oubel]

Hello,

My documents have the following form:

{

    "_id" : 1

    "Features" : [0.5, 10, 4, 2.5, 0.001 ]

}

I would like to recover all the documents for which the first element of "Features" is in the range [0,5], the second one in the range [2,20], and so on. In other words, I need to define a specific range for each element in "Features". 

Is this possible in mongodb? If so, could you please provide an example of query?

Thanks!

Stan

[John De Goes]

In SlamData, you would express this query with something like:

SELECT * FROM collection 

  WHERE Features[0] BETWEEN 0 AND 5 AND

        Features[1] BETWEEN 2 AND 20

Note that MongoDB currently lacks a way to efficiently execute this query in the aggregation pipeline, because 3.0 is missing an array element selection operator. So if you execute this query in SlamData, it will fallback to MapReduce (and there's no way I'm posting that code, you can check out the compilation using the tool).

That said, 3.2 will have an $arrayElemAt operator which can efficiently express such queries.

Regards,

John

[Wan Bachtiar]

Hi Estanislao,

How many elements are expected to be in the ‘Features’ array ?

I would recommend to modify the document schema to convert the ‘Features’ field from an array into subdocuments.

For example:

{
    "_id" : 1
    "Features" : {
        { "A" : 0.5 },
        { "B" : 10 },
        { "C" : 4 }, 
        { "D" : 2.5 }, 
        { "E" : 0.001 }
    }
}

This schema would allow you to name the elements rather than relying on array position, which also would simplify your queries.

In this schema, your query would be expressed :

db.collection.find( {
     /* Features A is greater than equal than 0 AND less than equal than 5*/
     "Features.A" : 
        { $gte: 0, $lte: 5 }, 

      /* Features B is greater than equal than 2 AND less than 20*/
      "Features.B": 
        { $gte: 2, $lt: 20 },

      /* Features E is greater than 20 AND less than 25*/
      "Features.E": 
        { $gt: 20, $lt: 25 }
    })

Any documents interactions would also be more informative i.e. { Features.C: 99 }

Please see MongoDB Data Modeling documentation for more information and examples.

Is this possible in mongodb? If so, could you please provide an example of query?

Given the assumption that ‘Features’ array elements are consistent in length. i.e. the ‘Features’ field always contain 5 number of elements, and exists for all documents.

Your example query would look like below :

db.collection.find( {
     /* First element of Features is greater than equal than 0 AND less than equal than 5*/
     "Features.0" : 
        { $gte: 0, $lte: 5 }, 

      /* Second element of Features is greater than equal than 2 AND less than 20*/
      "Features.1": 
        { $gte: 2, $lt: 20 },

      /* Third element of Features is greater than 20 AND less than 25*/
      "Features.2": 
        { $gt: 20, $lt: 25 }
    })

As stated above, I would recommend to modify your schema for clarity.

Kind Regards,

Wan.

​