Find specific field in MongoDB document based on condition

Question

I have the following MongoDB documents like this one:

    {
        "_id": "ABC",
        "properties":
        [
            {
                "_id": "123",
                "weight":
                {
                    "$numberInt": "0"
                },
                "name": "Alice"
            },
            {
                "_id": "456",
                "weight":
                {
                    "$numberInt": "1"
                },
                "name": "Bob"
            },
            {
                "_id": "789",
                "weight":
                {
                    "$numberInt": "1"
                },
                "name": "Charlie"
            }
        ]
    }

And I would like to find the _id of the property with name "Alice", or the _id of the property with "$numberInt": "0".

I'm using pymongo.

The following approach:

    from pymongo import MongoClient
    mongo_client = MongoClient("mymongourl")
    mongo_collection = mongo_client.mongo_database.mongo_collection
    
    mongo_collection.find({'properties.name': 'Alice'}, {'properties': 1})[0]['_id']

Gives the very first _id ("123") But since I filtered for the document, if Alice was in the second element of the properties array (_id: "456") I would have missed her. Which is the best method to find for the specific _id associated with the element with the specified name?

Answer 1

You can simply use $reduce to iterate through the properties array. Conditionally store the _id field if it matches your conditions.

db.collection.aggregate([
  {
    "$addFields": {
      "answer": {
        "$reduce": {
          "input": "$properties",
          "initialValue": null,
          "in": {
            "$cond": {
              "if": {
                $or: [
                  {
                    $eq: [
                      "$$this.name",
                      "Alice"
                    ]
                  },
                  {
                    $eq: [
                      "$$this.weight",
                      0
                    ]
                  }
                ]
              },
              "then": "$$this._id",
              "else": "$$value"
            }
          }
        }
      }
    }
  }
])

Mongo Playground