Mongo aggregation framework match a given

My model :

const scheduleTaskSchema = new Schema({
  activity: { type: Object, required: true },
  date: { type: Date, required: true },
  crew: Object,
  vehicle: Object,
  pickups: Array,
  details: String,
});
const ScheduleTaskModel =  mongoose.model("schedule_task", scheduleTaskSchema),

and this aggregation pipeline :

  let aggregation = [
    {
      $sort: {
        "pickups.0.time": 1,
      },
    },
    {
      $group: {
        _id: "$date",
        tasks: { $push: "$$ROOT" },
      },
    },
    { $sort: { _id: -1 } },
  ];



  if (hasDateQuery) {
    aggregation.unshift({
      $match: {
        date: { $gte: new Date(start_date), $lte: new Date(end_date) },
      },
    });
  } else {
    aggregation.push({ $limit: 2 });
  }

  const scheduledTasksGroups = await ScheduleTaskModel.aggregate(aggregation);

the crew object can have arbitrary number of keys with this structure :

crew : {
  drivers: [
    {
      _id: "656b1e9cf5b894a4f2v643bc",
      name: "john"
    },
    {
      _id: "567b1e9cf5b954a4f2c643bhh",
      name: "bill"
    }
  ],
  officers: [
    {
      _id: "655b1e9cf5b6632a4f2c643jk",
      name: "mark"
    },
    {
      _id: "876b1e9af5b664a4f2c234bb",
      name: "jane"
    }
  ],
  //...any number of keys that contain an array of objects that all have an _id
}

I'm looking for a way to return all documents (before sorting/grouping) that contain a given _id anywhere within the crew object without knowing which key to search,it can be many different keys that all contain an array of objects that all have an _id

Any ideas ?

CodePudding user response：

You can use $objectToArray for this:

db.collection.aggregate([
  {$addFields: {crewFilter: {$objectToArray: "$crew"}}},
  {$set: {
      crewFilter: {$size: {
          $reduce: {
            input: "$crewFilter",
            initialValue: [],
            in: {$concatArrays: [
                "$$value",
                {$filter: {
                    input: "$$this.v",
                    as: "member",
                    cond: {$eq: ["$$member._id", _id]}
                  }
                }
              ]
            }
          }
      }}
  }},
  {$match: {crewFilter: {$gt: 0}}}
])

See how it works on the playground example