CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Tony Mathew
Tony Mathew

Reputation: 910

Mongodb - Add extra fields based on other field value

I have documents in db in the following format: 

{
"_id" : ObjectId("5d6fb50852020c4a182fc773"),
"startTimestamp" : "1567601927157"
}

What I want to achieve is, using the "startTimestamp" value, create the following new fields:

Can I get a query to do the above operation in all the documents and finally create respective documents in the following format:

{
"startTimestamp" : "1567601927157",
"date" : "04-09-2019",
"hour" : "18",
"month" : "9",
"time" : "18:28:47",
"weekDay" : "Wednesday",
}

Edit:
"startTimestamp" is not the only field present in the documents, it has other fields as well, like below: 

 {
 "useCaseStatus" : "In Progress",
"feedbackRequested" : false,
"userFeedback" : null,
"startTimestamp" : "1567669352778"
 }

By adding new fields to the above document, I dont want to delete the fields that are already present(because all the solutions I have got so far removes the other fields present in the documents). Also, adding one more expected document below (Please note that hour and month fields are in string format, not int): 

{
 "useCaseStatus" : "In Progress",
"feedbackRequested" : false,
"userFeedback" : null,
"startTimestamp" : "1567669352778",
"endTimestamp" : null,
"date" : "05-09-2019",
"hour" : "13",
"month" : "9",
"time" : "13:12:32",
"weekDay" : "Thursday"
}

Upvotes: 5

Views: 1534

Answers (3)

Ashh
Ashh

Reputation: 46481

You can use below aggregation

db.collection.aggregate([
  { "$replaceRoot": {
    "newRoot": {
      "$let": {
        "vars": { "date": { "$toDate": { "$toLong": "$startTimestamp" } } },
        "in": {
          "$mergeObjects": [
            {
              "date": { "$dateToString": { "date": "$$date", "format": "%d-%m-%Y" } },
              "month": { "$toString": { "$month": "$$date" } },
              "hour": { "$toString": { "$hour": "$$date" } },
              "time": { "$dateToString": { "date": "$$date", "format": "%H-%M-%S" } },
              "weekDay": { "$dayOfWeek": "$$date" }
            },
            "$$ROOT"
          ]
        }
      }
    }
  }},
  { "$out": "collectionName" }
])

Output

{
  "date": "04-09-2019",
  "hour": 12,
  "month": 9,
  "startTimestamp": "1567601927157",
  "time": "12-58-47",
  "weekDay": 4
}

Upvotes: 3

sushant mehta
sushant mehta

Reputation: 1284

You need to implement aggregate pipeline and use date operators available but as you have millisecond saved in string first we have to convert it to int then date then perfrom date operators notice some of them will need timezone to give accurate result instead will just give utc results

db.collection.aggregate([
  {
    $addFields: {
      longMillis: {
        $toLong: "$startTimestamp"
      }
    }
  },
  {
    $project: {
      startTimestamp: 1,
      "date": {
        "$add": [
          new Date(0),
          "$longMillis"
        ]
      }
    }
  },
  {
    $project: {
      startTimestamp: 1,
      month: {
        $month: "$date"
      },
      day: {
        $switch: {
          branches: [
            {
              case: {
                $eq: [
                  {
                    $dayOfMonth: "$date"
                  },
                  1
                ]
              },
              then: "Sunday"
            },
            {
              case: {
                $eq: [
                  {
                    $dayOfMonth: "$date"
                  },
                  2
                ]
              },
              then: "Monday"
            },
            {
              case: {
                $eq: [
                  {
                    $dayOfMonth: "$date"
                  },
                  3
                ]
              },
              then: "Tuesday"
            },
            {
              case: {
                $eq: [
                  {
                    $dayOfMonth: "$date"
                  },
                  4
                ]
              },
              then: "Wednesday"
            },
            {
              case: {
                $eq: [
                  {
                    $dayOfMonth: "$date"
                  },
                  5
                ]
              },
              then: "Thursday"
            },
            {
              case: {
                $eq: [
                  {
                    $dayOfMonth: "$date"
                  },
                  6
                ]
              },
              then: "Friday"
            },
            {
              case: {
                $eq: [
                  {
                    $dayOfMonth: "$date"
                  },
                  7
                ]
              },
              then: "Saturday"
            },

          ],
          default: 6
        }
      },
      hour: {
        $hour: {
          "date": "$date",
          "timezone": "+05:30"
        }
      },
      date: {
        $dateToString: {
          format: "%d-%m-%Y",
          date: "$date"
        }
      },
      time: {
        $dateToString: {
          format: "%H:%M:%S",
          date: "$date",
          timezone: "+05:30"
        }
      },

    }
  }
])

Giving result:

[
  {
    "date": "04-09-2019",
    "day": "Wednesday",
    "hour": 18,
    "month": 9,
    "startTimestamp": "1567601927157",
    "time": "18:28:47"
  }
]

Upvotes: 0

mickl
mickl

Reputation: 49985

You need to start with $toLong and $toDate to parse your string. Then you can use $dateToParts and $dayOfWeek. To translate number into string you can use $switch

db.collection.aggregate([
    {
        $addFields: {
            date: {
                $toDate: {
                    $toLong: "$startTimestamp"
                }
            }
        }
    },
    {
        $addFields: {
            dateParts: { $dateToParts: { date: "$date" } },
            dayOfWeek: { $dayOfWeek: "$date" }
        }
    },
    {
        $project: {
            startTimestamp: 1,
            date: { $dateToString: { date: "$date", format: "%d-%m-%Y" } },
            hour: "$dateParts.hour",
            month: "$dateParts.month",
            time: { $dateToString: { date: "$date", format: "%H:%M:%S" } },
            weekDay: {
                $switch: {
                    branches: [
                        { case: { $eq: [ "$dayOfWeek", 1 ] }, then: "Sunday" },
                        { case: { $eq: [ "$dayOfWeek", 2 ] }, then: "Monday" },
                        { case: { $eq: [ "$dayOfWeek", 3 ] }, then: "Tuesday" },
                        { case: { $eq: [ "$dayOfWeek", 4 ] }, then: "Wednesday" },
                        { case: { $eq: [ "$dayOfWeek", 5 ] }, then: "Thursday" },
                        { case: { $eq: [ "$dayOfWeek", 6 ] }, then: "Friday" }
                    ],
                    default: "Saturday"
                }
            }
        }
    }
])

Mongo Playground

Upvotes: 1

Related Questions