MongoDB - Find array index of document in array field

Question

I have aggregation that contains array field. This array field contains documents (objects). For these I have some matching criteria and I would like create a new fields named lowestCheapIndex and highestExpensiveIndex, each with array index of matching element.

Matching criteria:

lowestCheapIndex - Should contain lowest array index number of any record item, that has price below 20.

highestExpensiveIndex - Should contain highest array index number of any record item, that has price over 30.

My current aggregation output:

{
    '_id': 'Egg shop',
    'records': [
        {'_id': 1, 'price': 22},
        {'_id': 2, 'price': 18},
        {'_id': 3, 'price': 34},
        {'_id': 4, 'price': 31},
        {'_id': 5, 'price': 13},
    ]
}

Desired output:

{
    '_id': 'Egg shop',
    'records': [
        {'_id': 1, 'price': 22},
        {'_id': 2, 'price': 18},
        {'_id': 3, 'price': 34},
        {'_id': 4, 'price': 31},
        {'_id': 5, 'price': 13},
    ],
    'lowestCheapIndex': 1,
    'highestExpensiveIndex': 3,
}

Question:

How can i retrieve array index based on my criteria? I found $indexOfArray in docs, but still I am having hard time how it would be used in my case.

ray · Accepted Answer · 2021-08-03 15:07:33Z

1

You can do following in an aggregation pipeline:

use $map to augment your records array with booleans indicating below 20 and over 30
use $indexOfArray to search for the booleans; For highestExpensiveIndex, reverse the array first to get the index then subtract it from size of array - 1 to get the expected index.

db.collection.aggregate([
  {
    "$addFields": {
      "records": {
        "$map": {
          "input": "$records",
          "as": "r",
          "in": {
            "_id": "$$r._id",
            "price": "$$r.price",
            "below20": {
              $lt: [
                "$$r.price",
                20
              ]
            },
            "over30": {
              $gt: [
                "$$r.price",
                30
              ]
            }
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "lowestCheapIndex": {
        "$indexOfArray": [
          "$records.below20",
          true
        ]
      },
      "highestExpensiveIndex": {
        "$subtract": [
          {
            "$subtract": [
              {
                $size: "$records"
              },
              {
                "$indexOfArray": [
                  {
                    "$reverseArray": "$records.over30"
                  },
                  true
                ]
              }
            ]
          },
          1
        ]
      }
    }
  }
])

Mongo playground

edited Aug 3, 2021 at 15:07

answered Aug 3, 2021 at 2:14

ray

15.6k12 gold badges96 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fusion Over a year ago

Thanks for posting solution. lowestCheapestIndex should be 1 (record with _id: 2), because array indexes are beginning by 0 not 1. In contrary, highestExpensiveIndex should be the highest index. That means 3 (record with _id: 4). Thanks for code snippet, but it shows only the lowest array index for below20 and lowest array index for over30.

ray Over a year ago

The answer is updated with explanation. New solution uses $reverseArray

Fusion Over a year ago

just in case... would you mind to edit your answer and to paste code directly here as well? This is due external content having possibility of being deleted after some period of time.

Collectives™ on Stack Overflow

MongoDB - Find array index of document in array field

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related