Home > Enterprise >  project field not exist by default value
project field not exist by default value

Time:11-22

I want to project a not exist field to default value

CollectionA

{
  _id: ObjectId("6013859ba0c3120034d08bfa"),
  name: "A1",
  refs:[
   {id: ObjectId("6013859ba0c3120034d08bfb"), text: "ABC"},
   {id: ObjectId("6013859ba0c3120034d08bfc"), text: "DEF"}
  ]
}

CollectionB

{
  _id: ObjectId("6013859ba0c3120034d08bfb"),
  name: "B1",
  altName: "b1"
}

{
  _id: ObjectId("6013859ba0c3120034d08bfc"),
  name: "B2"
}

Expected output

{
    "_id" : ObjectId("6013859ba0c3120034d08bfa"),
    "name" : "A1",
    "refs" : [ 
        {
            "id" : ObjectId("6013859ba0c3120034d08bfb"),
            "text" : "ABC",
            "name" : "B1",
            "altName" : "b1"
        }, 
        {
            "id" : ObjectId("6013859ba0c3120034d08bfc"),
            "text" : "DEF",
            "name" : "B2",
            "altName" : "Unspecified"
        }
    ]
}

In my case, I just want to do this in project expression. I tried with project like in this question

{
    $project: {
      ...,
        altName: {
          $first: { $ifNull: [ "$$refsB.altName", "Unspecified" ] }
        },
      }
    }
  }

CodePudding user response:

You need to retrieve the refsB.altName value via $first first, then with $ifNull to check and assign value if it is null.

altName: {
  $ifNull: [
    {
      $first: "$refsB.altName"
    },
    "Unspecified"
  ]
}

Complete query

db.collectionA.aggregate([
  {
    $unwind: "$refs"
  },
  {
    "$lookup": {
      "from": "collectionB",
      "localField": "refs.id",
      "foreignField": "_id",
      "as": "refsB"
    }
  },
  {
    $project: {
      _id: 1,
      name: 1,
      refs: {
        id: "$refs.id",
        text: "$refs.text",
        name: {
          $first: "$refsB.name"
        },
        altName: {
          $ifNull: [
            {
              $first: "$refsB.altName"
            },
            "Unspecified"
          ]
        },
        
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      name: {
        $first: "$name"
      },
      refs: {
        $push: "$refs"
      }
    }
  }
])

Sample Mongo Playground

  • Related