How to add single field using $lookup?

[blue_puma]

How can I use aggregate $lookup to include a single value from another collection as (root) object?

stati: [
    { id: 1, str: 'ordered' },
    { id: 2, str: 'packed' },
    { id: 3, str: 'shipped' },
]

orders: [
    { id: 100, status: 3, strDate: '2021-03-01', items: [] },
    { id: 101, status: 2, strDate: '2021-04-01', items: [] },
    { id: 102, status: 1, strDate: '2021-04-01', items: [] },
]

result: [
    { id: 100, status: 3, strDate: '2021-03-01', items: [], strStatus: 'shipped' },
    { id: 101, status: 2, strDate: '2021-04-01', items: [], strStatus: 'packed' },
    { id: 102, status: 1, strDate: '2021-04-01', items: [], strStatus: 'ordered' },
]

Thanks,
bluepuma

[blue_puma]

I found a working solution, but I am wondering if it really has to be so complicated.

It seems very expensive to $lookup and $unwind an array instead of just integrating the single field.

db.orders.aggregate({
  $lookup: {
    from: "stati",
    let: {
      status: "$status"
    },
    pipeline: [
      {
        $match: {
          $expr: {
            $eq: [
              "$id",
              "$$status"
            ]
          }
        }
      },
      {
        $project: {
          str: 1
        }
      }
    ],
    as: "_strStatus"
  }
},
{
  $unwind: {
    path: "$_strStatus",
    preserveNullAndEmptyArrays: false
  }
},
{
  $set: {
    strStatus: "$_strStatus.str"
  }
},
{
  $project: {
    _strStatus: 0
  }
})

[blue_puma]

$lookup/$unwind works, but seems to me expensive from a computational perspective.

MongoDB Playground

Is there a way to fetch a field value from a 1-to-1 relationship without the $lookup/$unwind combination?

[turivishal (Vishal Turi)]

Hello @blue_puma Welcome to MongoDB Community Forum,

You can use $lookup without pipeline,

• $lookup with stati collection, pass status as localField and pass id as foreignField
• $set to show str status from above lookup result, $arrayElemAt to get first element from lookup result strStatus.str.

db.orders.aggregate([
  {
    $lookup: {
      from: "stati",
      localField: "status",
      foreignField: "id",
      as: "strStatus"
    }
  },
  {
    $set: {
      strStatus: { $arrayElemAt: ["$strStatus.str", 0] }
    }
  }
])