[SERVER-44355] Add $some array query operator Created: 01/Nov/19  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: Christofer Eliasson Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
is related to SERVER-46788 Suggestion: alias $any to $in Backlog
Assigned Teams:
Query Optimization
Participants:

 Description   

Using the $in and $all operators to query array fields cover a lot of ground, but when you want query for documents where the array holds at least 4 values, or exactly 8, then MongoDB is missing a useful operator.

Let’s take a simple example. You have a collection of Customers, and each customer document has a purchasedProducts field, which is an array of ObjectId’s (references to all products the customer have purchased). Now, for a marketing campaign you want to find all your customers who have purchased at least 3 out of a set of 10 selected products, in order to send a message to these customers.

In this scenario, the $in operator won’t do, because it would give you all customers who have purchased at least one of the products. The $all operator won’t work either, because it would only give you the customers who have purchased all 10 products in the set. In this scenario a $some operator would come in handy.

As a suggestion, the $some operator could take an array with two items - the first item being an array of values to match against and the second item being an object declaring the condition for a match.

/*
 * Example of a $some operator where the document would match if purchasedDocuments 
 * contain at least two of the three provided ObjectIds
 */
 
db.collection.find({
  purchasedProducts: {
    $some: [[ObjectId('ID1'), ObjectId('ID2'), ObjectId('ID3')], { $gte: 2 }]
  }
})

Using an aggregation pipeline we get access to the tools we need to create the equivalent of a $some operator, as I've outlined in this Medium story but it gets quite verbose and it would be really convenient to have it available as regular query operator.



 Comments   
Comment by Asya Kamsky [ 01/Nov/19 ]

This is currently possible via $expr in find or $match:

purchasedEvents=[ObjectId('ID1'), ObjectId('ID2'), ObjectId('ID3')];
db.collection.find({
     purchasedEvents: { $in:purchasedEvents },
    $expr:{$gt:[ {$size: {$setIntersection: [‘$purchasedEvents’, purchasedEvents]}}, 2] }
})

Comment by Carl Champain (Inactive) [ 01/Nov/19 ]

Hi christofer@markethype.io,

Thanks for taking the time to submit this request.
I'm assigning this ticket to the Query team for additional investigation. Updates will be posted on this ticket as they happen.

Kind regards,
Carl

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