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 ?
CodePudding user response：
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.
CodePudding user response：
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()