mongodb: how can i use $lookup and $match and $elemMatch in one query-CodePudding

I'm trying to do a lookup from collection1 to collection2 using attachments.collection2_keys (an array), and at the same time filter collection1 where collection2.type is typeA, for any key in attachments.collection2_keys.

I believe this query should work, but it returns no results:

db.getCollection('collection1').aggregate([{ $lookup: { from: "collection2", localField: "attachments.collection2_keys", foreignField: "collection2_key", as: "attachments.collection2_items" } }, {$match: {'attachments.collection2_items': {$elemMatch: {$type: 'typeA'}}}}])

Based on other stackoverflow questions, I tried this query, but it filters attachments.collection2_keys not collection1:

db.getCollection('collection1').aggregate([{ $lookup: { from: "collection2", localField: "attachments.collection2_keys", foreignField: "collection2_key", as: "attachments.collection2_items", pipeline: [{$match: {type: 'typeA'}}] } }])

collection1 example:

[
  { attachments: { collection2_keys: [ 'x' ] } },
  { attachments: { collection2_keys: [ 'y' ] } }
]

collection2 example:

[
  {
    collection2_key: 'x',
    type: 'typeA'
  },
  {
    collection2_key: 'y',
    type: 'typeB'
  }
]

desired result:

[
  {
    attachments: {
      collection2_keys: [ 'x' ],
      collection2_items: [
        {
          collection2_key: 'x',
          type: 'typeA'
        }      
      ]
    }
  }
]

CodePudding user response：

try using lookup along with match, addfields and project.

db.getCollection('collection1').aggregate([
  {
    '$lookup': {
      'from': 'collection2', 
      'localField': 'attachments.collection2_keys', 
      'foreignField': 'collection2_key', 
      'as': 'collection2_items'
    }
  }, {
    '$match': {
      'collection2_items.type': 'typeA'
    }
  }, {
    '$addFields': {
      'attachments.collection2_items': '$collection2_items'
    }
  }, {
    '$project': {
      '_id': 1, 
      'attachments': 1
    }
  }
])

CodePudding user response：

try this

db.getCollection('collection1').aggregate([
  {
    '$lookup': {
      'from': 'collection2',
      'let': { 'keys': '$attachments.collection2_keys' },
      'pipeline' : [{
        $match : {
         $expr: { $in: ["$collection2_key", "$$keys"] },
        }
       }], 
       'as': 'attachments.collection2_items'
    }
  },
  {
   $match : {
     "attachments.collection2_items.type": 'typeA'
   }
  }
])