CODEWITHSUNDEEP
node.jsmongodbmongoose
Ghost
Ghost

Reputation: 101

use aggregate to combine two fields

i have this data 

{
  "FirstName": null,
  "LastName": "natra",
  "Gender": null,
  "IsActive": true,
},
{
  "FirstName": "abiy",
  "LastName": "jagema",
  "Gender": "Female",
  "IsActive": true,
},

what i want is to get a data like this 

{
  "FullName": "abiy Jagema",
  "Gender": "Female",
  "IsActive": true,
},
{
  "FullName": Natra,
  "Gender": null,
  "IsActive": true,
},

so if both first name and last name exist concat else just set last name to full name using aggregate i tried it like this but if first name is missing it will put null on full name

User.aggregate([
  { $match: { IsActive: true } },
  {
    $project: {
      Gender: 1,
      IsActive: 1,
      FullName: {
        $cond: {
          if: "$LastName",
          then: { $concat: ["$FirstName", " ", "$LastName"] },
          else: "$FirstName"
        }
      }
    }
  }
]);

Upvotes: 2

Views: 340

Answers (4)

Abdus Sattar
Abdus Sattar

Reputation: 289

Little modification in the condition.

User.aggregate([
  { $match: { IsActive: true } },
  {
    $project: {
      Gender: 1,
      IsActive: 1,
      FullName: {
        $cond: {
          if: { $eq: [ "$FirstName", null ] },
          then: "$LastName", 
          else: { $concat: [ "$FirstName", " ", "$LastName" ] } }
        }
      }
    }
  }
]);

Upvotes: 1

Dexter
Dexter

Reputation: 982

This works for me: 

db.aggtest.aggregate([ { $match: { IsActive: true } }, 
{ $project: { "_id": 0, "Gender": 1, "IsActive": 1, "FullName": { $concat: [ { $ifNull: [ "$FirstName", "" ] }, " ", 
{ $ifNull: [ "$LastName", "" ]} ]} }} ])

Upvotes: 0

Gaurav
Gaurav

Reputation: 116

User.aggregate([
  { $match: { IsActive: true } },
  {
    $project: {
      Gender: 1,
      IsActive: 1,
      FullName: {
      $concat: [
      {
        $ifNull: [
          "$FirstName",
          ""
        ]
      },
      " ",
      {
        $ifNull: [
          "$LastName",
          ""
        ]
      }
     ]
    }
   }
  }
]);

Upvotes: 0

sushant mehta
sushant mehta

Reputation: 1274

First, check if firstName is null if null set it to "" else append " " to firstName and concat it to lastName

User.aggregate([
  {
    $match: {
      IsActive: true
    }
  },
  {
    $project: {
      Gender: 1,
      IsActive: 1,
      FirstName: {
        $cond: {
          if: {
            $eq: [
              "$FirstName",
              null
            ]
          },
          then: "",
          else: {
            $concat: [
              "$FirstName",
              " "
            ]
          }
        }
      },
      LastName: {
        $cond: {
          if: {
            $eq: [
              "$LastName",
              null
            ]
          },
          then: "",
          else: "$LastName"
        }
      }
    }
  },
  {
    $project: {
      Gender: 1,
      IsActive: 1,
      FullName: {
        $concat: [
          "$FirstName",
          "$LastName"
        ]
      }
    }
  }
])

Upvotes: 0

Related Questions