[SERVER-43931] $elemMatch object can treat '0' as an array index Created: 10/Oct/19  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Ali Rahbari Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Sprint: Query 2019-11-04
Participants:

 Description   

 

Consider following document:

{
  items: [
    [1, [2]],
    [2, [1]],
    [3, [1]],
    [2, [1]],
  ]
}

I want to remove all items which their 0 index is 2.

db.col.updateMany(
    {},
    {
        $pull: {
            'items': {
                0: 2
            }
        }
    }
)

this does nothing.

db.col.update(
    {},
    {
        $pull: {
            'items': {
                $elemMatch: {
                    '0': 2
                }
            }
        }
    }
)

this oddly removes first item of items.

db.col.updateMany(
    {},
    {
        $pull: {
            'items': {
                $elemMatch: {
                    $in: [2]
                }
            }
        }
    }
)

this works as expected.

 



 Comments   
Comment by David Storch [ 22/Oct/19 ]

Thanks nicholas.zolnierz. I agree that it makes sense to consider as part of a larger project around positional path component.

Comment by Nicholas Zolnierz [ 21/Oct/19 ]

This appears to be another case of field name/array index ambiguity and implicit array traversal. Unfortunately, as others have stated, there's currently no way to use $elemMatch to match against a particular scalar value within an array. Since the $pull applies the $elemMatch query to each array within 'items', we can simplify the example to the following:

> db.test.find()                                                                                                                                                                                                                                           
{ "_id" : ObjectId("5daa05b1ed27cdc8347c1745"), "items" : [ 1, [ 2 ] ] }
{ "_id" : ObjectId("5daa05bded27cdc8347c1746"), "items" : [ { "0" : 2 } ] }
{ "_id" : ObjectId("5daa05cced27cdc8347c1747"), "items" : [ [ 2 ], 1 ] }
{ "_id" : ObjectId("5daa05eeed27cdc8347c1748"), "items" : [ 1, [ 1, 2 ] ] }
{ "_id" : ObjectId("5daa1e10ed27cdc8347c1749"), "items" : [ [ 1, 2 ] ] }
> db.test.find({items: {$elemMatch: {0: 2}}}, {_id: 0})                                                                                                                                                                                                    
{ "items" : [ 1, [ 2 ] ] }
{ "items" : [ { "0" : 2 } ] }
{ "items" : [ [ 2 ], 1 ] }

In the first query, the '0' in the $elemMatch predicate matches the first doc because the sub-array [2] has a value of 2 at the index 0. This is consistent with the "odd" example given in the description. Notice, however, that the '0' is also treated as a field name to match the second document. As such I don't think the title of this ticket is quite accurate.

david.storch I think this ticket is superseded by either SERVER-4753 or SERVER-28779, so perhaps we can hold it in the "Positional path syntax" project for now and consider consolidating later?

Comment by Carl Champain (Inactive) [ 10/Oct/19 ]

Hi rahbari@gmail.com,

Thanks for the report.
What you are trying to do with $pull is not possible. However, there is a way to accomplish the desired output using the pipeline syntax 4.2:

db.col.update(
     {},
     [ {$set:{items:{$filter:{input:"$items", cond:{$ne:[2,{$arrayElemAt:["$$this",0]}]} }}}}] 
)

$set replaces the value of items with the new filtered array. $filter returns the arrays where the element at index 0 is not equal to 2.

The '0':2 in $elemMatch is a bug though; the number 0 shouldn't be treated as an array index, it should be treated only as a field name. I'll pass this ticket along to the Query team for further consideration. Please watch it for updates.

Kind regards,
Carl

Generated at Thu Feb 08 05:04:30 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.