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
The solution is below : (for node.js replace the user id to seach by req.body.userId
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
peruser
since it just takes the"$first"
"$match"
. If morerooms
are possible, or desired, you’ll need to modify the later stages and output formatting as required.Example output:
Try it on mongoplayground.net.