CODEWITHSUNDEEP
mongodbmongodb-querypymongo
bd_28
bd_28

Reputation: 125

How to merge data from two collections in MongoDB

I really need help with my current problem.

The Problem: How can I merge data from two collections?

The first collection is called users where every document holds info about one user. One example of a document in this collection in JSON Format:

    {
        "_id": ObjectId("userId1"),
        "nameAndSurname": "Name 1",
        "arrayOfImages": ["wwww.urlToImage1.jpeg"],
        "favouritePlayer" : "Monfils",
        "sport" : "Tennis",
        "isProfileBlocked" : false
    }

The second collection is called user_relations where every document holds info about friends that a certain user from users collection have. One example of a document in this collection in JSON Format:

    {
        "_id": ObjectId("someRandomString"),
        "userId": "userId1",
        "friendsArray": [
        {
            "userId" : "userId2",
            "lastTimestamp": 19236752642,
            "message": "Hellooo"
        },
        {
            "userId" : "userId3",
            "lastTimestamp": 12236752342,
            "message": "Yeah",
        },
        ]
    }

I have a Python query that looks like this:

db.user_relations.aggregate([
  {
    "$match": {
      "userId": "userId1"
    }
  },
  {
    "$unwind": {
      "path": "$friendsArray"
    }
  },
  {
    "$sort": {
      "friendsArray.lastTimestamp": 1
    }
  },
  {
    "$limit": 10
  },
  {
    "$replaceRoot": {
      "newRoot": "$friendsArray"
    }
  }
])

and the response when I run that query looks like this:

[{'userId': 'userId2', 'lastTimetamp': 19236752642, 'message': 'Yeah'}, {'userId': 'userId3', 'lastTimestamp': 12236752342, 'message': 'Hellooo'}]

Now what I want to do is modify this query so that I can get nameAndSurname and arrayOfImages[0] values from users collection for each value i.e. user in the friendsArray so that the response could be:

[{'userId': 'userId2', 'nameAndSurname : 'Name 2', 'pictureUrl' : 'wwww.urlToImage2.jpeg', 'lastTimestamp': 19236752642, 'message': 'Yeah'}, {'userId': 'userId3', 'nameAndSurname : 'Name 3', 'pictureUrl' : 'wwww.urlToImage3.jpeg', 'lastTimestamp': 12236752342, 'message': 'Hellooo'}]

Thank You for your time!

Upvotes: 1

Views: 167

Answers (1)

Takis
Takis

Reputation: 8695

Test code here

You want a $lookup but you want is like SQL like join, all fields in the root document so an unwind and replace root is added to merge in 1 document.

Query

db.user_relations.aggregate([
  {
    "$match": {
      "$expr": {
        "$eq": [
          "$userId",
          "userId1"
        ]
      }
    }
  },
  {
    "$unwind": {
      "path": "$friendsArray"
    }
  },
  {
    "$sort": {
      "friendsArray.lastTimeStamp": 1
    }
  },
  {
    "$limit": 10
  },
  {
    "$replaceRoot": {
      "newRoot": "$friendsArray"
    }
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "userId",
      "foreignField": "_id",
      "as": "joined__"
    }
  },
  {
    "$unwind": {
      "path": "$joined__"
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$joined__",
          "$$ROOT"
        ]
      }
    }
  },
  {
    "$project": {
      "joined__": 0
    }
  }
])

Upvotes: 1

Related Questions