I have objects that look like this
{
name: 'Object 1',
fruitList: ['apple','pear','orange','grape']
},
{
name: 'Object 2',
fruitList: ['melon','pear','apple','kiwi']
}
I need to retrieve all the objects that have apple before pear in their fruitList, in this example it would mean Object 1 only. Can I do a custom match function that iterates over that list and checks it it matches my criteria ?
You need a mechanism to compare the indexes of the fruits in question and use the comparison as a match condition with the $expr operator. Leverage the aggregation pipeline operators:
$indexOfArray - Searches an array for an occurrence of a specified value and returns the array index (zero-based) of the first occurrence.$subtract - return the difference between the two indexes. If the value is negative then apple appears before pear in the list.$lt - the comparison operator to use in $expr query that compares two values and returns true when the first value is less than the second value.To get a rough idea of these operators at play in an aggregation pipeline, check out the following Mongo Playground.
The actual query you need is as follows:
db.collection.find({
$expr: {
lt: [
{
$subtract: [
{ $indexOfArray: [ '$fruitList', 'apple' ] },
{ $indexOfArray: [ '$fruitList', 'pear' ] }
]
},
0
]
}
})
For a generic regex based solution where the fruitList array may contain a basket of assorted fruits (in different cases) for instance:
"fruitList" : [
"mango",
"Apples",
"Banana",
"strawberry",
"peach",
"Pears"
]
The following query can address this challenge:
const getMapExpression = (fruit) => {
return {
$map: {
input: '$fruitList',
as: 'fruit',
in: {
$cond: [
{ $regexMatch: { input: '$$fruit', regex: fruit, options: 'i' } },
{ $literal: fruit },
'$$fruit'
]
}
}
}
}
db.collection.find({
$expr: {
$lt: [
{
$subtract: [
{ $indexOfArray: [ getMapExpression('apple'), 'apple' ] },
{ $indexOfArray: [ getMapExpression('pear'), 'pear' ] }
]
},
0
]
}
})