MongoDB: use array returned from aggregation pipeline for $in query in the next stage

Question

As the question title says, I'm trying to use an array field returned from a $match stage to query another collection in the next stage using $lookup and the $in operator to retrieve all documents that have at least one category inside this array. (I'm using Mongoose in Node, by the way)

I want to match a "configurations" collection by '_id' that have this simplified schema:

{
    title: {type: String, required: true},
    categories: {
        allow: {type: Boolean, required: true},
        list: [
            {
               name: {type: String, required: true},// DENORMALIZED CATEGORY NAME
               _id: {type: mongoose.Schema.Types.ObjectId}
            }
        ]
    }
}

And in the next stage I want to aggregate all "partners" that belongs to at least one of those categories array. "partners" have the following schema:

{
    company: {type: String, required: true},
    categories: [
        {type: mongoose.Schema.Types.ObjectId}
    ]
}

This is what I'm doing right now:

configuration.aggregate([
{$match: {_id: ObjectID(configurationId)}},
{
  $lookup: {
    from: "partners",
    pipeline: [
      {
        $match: {
          active: true,// MATCH ALL ACTIVE PARTNERS
          categories: {
            $in: {// HERE IS THE PROBLEM: I CAN'T RETRIEVE AN ARRAY FROM $map OPERATOR
              $map: {// MAP CONFIGURATION CATEGORY LIST TO OUTPUT AN ARRAY ONLY WITH ID OBJECTS
                input: '$categories.list',
                as: 'category',
                in: '$$category._id'
              }
            }
          }
        }
      },
      { $project: { _id: 1, company: 1 } }
    ],
    as: "partners"
  }
},
])

The $map operator works as expected in a $project stage, but in this case I just can't use it's result as an array to be used with $in operator.

Is there any way to do this?

Thanks!

UPDATE

Doing like @Veeram suggested eliminates the need of $map operator in the $lookup stage:

{
  "$lookup":{
    "from":"partners",
    "let":{"categories_id":"$categories.list._id"},
    "pipeline":[
      {"$match":{"active":true,"$expr":{"$in":["$categories","$$categories_id"]}}},
      {"$project":{"_id":1,"company":1}}
    ],
    "as":"partners"
  }
}

But the problem persists with the $in operator. Like I've commented, this $in use case is the same as the 4th example in the official documentation (docs.mongodb.com/manual/reference/operator/aggregation/in), and it results in a false statement, because we are trying to check if an array ("$categories") is an element of another array ("$$categories_id"), which will fail, because the elements of "$$categories_id" are id objects and not arrays.

Does anyone know if there is any workaround for this?

Thanks!

s7vr s7vr · Accepted Answer · 2018-09-28T19:21:12

You don't need to use $map. You can use dot notation to access the ids.

$let is required to access the values from local collection and $expr to compare the document fields.

Something like

  {
  "$lookup":{
    "from":"partners",
    "let":{"categories_id":"$categories.list._id"},
    "pipeline":[
      {"$match":{
        "active":true,
        "$expr":{
          "$gt":[
            {"$size":{"$setIntersection":["$categories","$$categories_id"]}},
            0
          ]
        }
      }},
      {"$project":{"_id":1,"company":1}}
    ],
    "as":"partners"
  }
}

MongoDB: use array returned from aggregation pipeline for $in query in the next stage

1 Answers