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 istime
field number using$isNumber
if yes then return currenttime
otherwise go to else part$let
to create in-memory variabletime
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
bytime
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 }
])
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.
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}])