Mongodb aggregate lookup return only one field of array

Question

i have some collections for our project.

• Casts collection contains movie casts
• Contents collection contains movie contents

i want to run aggregate lookup for get information about movie casts with position type.

i removed collections details unnecessary fields.

Casts details:

{
    "_id" : ObjectId("5a6cf47415621604942386cd"),
    "fa_name" : "",
    "en_name" : "Ehsan",
    "fa_bio" : "",
    "en_bio" : ""
}

Contents details:

{
    "_id" : ObjectId("5a6b8b734f1408137f79e2cc"),

    "casts" : [ 
        {
            "_id" : ObjectId("5a6cf47415621604942386cd"),
            "fa_fictionName" : "",
            "en_fictionName" : "Ehsan2",
            "positionType" : {
                "id" : 3,
                "fa_name" : "",
                "en_name" : "Director"
            }
        }, 
        {
            "_id" : ObjectId("5a6cf47415621604942386cd"),
            "fa_fictionName" : "",
            "en_fictionName" : "Ehsan1",
            "positionType" : {
                "id" : 3,
                "fa_name" : "",
                "en_name" : "Writers"
            }
        }
    ],
    "status" : 0,
    "created" : Timestamp(1516997542, 4),
    "updated" : Timestamp(1516997542, 5)
}

when i run aggregate lookup with bellow query, in new generated lookup array only one casts contents If in accordance with above casts array value aggregate lookup should return two casts content with two type. in casts array value exists two type of casts, 1) writers and directors. but returned director casts content. _casts should contains two object not one object!

aggregate lookup query:

{$lookup:{from:"casts",localField:"casts._id",foreignField:"_id",as:"_casts"}}

result:

{
        "_id" : ObjectId("5a6b8b734f1408137f79e2cc"),

        "casts" : [ 
            {
                "_id" : ObjectId("5a6cf47415621604942386cd"),
                "fa_fictionName" : "",
                "en_fictionName" : "Ehsan2",
                "positionType" : {
                    "id" : 3,
                    "fa_name" : "",
                    "en_name" : "Director"
                }
            }, 
            {
                "_id" : ObjectId("5a6cf47415621604942386cd"),
                "fa_fictionName" : "",
                "en_fictionName" : "Ehsan1",
                "positionType" : {
                    "id" : 3,
                    "fa_name" : "",
                    "en_name" : "Writers"
                }
            }
        ],
    "_casts" : [ 
           {
            "_id" : ObjectId("5a6cf47415621604942386cd"),
            "fa_name" : "",
            "en_name" : "Ehsan",
            "fa_bio" : "",
            "en_bio" : ""
           }
        ],
        "status" : 0,
        "created" : Timestamp(1516997542, 4),
        "updated" : Timestamp(1516997542, 5)
    }

EDIT-1 finally my problem is solved. i have only one problem with this query, this query doesn't show root document fields. finally solve this problem. finally query exists in EDIT-2.

query:

db.contents.aggregate([ 
{"$unwind":"$casts"},
{"$lookup":{"from":"casts","localField":"casts._id","foreignField":"_id","as":"casts.info"}},
{"$unwind":"$casts.info"},
{"$group":{"_id":"$_id", "casts":{"$push":"$casts"}}},
])

EDIT-2

db.contents.aggregate([ 
{"$unwind":"$casts"},
{"$lookup":{"from":"casts","localField":"casts._id","foreignField":"_id","as":"casts.info"}},
{"$unwind":"$casts.info"},
{$group:{"_id":"$_id", "data":{"$first":"$$ROOT"}, "casts":{"$push":"$casts"}}},
{$replaceRoot:{"newRoot":{"$mergeObjects":["$data",{"casts‌​":"$casts"}]}}},
{$project:{"casts":0}}
]).pretty()

Solution 1

This is expected behavior.

From the docs,

If your localField is an array, you may want to add an $unwind stage to your pipeline. Otherwise, the equality condition between the localField and foreignField is foreignField: { $in: [ localField.elem1, localField.elem2, ... ] }.

So to join each local field array element with foreign field element you have to $unwind the local array.

db.content.aggregate([
  {"$unwind":"$casts"},
  {"$lookup":{"from":"casts","localField":"casts._id","foreignField":"_id","as":"_casts"}}
])

Solution 2

Vendor Collection

Items Collection

db.items.aggregate([
                      { $match:
                              {"item_id":{$eq:"I001"}}
                      },
                      { 
                        $lookup:{  
                                    from:"vendor",
                                    localField:"vendor_id",
                                    foreignField:"vendor_id",
                                    as:"vendor_details"
                                 }
                      },
                      {
                       $unwind:"$vendor_details"
                      },
                      {
                        $project:{ 
                                  "_id":0,
                                  "vendor_id":0,
                                  "vendor_details.vendor_company_description":0,
                                  "vendor_details._id":0,
                                  "vendor_details.country":0,
                                  "vendor_details.city":0,
                                  "vendor_details.website":0
                                  }
                        }
                     ]);

Output

Solution 3

Your Casts collection shows only 1 document. Your Contents collection, likewise, shows only 1 document.

This is 1 to 1 - not 1 to 2. Aggregate is working as designed.

The Contents document has 2 "casts." These 2 casts are sub-documents. Work with those as sub-documents, or re-design your collections. I don't like using sub-documents unless I know I will not need to use them as look-ups or join on them.

I would suggest you re-design your collection.

Your Contents collection (it makes me think of "Movies") could look like this:

_id
title
releaseDate
genre
etc.

You can create a MovieCasts collection like this:

_id
movieId (this is _id from Contents collection, above)
castId (this is _id from Casts collection, below)

Casts

_id
name
age
etc.

Comments

[Ehsan Farahani Asil]

thanks very much for your response. my problem don't solve yet. in contents.casts collection i have 2 object that both of them is same but poisitionType is different. object[1].positionType="Director", object[2].positionType="Writer", castsId both object is same. so result of aggregate should return contentes._casts with two object but contents._casts returned one object!!!

[s7vr]

Np. It returns two one for each cast object but you've to use $unwind.

[Ehsan Farahani Asil]

when i used $unwind instead of returned one document with two object in casts returned two document with exactly casts. please help me how to return one document with two object in casts.

[s7vr]

Okay. You just need to add the $group stage at the end to get back to original array. Try {"$group":{"_id":"$_id", "casts":{"$push":"$casts"}, "_casts":{"$push":"$_casts"} }}

[Ehsan Farahani Asil]

yeah, thanks. work good. my dear i have one question. how to merge _casts data to existing casts object. i mean merge all value of _casts with all value of casts.

[s7vr]

Np. Try db.content.aggregate([ {"$unwind":"$casts"}, {"$lookup":{"from":"casts","localField":"casts._id","foreign‌​Field":"_id","as":"c‌​asts._id"}}, {"$group":{"_id":"$_id", "casts":{"$push":"$casts"}}} ])

[Ehsan Farahani Asil]

thanks very much my friend. you really helped me a lot. your query work good but has a one problem, returned data doesn't have other fields of document. in returned document only has a _id and casts object. how to add other fields automatically without $project.

[s7vr]

You are very welcome. You have to make two changes. First change to get the whole document in $group stage and second change to use $replaceRoot with $mergeObjects to join documents. Something like {"$group":{"_id":"$_id", "data":{"$first":"$$ROOT"}, "casts":{"$push":"$casts"}}}, {"$replaceRoot":{"newRoot":{"$mergeObjects":["$data",{"casts‌​":"$casts"}]}}}. This change requires 3.6 mongo version.

[Ehsan Farahani Asil]

yeahhhhh. all my problems is solved. final query: db.contents.aggregate([ {"$unwind":"$casts"}, {"$lookup":{"from":"casts","localField":"casts._id","foreign‌​Field":"_id","as":"c‌​asts.info"}}, {$group:{"_id":"$_id", "data":{"$first":"$$ROOT"}, "casts":{"$push":"$casts"}}}, {$replaceRoot:{"newRoot":{"$mergeObjects":["$data",{"casts‌​‌​":"$casts"}]}}}, {$project:{"casts":0}} ]). my dear, use multiple pipline query can reduce performance?

[s7vr]

You don't need last project. I've updated my previous comment. About performance it really depends on the volume of data processed, server capacity and network and all. So you have to run some performance test cases for your use case.

[Ehsan Farahani Asil]

db.contents.aggregate([ {"$unwind":"$casts"}, {"$lookup":{"from":"casts","localField":"casts._id","foreign‌​Field":"_id","as":"c‌​asts.info"}}, {"$unwind":"$casts.info"}, {$group:{"_id":"$_id", "data":{"$first":"$$ROOT"}, "casts":{"$push":"$casts"}}}, {$replaceRoot:{"newRoot":{"$mergeObjects":["$data",{"casts‌​‌​":"$casts"}]}}}, {$project:{"casts":0}} ]).pretty() this is last query. if i don't used last project returned document has a two casts object,

[s7vr]

Please make sure you're verifying it correctly. I didn't get a chance to test but it shouldn't be needed. Good luck.