I have collection - songs

I need to find the longest song, but the length of the song comes in two formats: seconds and HH:MM:SS.

{
  "_id": "61179c8d43400f31df195223",
  "name": "song1",
   "time": 2000
},
{
  "_id": "61179c8341f00f31df159223",
  "name": "song2",
  "time": "00:34:16"
},
{
  "_id": "61179c8341f00f31df195223",
  "name": "song3",
  "time": 2800
},
{
  "_id": "61179c8380f00f31df195223",
  "name": "song4",
  "time": "00:01:10"
}
,{
  "_id": "61179c8310f00f31df195223",
  "name": "song5",
  "time": "00:15:10"
}

CodePudding user response：

There is no straight way to do this, you can try aggregation pipeline,

• $addFields to add or edit a field
• $cond to check is time field number using $isNumber if yes then return current time otherwise go to else part
• $let to create in-memory variable time after splitting it using $split by ":"
• $arrayElemAt to get specific element form above split array
• $toInt to convert split numbers to integer
• $multiply to multiply hours, minutes to a specific number to convert it in seconds
• $sum to get total seconds
• $sort by time in descending order
• $limit to get first document

db.collection.aggregate([
  {
    $addFields: {
      time: {
        $cond: [
          { $isNumber: "$time" },
          "$time",
          {
            $let: {
              vars: {
                time: { $split: ["$time", ":"] }
              },
              in: {
                $sum: [
                  {
                    $multiply: [
                      { $toInt: { $arrayElemAt: ["$$time", 0] } },
                      3600
                    ]
                  },
                  {
                    $multiply: [
                      { $toInt: { $arrayElemAt: ["$$time", 1] } },
                      60
                    ]
                  },
                  { $toInt: { $arrayElemAt: ["$$time", 2] } }
                ]
              }
            }
          }
        ]
      }
    }
  },
  { $sort: { time: -1 } },
  { $limit: 1 }
])

Playground

CodePudding user response：

Query

• adds a new field timeSec, to store the time in seconds
• if time not a string we keep the time (its already in second)
• else
• split the string to the parts for example [1, 2, 10]
• $zip the parts with their multipliers [[1 3600] [2 60] [10 1]] (1 hour = 3600 sec etc)
• reduce to sum after we multiple the array members

*if its nested alot, you can un-nest it using more tempFields to be more readable.

Test code here

aggregate(
[{$set:
  {timeSec:
   {$cond: 
    [{$eq:[{$type:"$time"}, "string"]},
     {$reduce:
      {input:
       {$zip:{inputs:[{$split:["$time", ":"]}, [3600, 60, 1]]}},
       initialValue:0,
       in:
       {$add:
        ["$$value",
         {$multiply:
          [{$toInt:{$arrayElemAt:["$$this", 0]}},
           {$arrayElemAt:["$$this", 1]}]}]}}},
     "$time"]}}},
 {$sort:{timeSec:-1}},
 {$limit:1}])