SPEC - Array query

[amorton]

This also should define how to handle these queries, which are like $elemMatch which we dont want to do:

• {{{ foo: { $gt: 25 } }}} foo is an array with one number > 25
• {{{ foo: { $gt: 15, $lt: 20 } }}} foo is an array with one number > 15, one less than 20, and it could be two different items or the same
• {{{ "foo.1": { $gt: 25 } }}} foo is an array and the second item is a number > 25

[vkarpov15]

I wasn't aware that MongoDB drills into arrays for operators like $gt, but I suppose that isn't surprising given that they do for $eq. Drilling into arrays in queries is fairly important, but doing so for query operators other than $eq is less important IMO.

Here's mongo shell output for 1:

MongoDB Enterprise > db.tests.insertOne({ foo: [26] })
{
    "acknowledged" : true,
    "insertedId" : ObjectId("63ebf98b96e0bf387a1be749")
}
MongoDB Enterprise > db.tests.findOne({ foo: { $gt: 25 } })
{ "_id" : ObjectId("63ebf98b96e0bf387a1be749"), "foo" : [ 26 ] }
MongoDB Enterprise > db.tests.findOne({ foo: { $lt: 27 } })
{ "_id" : ObjectId("63ebf98b96e0bf387a1be749"), "foo" : [ 26 ] }
MongoDB Enterprise > 

Here's mongo shell output for 2 & 3:

MongoDB Enterprise > db.tests.insertOne({ foo: [21, 14] })
{
    "acknowledged" : true,
    "insertedId" : ObjectId("63ebf9ce96e0bf387a1be74a")
}
MongoDB Enterprise > db.tests.findOne({ foo: { $gt: 15, $lt: 20 } })
{ "_id" : ObjectId("63ebf9ce96e0bf387a1be74a"), "foo" : [ 21, 14 ] }
MongoDB Enterprise > db.tests.findOne({ 'foo.1': { $gt: 13 } })
{ "_id" : ObjectId("63ebf9ce96e0bf387a1be74a"), "foo" : [ 21, 14 ] }
MongoDB Enterprise > db.tests.findOne({ 'foo.1': { $gt: 15 } })
null
MongoDB Enterprise > 

[maheshrajamani]

MongoDB Enterprise > db.tests.findOne({ foo: { $gt: 15, $lt: 20 } }) { "_id" : ObjectId("63ebf9ce96e0bf387a1be74a"), "foo" : [ 21, 14 ] } @vkarpov15 None of the elements in the array satisfies both $gt and $lt, not sure how its evaluated to return this document

[vkarpov15]

@maheshrajamani that's exactly why I chose that example. It looks like the proper way to read db.tests.findOne({ foo: { $gt: 15, $lt: 20 } }) is "find any documents in 'tests' collection where 'foo' contains an element that is >= 15, and an element that is <= 20". They don't necessarily need to be the same element.

Below is a related case with subdocument arrays. Notice that the query finds documents where there is at least one coordinate with x = 1, and where there is at least one coordinate with y = 1. But they don't need to be the same coordinate.

MongoDB Enterprise > db.test.insertOne({ coordinates: [{ x: 0, y: 1 }, { x: 1, y: 0 }] })
{
    "acknowledged" : true,
    "insertedId" : ObjectId("63ee96ba2ba8deac98145cd6")
}
MongoDB Enterprise > db.test.findOne({ 'coordinates.x': 1, 'coordinates.y': 1 })
{
    "_id" : ObjectId("63ee96ba2ba8deac98145cd6"),
    "coordinates" : [
        {
            "x" : 0,
            "y" : 1
        },
        {
            "x" : 1,
            "y" : 0
        }
    ]
}
MongoDB Enterprise > 

The special handling for array queries in MongoDB behaves like an "or" on the individual array elements. That's why $elemMatch exists. In order to find documents where foo contains an element that is both >= 15, and <= 20, you would do db.tests.findOne({ foo: { $elemMatch: { $gt: 15, $lt: 20 } } })

[sync-by-unito[bot]]

➤ Mahesh Rajamani commented:

Mahesh Rajamani can help on this.