[SERVER-14203] Support for $elemMatch inside $in Created: 07/Jun/14  Updated: 06/Dec/22

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

Type: New Feature Priority: Major - P3
Reporter: Michael Delamere Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 7
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by SERVER-3544 support $elemMatch with $in Closed
Related
related to SERVER-589 query $operators should be composable Backlog
Assigned Teams:
Query Optimization
Participants:

 Description   

We allow $elemMatch clauses inside an $all as shown below, but we reject this for $in. We should allow an $in-$elemMatch query which says "the document must match one of these $elemMatch clauses".

Original Description

Hello,

In mongodb I can query with either $and or $or which is important for deciding whether all elements need to match or just one.

Looking at the following example provided in the mongob-docs:

db.inventory.find( {
                     qty: { $all: [
                                    { "$elemMatch" : { size: "M", num: { $gt: 50} } },
                                    { "$elemMatch" : { num : 100, color: "green" } }
                                  ] }
                   } )

you can see that one can specify - in combination with $elemMatch - that all ($all) the subqueries must match.

What I would like to do is to say that only one of these has to match, which is important for doing certain types of search-queries. I noticed though, that although for $and there is the alternative $or, this does not seem to exist for $all. I would therefore like to request something like a $any keyword which behaves like $or, but can be used like $all.

I think this is quite an important issue and shouldn't be to hard to implement - or is it?

Thanks for the great work.

Best regards,
Michael



 Comments   
Comment by David Storch [ 09/Jun/14 ]

mdelamere, agreed that we should have parity between $in and $all just as we do between $or and $and. This makes lots of sense. We will take this into account as we plan for future development cycles.

Yes that would work (if we replace "products:" with "attributes:").

Whoops, good catch! I fixed this in my comment above.

Comment by Michael Delamere [ 09/Jun/14 ]

Hi Dave,

Yes that would work (if we replace "products:" with "attributes:"). I would still like to see the $in coming because:

a) it is less verbose and I prefer the $in syntax,
b) I miss it because I expected $all to have a similar alternative as $and/$or do,
c) it makes it easier to switch queries from $all to $in without writing completely new queries (although to be fair - your query could also be written with $and)

Obviously there was a reason for creating $all, even although that could have been written as a $and query. I think therefore the same reasons could apply for a $in supporting $elemMatch. As the $or does the trick though, I can see that this will not be on the top of your list.

Thanks agan for you time!

Best regards,
Michael

Comment by David Storch [ 09/Jun/14 ]

Hi Michael,

You can write an $or query with $elemMatch clauses using the following syntax:

db.products.find({
    $or: [
        {attributes: {$elemMatch: {attr_id: 123, val: {$elemMatch: {val: {$regex: "^my_name.*"}}}}}},
        {attributes: {$elemMatch: {attr_id: 456, val: {$elemMatch: {val: {$regex: "^my_article_number.*"}}}}}}
    ]
});

Our reference pages for $or are here: http://docs.mongodb.org/manual/reference/operator/query/or/.

Best,
Dave

Comment by Michael Delamere [ 09/Jun/14 ]

Hi,

Thanks very much for your response and for editing the ticket accordingly. The idea with the $in sounds excellent and would fit my needs perfectly. I actually tried replacing the $all with $or, but unfortunately that yielded an error. In my concrete case, this works:

db.products.find({ 
  "attributes" : { 
    "$all" : [ 
      { "$elemMatch" : { "attr_id" : 123 , "val" : { "$elemMatch" : { "val" : { "$regex" : "^my_name.*"}}}}}, 
      { "$elemMatch" : { "attr_id" : 456 , "val" : { "$elemMatch" : { "val" : { "$regex" : "^my_article_number.*"}}}}}
    ]
  }
})

But when I replaced the $all with $or I got:

error: {
	"$err" : "Can't canonicalize query: BadValue unknown operator: $or",
	"code" : 17287
}

Thanks again and best regards,
Michael

Comment by David Storch [ 09/Jun/14 ]

Hi mdelamere,

Thanks for the feature request! There is something quite similar to the hypothetical "$any" operator you describe. It's called $in and is documented here: http://docs.mongodb.org/manual/reference/operator/query/in/. Here is an example $in query:

db.inventory.find({qty: {$in: [4, 5, 6]}});

However, you are correct that currently it is illegal to nest $elemMatch queries inside an $in (even though you can nest $elemMatch inside an $all). I'm going to change the title and description of this ticket to be about supporting $elemMatch within an $in query. This would allow you to write a query like this:

db.inventory.find( {
                      qty: { $in: [
                                     { "$elemMatch" : { size: "M", num: { $gt: 50} } },
                                     { "$elemMatch" : { num : 100, color: "green" } }
                                   ] }
                    } 
)

The $in operator is syntactic sugar for $or, so you can get the same logical result as the currently unsupported $in-$elemMatch query using an $or:

db.inventory.find( {
                      $or: [
                                {qty: { "$elemMatch" : { size: "M", num: { $gt: 50} } } },
                                {qty: { "$elemMatch" : { num : 100, color: "green" } } }
                      ]
})

I'm going to reformat this ticket to be about supporting $elemMatch under $in and mark it for triage.

Thanks,
Dave

Generated at Thu Feb 08 03:34:09 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.