MongoDB compass - Get the field name (key) with the max value, from 3 fields and their values in a document

Question

I have this sample mongodb document -

{
    _id: 5db85ee97d9fb13ead4fc54c
    applId: 5d48f34f7d9fb10ce171f905
    fileId: "dd386cf7-4139-45c2-9853-cbb126621b51"
    job: Object
    country: "US"
    fullName: "abcd xyz"
    htmlWordCount: 2766
    textWordCount: 1867
    rchilliTextWordCount: 2840
    deleted: 0
    dateEntered: 2019-10-29 15:46:49.237
    dateModified: 2019-10-29 15:46:49.237
}

I want to build a query in compass so that I have following fields in the output -

{
    _id: 5db85ee97d9fb13ead4fc54c
    country: "US"
    fullName: "abcd xyz"
    htmlWordCount: 2766
    textWordCount: 1867
    rchilliTextWordCount: 2840
    winner: "rchilliTextWordCount"
}

Please note that it has a new field called "winner" which always returns the column with maximum wordcount (out of 3 "htmlWordCount", "textWordCount", "rchilliTextWordCount" columns). This new column "winner" is to be produced on runtime on query. Also this query is filtered on country = "US".

How do I do this in MongoDB Compass or what should the aggregation pipeline look like?

Answer 1

You may use $switch or $cond

db.collection.aggregate([
  {
    $match: {
      country: "US"
    }
  },
  {
    $project: {
      country: 1,
      fullName: 1,
      htmlWordCount: 1,
      textWordCount: 1,
      rchilliTextWordCount: 1,
      winner: {
        $switch: {
          branches: [
            {
              case: {
                $and: [
                  {
                    $gt: [
                      "$htmlWordCount",
                      "$textWordCount"
                    ]
                  },
                  {
                    $gt: [
                      "$htmlWordCount",
                      "$rchilliTextWordCount"
                    ]
                  }
                ]
              },
              then: "htmlWordCount"
            },
            {
              case: {
                $and: [
                  {
                    $gt: [
                      "$textWordCount",
                      "$htmlWordCount"
                    ]
                  },
                  {
                    $gt: [
                      "$textWordCount",
                      "$rchilliTextWordCount"
                    ]
                  }
                ]
              },
              then: "textWordCount"
            },
            {
              case: {
                $and: [
                  {
                    $gt: [
                      "$rchilliTextWordCount",
                      "$htmlWordCount"
                    ]
                  },
                  {
                    $gt: [
                      "$rchilliTextWordCount",
                      "$textWordCount"
                    ]
                  }
                ]
              },
              then: "rchilliTextWordCount"
            }
          ],
          default: "No winners"
        }
      }
    }
  }
])

MongoPlayground