'Translate from Mysql to MangoDb
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 ?
Solution 1:[1]
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.
Solution 2:[2]
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()
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | Nasser MEHLEB |