MongoDB equivalent of this SQL query

Question

I was wondering how to run equivalent of the following SQL query in MongoDB.

// 1 = current user ID

SELECT p.id, p.firstName, p.lastName from friendships f
JOIN person p ON p.id = f.requester
WHERE f.requestee = 1
UNION
SELECT p.id, p.firstName, p.lastName
from friendships f
JOIN person p ON p.id = f.requestee
WHERE f.requester = 1

friendships table

╔══════════════════════════════════════╗
║ requester       requestee       type ║
╠══════════════════════════════════════╣
║ userA           userC            1   ║
║ userB           userA            1   ║
║ userC           userD            1   ║
╚══════════════════════════════════════╝

Let's say I am userA my friends are according to the table above userB and userC, thus I need those two users to be returned.

MongoDB friendship document (collection friendships)

{
    requester: userID
    requestee: otherUserID
    type: // type of relationship (accepted, declined, blocked, etc.)
    time: // timestamp
}

MongoDB user document (collection users)

{
    _id: ObjectId('random')
    firstName: 'Someone',
    lastName: 'Random'
}

Desired result: All friends of current user and their user details.

[
    {
        firstName: 'John'
        lastName: 'Doe'
        time: // timestamp from friendship document
    },
    {
        firstName: 'Jane'
        lastName: 'Doe'
        time: // timestamp from friendship document
    },
    {
        firstName: 'James'
        lastName: 'Doe'
        time: // timestamp from friendship document
    }
]

Thank you very much in advance for all help.

Answer 1

This is a partial answer based on the information available in the questions. You might need to make changes as needed. Any improvements are appreciated.

db.friendship.aggregate([
{ 
      $lookup:{  
         from:"person",
         as: "p1",
         localField: "personid1",
         foreignField: "personid",
      }
    },
    { 
      $lookup:{  
         from:"person",
         as: "p2",
         localField: "personid2",
         foreignField: "personid",
      }
    },
    {
        $match:{ $or: [{personid1:"1"},{personid2:"1"}]}
    },
    {
        $replaceRoot:{ newRoot: {
            "persons" : { $setUnion : [ "$p1" , "$p2" ]}
        }
        }
    },{
        $unwind:{  path: "$persons"}
    },
    {
        $group:{ _id: {personid:"$persons.personid", firstname:"$persons.firstname", lastname:"$persons.lastname"} }
    },
    {
        $replaceRoot:{ newRoot: {
            "personid" : "$_id.personid",
            "firstname" : "$_id.firstname",
            "lastname" : "$_id.lastname"
        }
        }
    }
]).pretty()