Turning the output of one query as the array of an $in operator of another query-CodePudding

Essentially, there are 2 queries. The first one queries a collection_A and then return an array of _id. The second query will use this array in a $in operator to query collection_B.

How can this be done in just 1 query instead of 2? I.e., how can this be done with one call to MongoDB server instead of 2 trips?

CodePudding user response：

I think what you need is $lookup, since oy connect documents by _id between collection_A and collection_B.

db.orders.aggregate([
  {
    "$lookup": {
      "from": "inventory",
      "localField": "item",
      "foreignField": "sku",
      "as": "inventory_docs"
    }
  }
])

mongoplayground

CodePudding user response：

The $lookup will join two collections either on a simple localField foreignField match or with a more complicated syntax let/pipeline, see

{
   $lookup:
     {
       from: <collection to join>,
       let: { <var_1>: <expression>, …, <var_n>: <expression> },
       pipeline: [ <pipeline to execute on the collection to join> ],
       as: <output array field>
     }
}

The simpler format, with a match, your first query, is as follows:

[{$match: {
 description: RegExp('salt')
}}, {$lookup: {
 from: 'entityMapping',
 localField: 'ndb',
 foreignField: 'fdc_id',
 as: 'array'
}}]