Mongodb - $group with $addToSet and then $lookup

Question

I've got the following query

db.getCollection('transportations').aggregate(
{
     $group: {
        _id: null,
        departure_city_id: { $addToSet: "$departure.city_id" },
        departure_station_id: { $addToSet: "$departure.station_id" }
     }
  }
);

and the result is

{
"_id" : null,
"departure_city_id" : [ 
    ObjectId("5a2f5378334c4442ab5a63ea"), 
    ObjectId("59dae1efe408157cc1585fea"), 
    ObjectId("5a5bbfdc35628410f9fdcde9")
],
"departure_station_id" : [ 
    ObjectId("5a2f53d1334c4442ab5a63ee"), 
    ObjectId("5a2f53c5334c4442ab5a63ed"), 
    ObjectId("5a5bc13435628410f9fdcdea")
]
}

Now i want to lookup each departure_city_id with the collection "areas" to get the "name" of the area and each departure_station_id with the collection "stations" to get also the "name" of the station

The result could be something like this

{
"_id" : null,
"departure_city_id" : [ 
    {
        _id: ObjectId("5a2f5378334c4442ab5a63ea"),
        name: "City 1
    }, 
    {
        _id: ObjectId("59dae1efe408157cc1585fea"),
        name: "City 2
    },
    {
        _id: ObjectId("5a5bbfdc35628410f9fdcde9"),
        name: "City 3
    }
],
"departure_station_id" : [ 
    {
        _id: ObjectId("5a2f53d1334c4442ab5a63ee"),
        name: "Station 1
    }, 
    {
        _id: ObjectId("5a2f53c5334c4442ab5a63ed"),
        name: "Station 2
    },
    {
        _id: ObjectId("5a5bc13435628410f9fdcdea"),
        name: "Station 3
    }
]

}

possibly duplicate https://stackoverflow.com/questions/34967482/lookup-on-objectids-in-an-array — Saikat Hajra, Mar 11 '18 at 03:44
The main difference with this answer is that i have 2 different arrays with different kind of objects. Cities and stations. I can't just unwind because it will merge them. — Michalis, Mar 11 '18 at 06:20

score 0 · Answer 1 · answered Mar 11 '18 at 06:32

The $lookup aggregation pipeline stage NOW works directly with an array (on 3.3.4 version).

See: lookup between local (multiple)array of values and foreign (single) value

The answer of the question is just:

db.getCollection('transportations').aggregate(
{
     $group: {
        _id: null,
        departure_city_id: { $addToSet: "$departure.city_id" },
        departure_station_id: { $addToSet: "$departure.station_id" }
     }
},
{
    $lookup: {
        from: "areas",
        localField: "departure_city_id",
        foreignField: "_id",
        as: "departure_city_id"
    }
},
{
    $lookup: {
        from: "stations",
        localField: "departure_station_id",
        foreignField: "_id",
        as: "departure_station_id"
    }
}
)

Mongodb - $group with $addToSet and then $lookup

1 Answers1