MongoDB - How to compute field based on other fields existence?

Question

Suppose I have a trips collection where I would like to compute the status of each trip. The status is calculated based on other fields.

• If the trip has no driver it should be pending.
• If the trip has a driver it should be in transit.
• If the trip has a driver and an invoice number it should be completed.
• If the trip has a driver and an invoice number and a bill number it should be billed.

How could I implement this using the aggregation pipeline?

Example docs :

[{
    // This has both driver and invoice, should be completed
    "_id" : ObjectId("5e24fbfd44621900c5730a48"),
    "customer" : ObjectId("5dd7eaf7ef8a7b00ba8f090b"),
    "date" : ISODate("2020-01-17T03:00:00.000Z"),
    "distance" : 24, // in km
    "driver" : ObjectId("5e1e302e26f00000c451923e"),
    "invoice" : "0001-00001234",
    "status": "completed" // this should be calculated

},
{
    // This has only driver, should be in transit
    "_id" : ObjectId("5e24fbfd44621900c5730a48"),
    "customer" : ObjectId("5dd7eaf7ef8a7b00ba8f090b"),
    "date" : ISODate("2020-01-17T03:00:00.000Z"),
    "distance" : 24, // in km
    "driver" : ObjectId("5e1e302e26f00000c451923e"),
    "status": "in transit" // this should be calculated

},
{
    // This is missing both driver and invoice, should be pending
    "_id" : ObjectId("5e24fbfd44621900c5730a48"),
    "customer" : ObjectId("5dd7eaf7ef8a7b00ba8f090b"),
    "date" : ISODate("2020-01-17T03:00:00.000Z"),
    "distance" : 24, // in km
    "status": "pending" // this should be calculated
}]

Answer 1

One solution would be this one:

db.col.updateMany(
   {
      driver: { $exists: true },
      invoice: { $exists: true }
   },
   { $set: {status: "completed"}}
)

db.col.updateMany(
   {
      driver: { $exists: false },
      invoice: { $exists: false }
   },
   { $set: {status: "pending"}}
)

db.col.updateMany(
   {
      driver: { $exists: true },
      invoice: { $exists: false }
   },
   { $set: {status: "in transit"}}
)

Or in a single command witn Aggregation pipeline (Starting in MongoDB 4.2):

db.col.updateMany(
   {},
   [{
      $set: {
         status: {
            $switch: {
               branches: [
                  { case: { $and: ["$driver", "$invoice"] }, then: "completed" },
                  { case: { $and: ["$driver", { $not: "$invoice" }] }, then: "in transit" },
                  { case: { $and: [{ $not: "$driver" }, { $not: "$invoice" }] }, then: "pending" },
               ],
               default: "$status"
            }
         }
      }
   }]
)

Answer 2

You can try this, it uses $switch to check all cases in series & $type check for field existence :

db.trips.aggregate([{
    $addFields: {
        status: {
            $switch:
            {
                branches: [
                    {
                        case: { $eq: [{ $type: '$driver' }, 'missing'] },
                        then: 'pending'
                    },
                    {
                        case: { $eq: [{ $type: '$driver' }, 'objectId'] },
                        then: 'in transit'
                    },
                    {
                        case: {
                            $and: [{ $eq: [{ $type: '$driver' }, 'objectId'] },
                            { $eq: [{ $type: '$invoice' }, 'string'] }]
                        },
                        then: "completed"
                    },
                    {
                        case: {
                            $and: [{ $eq: [{ $type: '$driver' }, 'objectId'] },
                            { $eq: [{ $type: '$invoice' }, 'string'] },
                            { $eq: [{ $type: '$billNumber' }, 'string'] }]
                        },
                        then: "billed"
                    }
                ],
                default: '$status'
            }
        }
    }
}
])

Ref : MongoDB-Playground