CODEWITHSUNDEEP
mongodb
MrLister
MrLister

Reputation: 707

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

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"
  }
]

UPDATE 1 Here is a playground where another user has joined another account, so the pipeline does not return "Aff 2" like expected

https://mongoplayground.net/p/W6W88_2MaI3

UPDATE 2 Here is a playground that almost does what I want... it's returning duplication "AFF 2" entries.

https://mongoplayground.net/p/nTI3MKNPEmD

Upvotes: 0

Views: 210

Answers (3)

indybee
indybee

Reputation: 1736

try this instead:

https://mongoplayground.net/p/HDm2sbdvH88

db.Collection1.aggregate([
  {
    "$lookup": {
      "from": "Collection2",
      "localField": "_id",
      "foreignField": "col_1_ID",
      "as": "joined_docs"
    }
  },
  {
    $unwind: {
      "path": "$joined_docs"
    }
  },
  {
    $group: {
      _id: {
        account_id: "$_id",
        account_name: "$name",
        
      },
      user_ids: {
        $push: {
          "userID": "$joined_docs.userID"
        }
      }
    }
  },
  {
    $match: {
      "user_ids.userID": {
        $nin: [
          "61cf82dac828bd519cfd38ca"
        ]
      }
    }
  },
  {
    $project: {
      user_ids: 0
    }
  }
])

Upvotes: 0

MrLister
MrLister

Reputation: 707

ANSWER:

after messing around with several mongo playgrounds and digging into a few different pipeline attributes... here is what works:

https://mongoplayground.net/p/xbZeRfVcrZq

Data:

db={
  "Collection1": [
    {
      "_id": "61cf8452fca008360872c9cd",
      "name": "Aff 2"
    },
    {
      "_id": "61cf845ffca008360872c9d0",
      "name": "AFF 1"
    },
    {
      "_id": "61cf8468fca008360872c9d3",
      "name": "Aff 3"
    }
  ],
  "Collection2": [
    {
      "_id": "61e05bb5fe1d8327d4c73663",
      "userID": "61cf82dac828bd519cfd38ca",
      "col_1_ID": "61cf845ffca008360872c9d0"
    },
    {
      "_id": "61e05c14fe1d8327d4c7367d",
      "userID": "61cf82dac828bd519cfd38ca",
      "col_1_ID": "61cf8468fca008360872c9d3"
    },
    {
      "_id": "61e05ca0fe1d8327d4c73695",
      "userID": "61e05906246ccc41d4ebd30f",
      "col_1_ID": "61cf8452fca008360872c9cd"
    },
    {
      "_id": "61e05c14fe1d8327d4c73600",
      "userID": "61cf82dac828bd519cfd3111",
      "col_1_ID": "61cf8468fca008360872c9d3"
    },
    {
      "_id": "61e05c14fe1d8327d4c73601",
      "userID": "61cf82dac828bd519cfd3112",
      "col_1_ID": "61cf8452fca008360872c9cd"
    },
    
  ]
}

Pipeline:

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

result:

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

Upvotes: 0

indybee
indybee

Reputation: 1736

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
    }
  }
])

Upvotes: 1

Related Questions