MongoDB aggregate add new field with multiple conditions

Question

I cannot figure to add a new field with multiple conditions in MongoDB. Here is how DB looks like,

const db = [
  {
    _id: 1,
    isActived: false, // Drafted
    isScheduled: false,
    isExpired: false
  },
  {
    _id: 2,
    isActived: true, 
    isScheduled: true, // Scheduled
    isExpired: false
  },
  {
    _id: 3,
    isActived: true, // Expired
    isScheduled: false,
    isExpired: true
  },
  {
    _id: 4,
    isActived: true, // Actived
    isScheduled: false,
    isExpired: false
  },
]

Conditions are:

if (!isActived) status = "Draft"
if (isActived && isScheduled) status = "Scheduled"
if (isActived && isExpired) status = "Expired"
if (isActived && !isScheduled && !isExpired) status = "Actived"

I can figure out only one condition and cannot solve it any further. Here is what I did so far

{
      $addFields: {
        status: {
          $cond: [
            {
              $and: [
                { $eq: ["$isActived", true] },
                { $eq: ["$isScheduled", true] },
              ],
            },
            "Actived",
            "Scheduled",
          ],
          $cond: [
            {
              $and: [
                { $eq: ["$isActived", true] },
                { $eq: ["$isExpired", true] },
              ],
            },
            "Actived",
            "Expired",
          ],
        },
      },
    }

The result is not what I expected. Any suggestion. Thank You.

Answer 1

Simple nested conditions, working solution

db.collection.aggregate([
    {
        $addFields: {
        status: {
            $cond: [
            {
                $and: [
                { $eq: [ "$isActived", true ] },
                { $eq: [ "$isScheduled", false ] },
                { $eq: [ "$isExpired", false ] },
                ],
            },
            "Actived",
            {
                $cond: [
                {
                    $and: [
                    { $eq: [ "$isActived", true ] },
                    { $eq: [ "$isExpired", true ] },
                    ],
                },
                "Expired",
                {
                    $cond: [
                    {
                        $and: [
                        { $eq: [ "$isActived", true ] },
                        { $eq: [ "$isScheduled", true ] },
                        ],
                    },
                    "Scheduled",
                    {
                        $cond: [
                        {
                            $and: [
                            { $eq: [ "$isActived", false ] },                          
                            ],
                        },
                        "Draft",
                        null,
                        ],
                    },
                    ],
                },
                ],
            },
            ],
        },
        },
    },
])

Answer 2

You can concatenate if/else in this way:

Pseudocode:

if !isActived:
  return "Draft"
else
  if !isScheduled && !isExpired:
    return "Actived"
  else
    if isScheduled:
      return "Scheduled"
    else
      return "Expired" //<-- Since one of "isScheduled" and "isExpired" should be true

Take care on last line where may you can have isScheduled and isExpired to true, so you logic can change.

db.collection.aggregate([
  {
    "$set": {
      "status": {
        "$cond": {
          "if": {
            "$eq": [
              "$isActived",
              false
            ]
          },
          "then": "Draft",
          "else": {
            "$cond": {
              "if": {
                "$and": [
                  {
                    "$eq": [
                      "$isExpired",
                      false
                    ]
                  },
                  {
                    "$eq": [
                      "$isScheduled",
                      false
                    ]
                  }
                ]
              },
              "then": "Actived",
              "else": {
                "$cond": {
                  "if": {
                    "$eq": [
                      "$isScheduled",
                      true
                    ]
                  },
                  "then": "Scheduled",
                  "else": "Expired"
                }
              }
            }
          }
        }
      }
    }
  }
])

Example here