Question 240383 in Mongodb Question posted in
The official documentation can be found here.

How to project specific fields from subdocument baed on value of field from same document in MongoDB

Given a document with a subdocument from a $lookup aggregation, I want to project a specific field form the subdocument based on the field of the $$ROOT document. Example:

{
   "date": "2023-08-20",
   "code": "USD",
   "exchange_rates": {
      "USD": 1.01,
      "EUR": 2.01,
      "CNY": 3.01
   }
}

I want to get:

{
   "date": "2023-08-20",
   "exchange_rate": 1.01
}

This subdocument came from a $lookup operation, so maybe I can put the project (or any other command) directly inside the pipeline argument from this aggregation directly.

Tags:

2

Answers


  1. 0

    One option is:

    db.collection.aggregate([
//{$lookup...}
  {$project: {
      _id: 0,
      date: 1,
      exchange_rate: {$getField: {
          input: {$first: {$filter: {
                input: {$objectToArray: "$exchange_rates"},
                cond: {$eq: ["$$this.k", "$code"]}
          }}},
          field: "v"
      }}
  }}
])

    See how it works on the playground example

    Login or Signup to reply.
  2. 0

    Given I have the following collections:

    • one, which contains:

      {
    "date": "2023-08-20",
    "code": "USD",
    "exchange_rates": {
       "USD": 1.01,
       "EUR": 2.01,
       "CNY": 3.01
    }
 }

    • two, which contains: {date: '2023-08-20'}

    I can use the following pipeline to get the desired result from collection one, based on the date. I used $addFields to override the exchange_rates to USD.

    db.two.aggregate(
    [
        {
            $lookup: {
                from: 'one',
                as: 'exchange',
                let: {date: '$date'},
                pipeline: [
                    {
                        $match: {
                            $expr: {
                                $eq: ['$date', '$$date']
                            }
                        }
                    },
                    {
                        $addFields: {
                            "exchange_rates": "$exchange_rates.USD"
                        }
                    }
                ]
            }
        },
        { $unwind: "$exchange"},
        { $replaceRoot: { newRoot: "$exchange" } },
        { $project: {_id: 0, date: 1, "exchange_rates": 1} }
    ]
)

    Will result in:

    [
  {
    "date": "2023-08-20",
    "exchange_rates": 1.01
  }
]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search