Translate from Mysql to MangoDb

user_15145047 0
Viewed 34

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 ?

mongodb-query mongodb
2 Answers

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.

user_14093740

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()
user_151450470
Related