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 ?

CodePudding user response：

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
        ]
    }
})

Mongo Playground

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
        ]
    }
})