skip to Main Content

I have two tables that i want to join.
Tables : users, rooms.

use myDB;
create table users(
userId char(12),
pseudo varchar(24)
);

create table rooms (
    roomId char(24),
    userIdFrom char(12),
    userIdTo char(12)
);

insert into users values('000000000001','A');
insert into users values('000000000002','B');
insert into users values('000000000003','C');
insert into users values('000000000004','D');
insert into users values('000000000005','E');
insert into users values('000000000006','F');
insert into users values('000000000007','G');
insert into users values('000000000008','H');

insert into rooms values('000000000001000000000002','000000000001','000000000002');
insert into rooms values('000000000001000000000003','000000000001','000000000003');
insert into rooms values('000000000008000000000001','000000000008','000000000001');

My query is :

use myDB;
select u.userId, u.pseudo, r.roomId, r.userIdFrom, r.userIdTo from users u 
inner join rooms r on (r.userIdFrom = u.userId or r.userIdTo = u.userId)
where userId <> '000000000001';

Results are :

# userId, pseudo, roomId, userIdFrom, userIdTo
'000000000002', 'B', '000000000001000000000002', '000000000001', '000000000002'
'000000000003', 'C', '000000000001000000000003', '000000000001', '000000000003'
'000000000008', 'H', '000000000008000000000001', '000000000008', '000000000001'

Now, i want this query in mongoDB,

I tried : for userId in users = "000000000001"

    db.users.aggregate([
{
    $match: {
      "userId": { $nin: ["000000000001"] }
    }
  },
       {
          $lookup: {
             from: "rooms",
             let: {
                "userId": "000000000001",
                "userIdFrom": "$userId",
                "userIdTo": "$userId"
             },
             pipeline: [
                {
                   $match: {
                      $expr: {
                         $or: [
                            {
                               $eq: [
                                  "$userIdFrom", "$$userId"
                               ]
                            },
                            {
                               $eq: [
                                  "$userIdTo", "$$userId" 
                               ]
                            }
                         ]
                      }
                   }
                }
             ],
             as: "result"
          },
       },
    ]
).pretty()

But the results are not as expected,

{
        "_id" : ObjectId("626af4de5e41275250542c79"),
        "pseudo" : "B",
        "userId" : "000000000002",
        "password" : "$2b$10$iv1aEVd424yVqo5kXEbFnOBPe5FYKIKlbI5N1EEIvhiRL43b5fYku",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}
{
        "_id" : ObjectId("626af4de5e41275250542c7b"),
        "pseudo" : "E",
        "userId" : "000000000005",
        "password" : "$2b$10$BC3pfhkKKTRqPnU4X7hsjOWlMBpmaojTS62pGeDwuh5nFc/l5z8Gy",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}
{
        "_id" : ObjectId("626af4de5e41275250542c7d"),
        "pseudo" : "G",
        "userId" : "000000000007",
        "password" : "$2b$10$0G5sqXrLtp0f1wjZuqkI8O7WNAfu2K.FE.dUPsBP6OpEBvgrZd1u2",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}
{
        "_id" : ObjectId("626af4de5e41275250542c7f"),
        "pseudo" : "F",
        "userId" : "000000000006",
        "password" : "$2b$10$9.vBd3k1wFYfopDKzuwQuuWCBCuTPMLEd0aMuZpI6K3e37RbUPkDW",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}
{
        "_id" : ObjectId("626af4de5e41275250542c81"),
        "pseudo" : "H",
        "userId" : "000000000008",
        "password" : "$2b$10$8oNiWJi9y8j6UV5/sf6yvenXdvqii0VPaauL/2Y7QfMbDs9ffEHhC",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}
{
        "_id" : ObjectId("626af4de5e41275250542c77"),
        "pseudo" : "C",
        "userId" : "000000000003",
        "password" : "$2b$10$MCIFV5ATx3DdkLotL6Mht.o22Cc13G6Ad7QkTQKaZuAZpKA5MXwky",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}
{
        "_id" : ObjectId("626af4de5e41275250542c83"),
        "pseudo" : "I",
        "userId" : "000000000009",
        "password" : "$2b$10$TtScWbrfV30K/poipAuYGu4pwPvRkzCSkzghqJWNDRfYW9XnQPtbO",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}
{
        "_id" : ObjectId("626af4de5e41275250542c75"),
        "pseudo" : "D",
        "userId" : "000000000004",
        "password" : "$2b$10$8ct5anRaSIG5FzqQZTlVM./pQV66GQ3w.kUBVRMcMAD76Hy2B55B2",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}

Can some one help me ?

2

Answers


  1. Chosen as BEST ANSWER

    The solution is below : (for node.js replace the user id to seach by req.body.userId

        db.rooms.aggregate(
    {$match:
    {$or:[
    {userIdFrom: "000000000001"},
    {userIdTo:"000000000001"},
    ]}
    },
    {
        $lookup: {
            from: "users",
            let: {
                "userId": "000000000001",
                "userIdFrom": "$userIdFrom",
                "userIdTo": "$userIdTo"
            },
            pipeline: [
            {
                $match: {
                    $expr: {
                        $and: [
                        { $ne: [ "$userId", "$$userId"]},
                        { $or:  [ { $eq: [ "$userId", "$$userIdTo" ] }, { $eq: [ "$userId", "$$userIdFrom" ] } ] }
                    ]
    
                }
            }
        }
        ],
        as: "user"
    },
    },
    
    ).pretty()
    
    

  2. EDIT I didn’t notice the <> in the original SQL. Fixed!

    I’m not exactly sure how you want the output formatted so you’ll probably want to modify my later stages, but here’s one way you could get your desired "$lookup", etc.

    N.B.: This assumes there’s only one result from rooms per user since it just takes the "$first" "$match". If more rooms are possible, or desired, you’ll need to modify the later stages and output formatting as required.

    db.users.aggregate([
      { "$match": { "userId": { "$ne": "000000000001" } } },
      {
        "$lookup": {
          "from": "rooms",
          "let": { "userId": "$userId" },
          "pipeline": [
            {
              "$match": {
                "$expr": {
                  "$or": [
                    { "$eq": [ "$userIdFrom", "$$userId" ] },
                    { "$eq": [ "$userIdTo", "$$userId" ] }
                  ]
                }
              }
            }
          ],
          "as": "roomData"
        }
      },
      { "$match": { "$expr": { "$gt": [ { "$size": "$roomData" }, 0 ] } } },
      {
        "$replaceWith": {
          "$mergeObjects": [
            "$$ROOT",
            { "$first": "$roomData" }
          ]
        }
      },
      { "$unset": [ "_id", "roomData" ] }
    ])
    

    Example output:

    [
      {
        "pseudo": "B",
        "roomId": "000000000001000000000002",
        "userId": "000000000002",
        "userIdFrom": "000000000001",
        "userIdTo": "000000000002"
      },
      {
        "pseudo": "C",
        "roomId": "000000000001000000000003",
        "userId": "000000000003",
        "userIdFrom": "000000000001",
        "userIdTo": "000000000003"
      },
      {
        "pseudo": "H",
        "roomId": "000000000008000000000001",
        "userId": "000000000008",
        "userIdFrom": "000000000008",
        "userIdTo": "000000000001"
      }
    ]
    

    Try it on mongoplayground.net.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search