$expr query operator does not seem to work with array dot notation

Question

I need to find documents in a collection matching a comparison between two dates, one of them being in an array in the document.

I found a solution using a combination of $arrayElemAt with aggregation pipeline but stand frustrated with the problem.

I originally thought the problem came from how I used the $lte operator with Date fields but I managed to narrow down the issue to a much simpler reproducible one.

db.getCollection('test-expr').insert({
    "value1" : 1,
    "value2" : 1,
    "values" : [ 
        1
    ]
})

Now trying to find the document with $expr:

db.getCollection('test-expr').find({$expr: {$eq: ["$value1", "$value2"]}})

=> Returns the document I just inserted.

Trying to compare the first element of the array using dot notation:

db.getCollection('test-expr').find({$expr: {$eq: ["$value1", "$values.0"]}})

=> Returns nothing.

I think maybe the dot notation doesn't work in $expr but I couldn't find anything pointing that out in the MongoDB documentation.

As mentioned before, I have found solutions to answer my problem but would like to understand why it cannot be achieved this way.

Am I missing something?

Read about $in: docs.mongodb.com/manual/reference/operator/aggregation/in/… — Dheemanth Bhat
– Dheemanth Bhat, Commented Mar 10, 2021 at 9:13
@DheemanthBhat thanks for your insight. I actually have multiple solutions to this problem but would like to understand why it's not possible to do it this way. Will edit the question to emphasize this point. — stephane-ruhlmann
– stephane-ruhlmann, Commented Mar 10, 2021 at 9:28
Does this answer your question? Does the `$eq` operator work with array dot notation? — Dheemanth Bhat
– Dheemanth Bhat, Commented Mar 10, 2021 at 9:44
It's actually the same issue, thanks, but it does not provide an explanation for why dot notation does not work though Mongo docs says otherwise. — stephane-ruhlmann
– stephane-ruhlmann, Commented Mar 10, 2021 at 10:44

prasad_ · Accepted Answer · 2021-03-10 10:56:42Z

The $expr allows usage of Aggregation Expression Operators only. The dot notation you are using will not work to access the array element for the field "values" : [ 1 ]. You need to use the $arrayElemAt operator, and it works fine.

Your code find({$expr: {$eq: ["$value1", "$value2"]}}) worked, because the $expr used the aggregation expression operator $eq, not the MongoDB Query Language (MQL)'s operator $eq. Note both the operators look alike, but the usage and syntax is different.

And, the code find({$expr: {$eq: ["$value1", "$values.0"]}}) did not work - as expected. In the aggregation operator the $values.0, the 0 is interpreted as field name, not an index of an array field.

I think maybe the dot notation doesn't work in $expr but I couldn't find anything pointing that out in the MongoDB documentation.

The dot notation works fine in $expr also. Here is an example, with sample document:

{ "_id" : 1, "val" : { "0" : 99, "a" : 11 } }

Now using the $expr and dot notation:

db.test.find({ $expr: { $eq: [ "$val.0", 99 ]  } } )
db.test.find({ $expr: { $eq: [ "$val.a", 11 ]  } } )

Both the queries return the document - the match happens with the filter using the $expr and the dot notation. But, this is valid with embedded (or sub) documents only not with array fields.

Form the documentation, Aggregation Pipeline Operators says:

These expression operators are available to construct expressions for use in the aggregation pipeline stages.

expressions:

Expressions can include field paths, literals, system variables, expression objects, and expression operators. Expressions can be nested.

Field Paths

Aggregation expressions use field path to access fields in the input documents. To specify a field path, prefix the field name or the dotted field name (if the field is in the embedded document) with a dollar sign $. For example, "$user" to specify the field path for the user field or "$user.name" to specify the field path to "user.name" field.

Thank you prasad for a such a detailed explanation. This is exactly what I did not get.

Collectives™ on Stack Overflow

$expr query operator does not seem to work with array dot notation

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