How do I perform a mongoDB lookup and then merge results into a single data set?

Question

Hi I am looking to join data from two tables

What I am looking to do is create a "friend" relationship between two users using a lookup and find out how many emails each friend (if any sent)

here is a mongo DB playground... I am close, I just can't figure out how to get the 'other users' email information

I should only see emails from a's friends (b and c) and NOT any emails sent from a

This playground almost does what I want...

https://mongoplayground.net/p/KKocPm3fzEv

Here is the input for the above playground

db={
  "users": [
    {
      "_id": "a",
      "email": "a@test.com",
    },
    {
      "_id": "b",
      "email": "b@test.com",
    },
    {
      "_id": "c",
      "email": "c@test.com",
    }
  ],
  "friends": [
    {
      "userId": "a",
      "otherUserId": "b"
    },
    {
      "userId": "a",
      "otherUserId": "c"
    },
  ],
  "emailsSent": [
    {
      "userId": "a",
      "number": "25"
    },
    {
      "userId": "b",
      "number": "3"
    },
  ]
}

Here is the output from the above playground

[
  {
    "_id": "a",
    "a_myfriends": [
      {
        "_id": ObjectId("5a934e000102030405000002"),
        "otherUserId": "b",
        "userId": "a"
      },
      {
        "_id": ObjectId("5a934e000102030405000003"),
        "otherUserId": "c",
        "userId": "a"
      }
    ],
    "email": "a@test.com",
    "emailaddr": [
      {
        "_id": "b",
        "email": "b@test.com"
      },
      {
        "_id": "c",
        "email": "c@test.com"
      }
    ],
    "emailsent": [
      {
        "_id": ObjectId("5a934e000102030405000001"),
        "number": "3",
        "userId": "b"
      }
    ]
  }
]

There are three arrays of information now... how do I join them all together so each entry in the array is only for 'that' friend?

this is what I'd like to end up with

{
  "_id": "a",
  "a_myfriends": [
    {
      "otherUserId": "b",
      "email": "b@test.com",
      "number": "3"
    },
    {
      "otherUserId": "c",
      "email": "c@test.com"
    }
  ]
}

NOTE: I tried concatenating unions from this article, but I think it's not working due to the disparity of IDs for the user (eg _id and userId)

MongoDB: Combine data from multiple collections into one..how?

Takis · Accepted Answer · 2021-09-28T11:30:28.680

I think you can reduce the number of collections => reduce the $lookups and data will look more simple

How about this schema of 1 collection?
Even if someone has too many friends like 200.000, you could have an extra field {"extra_friends" "_id"}, and rarely use the a secondary friends collections going rarely max to 2 collections.

users=
[
    {
      "_id": "a",
      "email": "a@test.com",
      "emails-send" 25,
      "friends" ["b" "c"]      
    },
    {
      "_id": "b",
      "email": "b@test.com",
      "emails-send" 3,
      "friends" [....]
    },
    {
      "_id": "c",
      "email": "c@test.com",
      "emails-send" 0
      "friends" [....]
    }
  ]

Query
(for your schema, produces the expected data)

a series of $lookup and $unwind
from users -to friends (get friends) -to users (get friends email) -to emailsSent (get number of emails for those that sended)
some $set in the middle to keep the schema simple
group by _id back and combine those friends

*query could become smaller a bit, but this way its easy to follow, stage by stage and see what is happening

Test code here

db.users.aggregate([
  {
    "$match": {
      "$expr": {
        "$eq": [
          "$_id",
          "a"
        ]
      }
    }
  },
  {
    "$lookup": {
      "from": "friends",
      "localField": "_id",
      "foreignField": "userId",
      "as": "myfriends"
    }
  },
  {
    "$unwind": {
      "path": "$myfriends"
    }
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "myfriends.otherUserId",
      "foreignField": "_id",
      "as": "myfriendsEmails"
    }
  },
  {
    "$unwind": {
      "path": "$myfriendsEmails"
    }
  },
  {
    "$set": {
      "myfriends.email": "$myfriendsEmails.email"
    }
  },
  {
    "$unset": [
      "myfriendsEmails",
      "myfriends._id",
      "myfriends.userId"
    ]
  },
  {
    "$lookup": {
      "from": "emailsSent",
      "localField": "myfriends.otherUserId",
      "foreignField": "userId",
      "as": "friendsEmails"
    }
  },
  {
    "$unwind": {
      "path": "$friendsEmails",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    "$set": {
      "myfriends.number": "$friendsEmails.number"
    }
  },
  {
    "$unset": [
      "friendsEmails"
    ]
  },
  {
    "$group": {
      "_id": "$_id",
      "email": {
        "$first": "$email"
      },
      "friends": {
        "$push": "$myfriends"
      }
    }
  }
])

OK... this is getting closer... but still not quite what I want the output to be: https://mongoplayground.net/p/ONFyPQ_7QWv — MrLister, Sep 28 '21 at 02:42
even closer... try this one; https://mongoplayground.net/p/kzzedGna6_D — MrLister, Sep 28 '21 at 02:50

How do I perform a mongoDB lookup and then merge results into a single data set?

1 Answers1