Join lookup object ids in array

user_430112 0
Viewed 1620

I have the business collection with name and array of category object id's and array of brand object id's.

  • business and category is oneToMany
  • business and brand is oneToMany

Example collection structure

categories

{
"_id":ObjectId("595f2311f43c42124360a71f"),
"name":"Women",
}
{
"_id":ObjectId("595f2311f43c42124360a71e"),
"name":"MEN",
}

brands

{
"_id":ObjectId("695f2311f43c42124360a71f"),
"name":"Brand A",
}
{
"_id":ObjectId("695f2311f43c42124360a71e"),
"name":"Brand B",
}

business

{
 "_id":ObjectId("59a7d9e2d290a654c53bb1b6"),
 "name":"My Store",
"brands":[
 ObjectId("596e56489658851024160544"),
 ObjectId("597831971cc07f51bdaabfe6")
],
"categories":[
 ObjectId("595f2311f43c42124360a720"),
 ObjectId("59780cf7bb23af4eced57dba"),
 ObjectId("597f63f642c77654e1c8c574")
]

}

From above structure trying to expose data in the following format using db.createView. So I am trying to look up the categories collection and brand collection to show the names in array instead of the object ids.

Expected output

{
 "_id":ObjectId("59a7d9e2d290a654c53bb1b6"),
 "j_name":"My Store",
"brands":[
 "Brand A",
 "Brand B",

],
"categories":[
 "MEN",
 "WOMEN"
]    
}

What is tried is here but it's not working as expected

db.businesses.aggregate([

       { $unwind: {path:"$categories",preserveNullAndEmptyArrays: true}},
       { $lookup: {
           "from": "categories",
           "localField": "categories",
           "foreignField": "_id",
           "as": "categoryObjects"

            }
        },  
        { $unwind: {path:"$categoryObjects",preserveNullAndEmptyArrays: true}},
        // Group back to arrays
        { "$group": {
            "_id": "$_id",          
            "categoryObjects": { "$push": "$categoryObjects.name" }
        }},     

        { $project : { 
            "_id" : "$_id","j_name" :"$name","j_keywords" : "$keywords","rating":"$rating.overAll","logo":"$logo",
            "j_l_city" : "$address.city","j_l_area" : "$address.area","location":{$concat : ["$address.lat", ",", "$address.lng"]},
            "attr_payments":"$attributes.payments","attr_delivery":"$attributes.delivery","attr_parking":"$attributes.parking",         "attr_locatedAt":"$attributes.locatedAt","attr_tailoring":"$attributes.tailoring","attr_wifi":"$attributes.wifi","attr_kidspark":"$attributes.kidspark",
            "j_categories":"$categoryObjects",
             }}
         ])
aggregation-framework mongodb-query mongodb
1 Answers

A better pipeline would be:

db.business.aggregate([
    { "$lookup": {
        "from": "categories",
        "localField": "categories",
        "foreignField": "_id",
        "as": "categories"
    } },
    { "$lookup": {
        "from": "brands",
        "localField": "brands",
        "foreignField": "_id",
        "as": "brands"
    } },
    { "$addFields": {
        "brands": "$brands.name",
        "categories": "$categories.name"
    } }
]);

which is also the same as 

db.business.aggregate([
    { "$lookup": {
        "from": "categories",
        "localField": "categories",
        "foreignField": "_id",
        "as": "categories"
    } },
    { "$lookup": {
        "from": "brands",
        "localField": "brands",
        "foreignField": "_id",
        "as": "brands"
    } },
    { "$addFields": {
        "brands": {
            "$map": {
                "input": "$brands",
                "as": "brand",
                "in": "$$brand.name"
            } 
        },
        "categories": {
            "$map": {
                "input": "$categories",
                "as": "cat",
                "in": "$$cat.name"
            } 
        }  
    } }
]);
user_1220050
Related