Mongodb aggregate unexpected array result - Needs to return an object from $lookup instead of an array

Question

I'm joining two collections, but the result of trends is an array, each item always has only one trend, how do I remove the trend inside array?

'Items' collection:

{
 "itemid" : "370",
 "name" : "A"
},
{
"itemid" : "378",
 "name" : "B"
}

'Trends' collection

{
 "itemid" : "370",
 "max" : "715705",
},
{
 "itemid" : "378",
 "max" : "35346",
}

Command executed:

db.items.aggregate([
{
    $lookup: {
            from: "trends",
            localField: "itemid",
            foreignField: "itemid",
            as: "trend"
            }
    }
])

Result:

{
 "itemid" : "370",
 "name" : "A",
 "trend" : [       // unexpected array, the result is always a single 'trend'
        {
          "itemid" : "370",
          "max" : "715705",
        }
    ]
},
...

Expected:

{
 "itemid" : "370",
 "name" : "A",
 "trend" : {      // yeah, without array          
             "itemid" : "370",
             "max" : "715705",                    
            }
},
...

Raul Sanchez Reyes · Accepted Answer · 2020-01-13 20:04:43Z

3

You can do it with $unwind pipeline stage

db.items.aggregate([
{
    $lookup: {
            from: "trends",
            localField: "itemid",
            foreignField: "itemid",
            as: "trend"
            }
    },
    $unwind:"$trend"
])

answered Jan 13, 2020 at 20:04

Raul Sanchez Reyes

1515 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-01-13 20:48:59Z

2

It's not an unexpected result, as $lookup would result in retrieving all matched documents into an array i.e; trend will be an array in your case. It's for a reason, Assume if you're joining collection A with collection B & documents in collection A might multiple matching documents in collection B then all those documents from collection B are pulled into an array of that relative document of collection A in your case trend field. Here if you wanted to get object inside it you need to do one of these ::

db.items.aggregate([
{
    $lookup: {
            from: "trends",
            localField: "itemid",
            foreignField: "itemid",
            as: "trend"
            }
    },
    { $addFields: { trend: {$arrayElemAt : ["$trend", 0] } } } // This stage is going re-write trend field with first object from trend array created by $lookup stage.
])

(Or)

db.items.aggregate([
{
    $lookup: {
            from: "trends",
            localField: "itemid",
            foreignField: "itemid",
            as: "trend"
            }
    },
     { $unwind: { path: "$trend", preserveNullAndEmptyArrays: true } } 
])

Note : When you use $unwind you need to be cautious that you need to use preserveNullAndEmptyArrays option as if trend is an [] - which means no match found in collection B, then $unwind is going to remove all collection A documents with trend :[] in the final aggregation result.

answered Jan 13, 2020 at 20:48

whoami - fakeFaceTrueSoul

18.1k6 gold badges35 silver badges51 bronze badges

1 Comment

whoami - fakeFaceTrueSoul Over a year ago

@ViniciusRaupp : Yes it might work, but I'm giving you all the possible scenarios.If you might need all docs from collection A though there is no match in collection B i.e; trend : [], So here $unwind will remove this kind of docs. That's why we need preserveNullAndEmptyArrays: true. As $unwind can be tricky cause if your aggregation returns 100s or 1000s of documents then you might not be able to validate whether query really did work to return all docs or not. So it's better to use it rather than loose few docs :-)

Collectives™ on Stack Overflow

Mongodb aggregate unexpected array result - Needs to return an object from $lookup instead of an array

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related