sql - MongoDB中的3个表连接

Question

考虑到下面的 SQL 查询，当我需要从 3 个不同的表中获取数据值时。我将 SQL 中的要求模拟为

create table h(id int, hname varchar(20));
create table r(id int, rname varchar(20), hid int, facid int);
create table f(id int, fname varchar(20));

insert into h values(1, 'Hotel1');
insert into h values(2, 'Hotel2');

insert into r values(1, 'room1', 1, 1);
insert into r values(1, 'room1', 1, 2);
insert into r values(1, 'room1', 1, 3);
insert into r values(2, 'room2', 1, 1);
insert into r values(2, 'room2', 1, 2);

insert into f values(1, 'fac1');
insert into f values(2, 'fac2');
insert into f values(3, 'fac3');

select rname, facid, hname from r
inner join f 
on f.id = r.facid
inner join h 
on r.hid= h.id

我得到：

room1|1|Hotel1
room1|2|Hotel1
room1|3|Hotel1
room2|1|Hotel1
room2|2|Hotel1

现在，我需要在 MongoDB 中建立同样的理解。我尝试了 2 个集合加入为

rooms.aggregate([ {   "$match" : { "_id" : ObjectId("5c11fb6fc7daa1c774fb4e67"),  }
                                                       },
                                                   {
                                                     "$lookup":
                                                       {
                                                         "from": "facility",
                                                         "localField": "facilities",
                                                         "foreignField": "_id",
                                                         "as": "facilities"
                                                       }

                                                 }, 
                                                    { "$project" : {"facilities":1}}
                                                ])

如何向此查询添加另一个集合以获取酒店房间每个设施的值。

设施：

{
    "_id" : ObjectId("5123123123123scsd2"),
    "facility_price" : 2132,
    "facility_serves" : 2,
    "facility_name" : "Mini Bar",
    "facility_type" : "Room Related",
    "facility_desc" : "Minibar for in room fine dine."
}

房间：

{
    "_id" : ObjectId("234234jhd98234jhfw23"),
    "room_len" : 10,
    "room_name" : "Single",
    "hotel_id" : ObjectId("5c01323213123295a"),
    "facilities" : [ 
        ObjectId("5123123123123scsd2"), 
        ObjectId("58237h9a8723h4239a")
    ],
    "room_desc" : "good room for family and friends",
    "room_capacity" : 6,
    "room_price" : 1234,
    "total_rooms" : 13,
    "room_brd" : 12,
    "room_type" : "Executive Suite"
}

酒店：

{
    "_id" : ObjectId("5c01323213123295a"),
    "estd" : "20160808",
    "rating" : "4",
    "long_desc" : "Hotel Name is situated in posh locality of ,
    "chain" : "",
    "locality" : "good locality",
    "country" : "country code",
    "hoteladdress" : "some hotel address",
    "hoteltype" : "family",
    "short_desc" : "This elegant 4-star boutique hotel is just a 5-minute walk ",
    "longitude" : "74.82260099999996",
    "plus_code" : "8J6P3R8F+V2",
    "city_center_distance" : 6,
    "hotelname" : "Hotel Name id",
    "floors" : 3,
    "postal_code" : 122321,
    "rooms" : 23.,
    "latitude" : "12.312",
    "airport_distance" : 14,
    "city" : "SCS",
    "phno" : NumberLong(4234234234)
}

标签： sqlmongodbjoin

Answer 1

尝试如下所示：

rooms.aggregate([ {   "$match" : { "_id" : ObjectId("5c11fb6fc7daa1c774fb4e67")}},
                {
                    "$lookup":
                    {
                        "from": "facility",
                        "localField": "facilities",
                        "foreignField": "_id",
                        "as": "facilities"
                    }
                }, 
                {
                    "$lookup":
                    {
                        "from": "h",               // name of your collection
                        "localField": "hid",
                        "foreignField": "_id",
                        "as": "hotel"
                    }
                }, 
                { 
                   "$project" : {"facilities":1, "hotel":1}
                }

              ])