CODEWITHSUNDEEP
mongodbaggregation-framework
Remember_me
Remember_me

Reputation: 283

Mongo db aggregation with $lookup

I m trying to build a useful report for a page I m creating, but I m having issues with formatting the data, take a look at this below :

db.getCollection('store_spendings').aggregate(
{
  "$project": { "spend_dollars": 1, "comments.text": 1, "comments.user.user_id": 1, "store_id": 1 }
},
{
  "$lookup":
     {
        "from": "corporate_spendings",
        "localField": "store_id",
        "foreignField": "store_id",
        "as": "corporate_spendings"
    }
},
{
  "$match": { "corporate_spendings.region_id": 10816 }  
},
{
  "$project": { "spend_dollars": "$spend_dollars", "comments": 1, "prior_year_spend_dollars": "$corporate_spendings.prior_year_spend_dollars" } }
)

I get a result like this :

[
  spend_dollars: 321312,
  comments: [...comments data],
  prior_year_spend_dollars: [1231231],
  ...
]

I get correct results except the format of data is a bit off for prior_year_spend_dollars. I would like it to be a field just like spend_dollars rather than an array.

I've tried using $first but apparently it only works with group. Right now I do this 'first' operation in my app, but ideally it would be done on the db.

My ideal output with be like this :

[
  spend_dollars: 321312,
  comments: [...comments data],
  prior_year_spend_dollars: 1231231,
  ...
]

How can I do that?

Upvotes: 0

Views: 79

Answers (1)

s7vr
s7vr

Reputation: 75984

Try arrayElemAt operator in project stage which lets you pick value from array based on index.

 "prior_year_spend_dollars": 
{"$arrayElemAt": ["$corporate_spendings.
prior_year_spend_dollars", 0]}

Upvotes: 1

Related Questions