How can I get a mongo subset of a collection based on an another collection-CodePudding

I have two collections.

Collection 1 is like an account.
Collection 2 creates a unique association between a user and an account

I am trying to return the accounts for which the user has no association

Collection1 schema

const Collection1Schema = new Schema({
  name: { type: String, required: true },
});

Collection1 data

[
  {
    "_id": "61cf8452fca008360872c9cd",
    "name": "Aff 2"
  },
  {
    "_id": "61cf845ffca008360872c9d0",
    "name": "AFF 1"
  },
  {
    "_id": "61cf8468fca008360872c9d3",
    "name": "Aff 3"
  }
]

Collection2 schema

const Collection2Schema = new Schema({
  userID: { type: Schema.Types.ObjectId, required: true },
  col_1_ID: { type: Schema.Types.ObjectId, required: true },
});

Collection2 data

[
  {
    "_id": "61e05bb5fe1d8327d4c73663",
    "userID": "61cf82dac828bd519cfd38ca",
    "col_1_ID": "61cf845ffca008360872c9d0"
  },
  {
    "_id": "61e05c14fe1d8327d4c7367d",
    "userID": "61cf82dac828bd519cfd38ca",
    "col_1_ID": "61cf8468fca008360872c9d3"
  },
  {
    "_id": "61e05ca0fe1d8327d4c73695",
    "userID": "61e05906246ccc41d4ebd30f",
    "col_1_ID": "61cf8452fca008360872c9cd"
  }
]

This is what I have so far... but it does not return what the user is NOT apart of

I am using Collection2 as the basis in the pipeline

[
  {
    '$match': {
      'userID': new ObjectId('61cf82dac828bd519cfd38ca')
    }
  }, {
    '$lookup': {
      'from': 'Collection1', 
      'localField': 'col_1_ID', 
      'foreignField': '_id', 
      'as': 'aa'
    }
  }, {
    '$unwind': {
      'path': '$aa', 
      'preserveNullAndEmptyArrays': true
    }
  }
]

What I would like to return is all the collection 1 documents ( where userIdD = '61cf82dac828bd519cfd38ca') is NOT associated in collection 2 ... like this :

[
  {
    "_id": "61cf8452fca008360872c9cd",
    "name": "Aff 2"
  }
]

CodePudding user response：

try the inversing lookup

https://mongoplayground.net/p/hXAYyv8X461

db.Collection1.aggregate([
  {
    "$lookup": {
      "from": "Collection2",
      "localField": "_id",
      "foreignField": "col_1_ID",
      "as": "joined_docs"
    }
  },
  {
    $unwind: {
      "path": "$joined_docs"
    }
  },
  {
    $match: {
      "joined_docs.userID": {
        $ne: "61cf82dac828bd519cfd38ca"
      }
    }
  },
  {
    $project: {
      "joined_docs": 0
    }
  }
])