Replace empty array of unwind

[Nabeel_Raza (Nabeel Raza Naqeebi)]

Hey, I want to know that if i have multiple collections and we are using lookup on them and there is a possibility that some of the collections are empty and i want to add/replace them with some data.

-- current Output --

/* 1 */
{
“c_id” : “000074819”,
“u_id” : 106.0,
“user_A” : ,
“user_B” : ,
“user_C” : [
{
“name” : “XYZ”,
“currency” : “AED”,
“c_id” : “000074819”,
“status” : “C”
}
],
“user_D” :
}

-- Required Output --

/* 1 */
{
“c_id” : “000074819”,
“u_id” : 106.0,
“user_A” : [
{
“c_id” : “000074819”,
}],
“user_B” : [
{
“c_id” : “000074819”,
}],
“user_C” : [
{
“name” : “XYZ”,
“currency” : “AED”,
“c_id” : “000074819”,
“status” : “C”
}
],
“user_D” : [
{
“c_id” : “000074819”,
}]
}

Thanks in advance.

[Nabeel_Raza (Nabeel Raza Naqeebi)]

These are square brackets i.e. [ ]

[slava]

Hello, @Nabeel_Raza!

You can do it with $set (alias for $addFields) pipeline stage and $cond pipeline operator.
Let me show how it is done by an example.

First, we make a sample minimal dataset to work with:

db.players.insertMany([
  {
    _id: 'P1',
    name: 'William',
    teamId: 'T1',
  },
  {
    _id: 'P2',
    name: 'Deborah',
    // notice, that there is not T2 team in the 'teams' collection
    teamId: 'T2', 
  },
]);

db.teams.insertMany([
  {
    _id: 'T1',
    title: 'Angry Beavers',
  },
]);

Then, we run this aggregation, against the above dataset:

db.players.aggregate([
  {
    $lookup: {
      from: 'teams',
      as: 'team',
      localField: 'teamId',
      foreignField: '_id',
    },
  },
  {
    $set: {
      team: {
        $cond: {
          if: {
            // if nothing joined from
            // the 'teams' collection (empty array)
            $eq: [{
              $size: '$team',
            }, 0],
          },
          then: {
            // then define arbitrary object
            // as a replacement here
            _id: '$teamId',
            isMissing: true,
          },
          else: {
            // otherwise, pick that single object
            // from array, (if we always join 1 object with $lookup)
            $arrayElemAt: ['$team', 0],
          },
        },
      },
    },
  },
]).pretty();

That will give this output:

[
  {
    "_id" : "P1",
    "name" : "William",
    "teamId" : "T1",
    "team" : {
      "_id" : "T1",
      "title" : "Angry Beavers"
    }
  },
  {
    "_id" : "P2",
    "name" : "Deborah",
    "teamId" : "T2",
    "team" : {
      "_id" : "T2",
      "isMissing" : true
    }
  }
]

Notice, that for T2 team’s object is composed in the aggregation pipeline, and is not joined from the ‘teams’ collection.

The same aggregation would produce the same result, if we make joins from non-existent collections.

[Nabeel_Raza (Nabeel Raza Naqeebi)]

@slava

db.players.aggregate([
  {
    $lookup: {
      from: 'teams',
      as: 'team',
      localField: 'teamId',
      foreignField: '_id',
    },
  },
  {
    $set: {
      team: {
        $cond: {
          if: {
            // if nothing joined from
            // the 'teams' collection (empty array)
            $eq: [{
              $size: '$team',
            }, 0],
          },
          then: {
            // then define arbitrary object
            // as a replacement here
            url: '$teamId',
            isMissing: true,
          },
          else: {
            // otherwise, pick that single object
            // from array, (if we always join 1 object here)
            $arrayElemAt: ['$team', 0],
          },
        },
      },
    },
  },
]).pretty();

Thanks @slava
This work for me.

[Nabeel_Raza (Nabeel Raza Naqeebi)]

Here are more detail about set(aggregation) clause in mongodb: https://docs.mongodb.com/manual/reference/operator/aggregation/set/index.html.