$expr not working when finding data in MongoDB matching an array value with a field

Question

I have written a query that looks like it should work but I can't figure out why it doesn't.

Firstly - I narrowed down a query to make sure it was actually working. the line with $expr in it proves that the fields are correct ( i.e. the same results show with or without it ) .

db.nzpostRecon.find(
    { $and: [
            {"eship.order.packages.0.tracking_number":"AB41045027010225"},
            {"ticketNumber":"AB41045027010225"},
            {$expr: { $eq: ["$this.eship.order.packages.0.tracking_number", "$this.ticketNumber"] }},
            {"eship.order.packages.0.tracking_number" : {$exists: true}}
            ]

However, when I remove the first 2 lines, the results don't make sense. When I run it I get some documents that have a value in the tracking_number field ( in the array ) - but the ticketNumber field is blank - so they are definitely not equal even though both fields exist. I'm baffled by this & think maybe my understanding of that part of the query is wrong.

db.nzpostRecon.find(
    { $and: [
            {$expr: { $eq: ["$this.eship.order.packages.0.tracking_number", "$this.ticketNumber"] }},
            {"eship.order.packages.0.tracking_number" : {$exists: true}}
            ]

$expr allows aggregation expression operators within the find query. — prasad_
– prasad_, Commented Apr 13, 2021 at 8:14
@prasad_ as explained in the question - it doesn't work as expected. — Martin Thompson
– Martin Thompson, Commented Apr 13, 2021 at 8:16
See this post with similar question: $expr query operator does not seem to work with array dot notation — prasad_
– prasad_, Commented Apr 13, 2021 at 8:16

Joe · Accepted Answer · 2021-04-14 01:45:17Z

0

There is no field named this so "$this.eship.order.packages.0.tracking_number" and "$this.ticketNumber" both evaluate to null or undefined and therefore match for every document, making that $expr always true.

The arrayfield.0.embeddedfield notation is part of the Mongo Query Language, and is not available in aggregation expressions.

If eship.order.packages is an array of objects, $eship.order.packages.tracking_number will be an array containing all of the tracking_number values from those objects, so you can use $arrayElemAt, like:

{$expr: { $eq: [{$arrayElemAt:["$eship.order.packages.tracking_number",0]}, "$ticketNumber"] }},

edited Apr 14, 2021 at 1:45

answered Apr 13, 2021 at 8:39

Joe

29k4 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Thompson Over a year ago

Perfect. Thank you! I have spent hours on this and what you said makes perfect sense - works exactly as expected now.

Collectives™ on Stack Overflow

$expr not working when finding data in MongoDB matching an array value with a field

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related