Mixing
Multiple timerange group in days of data
1
I am trying to get the first and last document of three different time ranges within 24 hours and then group it by days.
Time Ranges in a day
Day 1
T1 : 06:00 - 17:00 - Get the first and last document
T2 : 17:00 - 22:00 - Get the first and last document
T3 : 22:00 - 06:00 - Get the first and last document
Day 2.. so on
Sample Document
{
"_id" : ObjectId("5ba6bc27260d0909e43b0874"),
"_DeviceName" : "Ground Floor",
"PointType" : "Building",
"_DeviceID" : ObjectId("5b9ae3a76080700be0f173d6"),
"_TariffID" : ObjectId("5a8893216b41bd32c0797f91"),
"_Timestamp" : ISODate("2018-09-22T22:03:18.552+0000"),
"_ModbusID" : 1,
"_LocationID" : ObjectId("5b9ae0eb6080700be0f173bf"),
"Registers" : {
"quadrant4reactiveenergyL3" : 781.0,
"quadrant4reactiveenergyL2" : 74.0,
"quadrant4reactiveenergyL1" : 144.0,
"quadrant1reactiveenergyL3" : 52.0,
"quadrant1reactiveenergyL2" : 706.0,
"quadrant1reactiveenergyL1" : 185.0,
"totalharmonicdistorsionVL3" : 3.3000000000000003,
"totalharmonicdistorsionVL2" : 3.5,
"totalharmonicdistorsionVL1" : 3.6,
"consumedactiveenergyL3" : 9144.0,
"consumedactiveenergyL2" : 21774.0,
"consumedactiveenergyL1" : 18509.0,
"totalconsumedactiveenergy" : 49445.0,
}}
What I tried to do is the following;
Grouped by time but cannot get the first and last element for each cond.
"$project": {
"yearMonthDayUTC": {
"$dateToString": {
"format": "%Y-%m-%d",
"date": "$_Timestamp"
}
},
"timewithOffset": {
"$dateToString": {
"format": "%H:%M:%S",
"date": "$_Timestamp"
}
},
"Registers.totalconsumedactiveenergy": 1.0,
"_DeviceID": 1.0
}
"$group": {
"_id": {
"Date": "$yearMonthDayUTC",
"Device": "$_DeviceID"
},
"T1": {
"$sum": {
"$cond": [
{
"$and": [
{
"$gte": [
"$timewithOffset",
"06:00:00"
]
},
{
"$lte": [
"$timewithOffset",
"17:00:00"
]
}
]
},
1.0,
0.0
]
}
},
"T2": {
"$sum": {
"$cond": [
{
"$and": [
{
"$gte": [
"$timewithOffset",
"17:00:00"
]
},
{
"$lte": [
"$timewithOffset",
"22:00:00"
]
}
]
},
1.0,
0.0
]
}
},
"firstactive": {
"$first": "$$ROOT.Registers.totalconsumedactiveenergy"
},
"lastactive": {
"$last": "$$ROOT.Registers.totalconsumedactiveenergy"
}
}
},
I am expecting to see something like this
Day1 : 12.12.2018
T1 - first document , lastdocument
T2 - first document , lastdocument
T3 - first document , lastdocument
Day2 : 13.12.2018
T1 - first document , lastdocument
T2 - first document , lastdocument
T3 - first document , lastdocument
...
c# mongodb mongodb-query
asked Dec 31 '18 at 16:48
John SartiesJohn Sarties
2319
add a comment |
1
I am trying to get the first and last document of three different time ranges within 24 hours and then group it by days.
Time Ranges in a day
Day 1
T1 : 06:00 - 17:00 - Get the first and last document
T2 : 17:00 - 22:00 - Get the first and last document
T3 : 22:00 - 06:00 - Get the first and last document
Day 2.. so on
Sample Document
{
"_id" : ObjectId("5ba6bc27260d0909e43b0874"),
"_DeviceName" : "Ground Floor",
"PointType" : "Building",
"_DeviceID" : ObjectId("5b9ae3a76080700be0f173d6"),
"_TariffID" : ObjectId("5a8893216b41bd32c0797f91"),
"_Timestamp" : ISODate("2018-09-22T22:03:18.552+0000"),
"_ModbusID" : 1,
"_LocationID" : ObjectId("5b9ae0eb6080700be0f173bf"),
"Registers" : {
"quadrant4reactiveenergyL3" : 781.0,
"quadrant4reactiveenergyL2" : 74.0,
"quadrant4reactiveenergyL1" : 144.0,
"quadrant1reactiveenergyL3" : 52.0,
"quadrant1reactiveenergyL2" : 706.0,
"quadrant1reactiveenergyL1" : 185.0,
"totalharmonicdistorsionVL3" : 3.3000000000000003,
"totalharmonicdistorsionVL2" : 3.5,
"totalharmonicdistorsionVL1" : 3.6,
"consumedactiveenergyL3" : 9144.0,
"consumedactiveenergyL2" : 21774.0,
"consumedactiveenergyL1" : 18509.0,
"totalconsumedactiveenergy" : 49445.0,
}}
What I tried to do is the following;
Grouped by time but cannot get the first and last element for each cond.
"$project": {
"yearMonthDayUTC": {
"$dateToString": {
"format": "%Y-%m-%d",
"date": "$_Timestamp"
}
},
"timewithOffset": {
"$dateToString": {
"format": "%H:%M:%S",
"date": "$_Timestamp"
}
},
"Registers.totalconsumedactiveenergy": 1.0,
"_DeviceID": 1.0
}
"$group": {
"_id": {
"Date": "$yearMonthDayUTC",
"Device": "$_DeviceID"
},
"T1": {
"$sum": {
"$cond": [
{
"$and": [
{
"$gte": [
"$timewithOffset",
"06:00:00"
]
},
{
"$lte": [
"$timewithOffset",
"17:00:00"
]
}
]
},
1.0,
0.0
]
}
},
"T2": {
"$sum": {
"$cond": [
{
"$and": [
{
"$gte": [
"$timewithOffset",
"17:00:00"
]
},
{
"$lte": [
"$timewithOffset",
"22:00:00"
]
}
]
},
1.0,
0.0
]
}
},
"firstactive": {
"$first": "$$ROOT.Registers.totalconsumedactiveenergy"
},
"lastactive": {
"$last": "$$ROOT.Registers.totalconsumedactiveenergy"
}
}
},
I am expecting to see something like this
Day1 : 12.12.2018
T1 - first document , lastdocument
T2 - first document , lastdocument
T3 - first document , lastdocument
Day2 : 13.12.2018
T1 - first document , lastdocument
T2 - first document , lastdocument
T3 - first document , lastdocument
...
c# mongodb mongodb-query
asked Dec 31 '18 at 16:48
John SartiesJohn Sarties
2319
can you post sample documents?
– Saravana
Dec 31 '18 at 17:49
Updated my question
– John Sarties
Dec 31 '18 at 17:59
the posted document doesn't have yearMonthDayUTC and timewithOffset fields
– Saravana
Dec 31 '18 at 18:01
Okay added the project stage in the query code
– John Sarties
Dec 31 '18 at 18:04
add a comment |
1
1
1
I am trying to get the first and last document of three different time ranges within 24 hours and then group it by days.
Time Ranges in a day
Day 1
T1 : 06:00 - 17:00 - Get the first and last document
T2 : 17:00 - 22:00 - Get the first and last document
T3 : 22:00 - 06:00 - Get the first and last document
Day 2.. so on
Sample Document
{
"_id" : ObjectId("5ba6bc27260d0909e43b0874"),
"_DeviceName" : "Ground Floor",
"PointType" : "Building",
"_DeviceID" : ObjectId("5b9ae3a76080700be0f173d6"),
"_TariffID" : ObjectId("5a8893216b41bd32c0797f91"),
"_Timestamp" : ISODate("2018-09-22T22:03:18.552+0000"),
"_ModbusID" : 1,
"_LocationID" : ObjectId("5b9ae0eb6080700be0f173bf"),
"Registers" : {
"quadrant4reactiveenergyL3" : 781.0,
"quadrant4reactiveenergyL2" : 74.0,
"quadrant4reactiveenergyL1" : 144.0,
"quadrant1reactiveenergyL3" : 52.0,
"quadrant1reactiveenergyL2" : 706.0,
"quadrant1reactiveenergyL1" : 185.0,
"totalharmonicdistorsionVL3" : 3.3000000000000003,
"totalharmonicdistorsionVL2" : 3.5,
"totalharmonicdistorsionVL1" : 3.6,
"consumedactiveenergyL3" : 9144.0,
"consumedactiveenergyL2" : 21774.0,
"consumedactiveenergyL1" : 18509.0,
"totalconsumedactiveenergy" : 49445.0,
}}
What I tried to do is the following;
Grouped by time but cannot get the first and last element for each cond.
"$project": {
"yearMonthDayUTC": {
"$dateToString": {
"format": "%Y-%m-%d",
"date": "$_Timestamp"
}
},
"timewithOffset": {
"$dateToString": {
"format": "%H:%M:%S",
"date": "$_Timestamp"
}
},
"Registers.totalconsumedactiveenergy": 1.0,
"_DeviceID": 1.0
}
"$group": {
"_id": {
"Date": "$yearMonthDayUTC",
"Device": "$_DeviceID"
},
"T1": {
"$sum": {
"$cond": [
{
"$and": [
{
"$gte": [
"$timewithOffset",
"06:00:00"
]
},
{
"$lte": [
"$timewithOffset",
"17:00:00"
]
}
]
},
1.0,
0.0
]
}
},
"T2": {
"$sum": {
"$cond": [
{
"$and": [
{
"$gte": [
"$timewithOffset",
"17:00:00"
]
},
{
"$lte": [
"$timewithOffset",
"22:00:00"
]
}
]
},
1.0,
0.0
]
}
},
"firstactive": {
"$first": "$$ROOT.Registers.totalconsumedactiveenergy"
},
"lastactive": {
"$last": "$$ROOT.Registers.totalconsumedactiveenergy"
}
}
},
I am expecting to see something like this
Day1 : 12.12.2018
T1 - first document , lastdocument
T2 - first document , lastdocument
T3 - first document , lastdocument
Day2 : 13.12.2018
T1 - first document , lastdocument
T2 - first document , lastdocument
T3 - first document , lastdocument
...
c# mongodb mongodb-query
asked Dec 31 '18 at 16:48
John SartiesJohn Sarties
2319
I am trying to get the first and last document of three different time ranges within 24 hours and then group it by days.
Time Ranges in a day
Day 1
T1 : 06:00 - 17:00 - Get the first and last document
T2 : 17:00 - 22:00 - Get the first and last document
T3 : 22:00 - 06:00 - Get the first and last document
Day 2.. so on
Sample Document
{
"_id" : ObjectId("5ba6bc27260d0909e43b0874"),
"_DeviceName" : "Ground Floor",
"PointType" : "Building",
"_DeviceID" : ObjectId("5b9ae3a76080700be0f173d6"),
"_TariffID" : ObjectId("5a8893216b41bd32c0797f91"),
"_Timestamp" : ISODate("2018-09-22T22:03:18.552+0000"),
"_ModbusID" : 1,
"_LocationID" : ObjectId("5b9ae0eb6080700be0f173bf"),
"Registers" : {
"quadrant4reactiveenergyL3" : 781.0,
"quadrant4reactiveenergyL2" : 74.0,
"quadrant4reactiveenergyL1" : 144.0,
"quadrant1reactiveenergyL3" : 52.0,
"quadrant1reactiveenergyL2" : 706.0,
"quadrant1reactiveenergyL1" : 185.0,
"totalharmonicdistorsionVL3" : 3.3000000000000003,
"totalharmonicdistorsionVL2" : 3.5,
"totalharmonicdistorsionVL1" : 3.6,
"consumedactiveenergyL3" : 9144.0,
"consumedactiveenergyL2" : 21774.0,
"consumedactiveenergyL1" : 18509.0,
"totalconsumedactiveenergy" : 49445.0,
}}
What I tried to do is the following;
Grouped by time but cannot get the first and last element for each cond.
"$project": {
"yearMonthDayUTC": {
"$dateToString": {
"format": "%Y-%m-%d",
"date": "$_Timestamp"
}
},
"timewithOffset": {
"$dateToString": {
"format": "%H:%M:%S",
"date": "$_Timestamp"
}
},
"Registers.totalconsumedactiveenergy": 1.0,
"_DeviceID": 1.0
}
"$group": {
"_id": {
"Date": "$yearMonthDayUTC",
"Device": "$_DeviceID"
},
"T1": {
"$sum": {
"$cond": [
{
"$and": [
{
"$gte": [
"$timewithOffset",
"06:00:00"
]
},
{
"$lte": [
"$timewithOffset",
"17:00:00"
]
}
]
},
1.0,
0.0
]
}
},
"T2": {
"$sum": {
"$cond": [
{
"$and": [
{
"$gte": [
"$timewithOffset",
"17:00:00"
]
},
{
"$lte": [
"$timewithOffset",
"22:00:00"
]
}
]
},
1.0,
0.0
]
}
},
"firstactive": {
"$first": "$$ROOT.Registers.totalconsumedactiveenergy"
},
"lastactive": {
"$last": "$$ROOT.Registers.totalconsumedactiveenergy"
}
}
},
I am expecting to see something like this
Day1 : 12.12.2018
T1 - first document , lastdocument
T2 - first document , lastdocument
T3 - first document , lastdocument
Day2 : 13.12.2018
T1 - first document , lastdocument
T2 - first document , lastdocument
T3 - first document , lastdocument
...
c# mongodb mongodb-query
c# mongodb mongodb-query
asked Dec 31 '18 at 16:48
John SartiesJohn Sarties
2319
asked Dec 31 '18 at 16:48
John SartiesJohn Sarties
2319
edited Jan 1 at 8:34
John Sarties
asked Dec 31 '18 at 16:48
John SartiesJohn Sarties
2319
asked Dec 31 '18 at 16:48
John SartiesJohn Sarties
2319
asked Dec 31 '18 at 16:48
John SartiesJohn Sarties
2319
2319
can you post sample documents?
– Saravana
Dec 31 '18 at 17:49
Updated my question
– John Sarties
Dec 31 '18 at 17:59
the posted document doesn't have yearMonthDayUTC and timewithOffset fields
– Saravana
Dec 31 '18 at 18:01
Okay added the project stage in the query code
– John Sarties
Dec 31 '18 at 18:04
add a comment |
can you post sample documents?
– Saravana
Dec 31 '18 at 17:49
Updated my question
– John Sarties
Dec 31 '18 at 17:59
the posted document doesn't have yearMonthDayUTC and timewithOffset fields
– Saravana
Dec 31 '18 at 18:01
Okay added the project stage in the query code
– John Sarties
Dec 31 '18 at 18:04
can you post sample documents?
– Saravana
Dec 31 '18 at 17:49
can you post sample documents?
– Saravana
Dec 31 '18 at 17:49
Updated my question
– John Sarties
Dec 31 '18 at 17:59
Updated my question
– John Sarties
Dec 31 '18 at 17:59
the posted document doesn't have yearMonthDayUTC and timewithOffset fields
– Saravana
Dec 31 '18 at 18:01
the posted document doesn't have yearMonthDayUTC and timewithOffset fields
– Saravana
Dec 31 '18 at 18:01
Okay added the project stage in the query code
– John Sarties
Dec 31 '18 at 18:04
Okay added the project stage in the query code
– John Sarties
Dec 31 '18 at 18:04
add a comment |
1 Answer
1
active
oldest
votes
1
Please try the below aggregation pipeline, if you want the complete $first and $last documents you need to use $$ROOT
db.t18.aggregate([
{$group: {_id : {
date : {$dateToString: {format : "%Y-%m-%d", date : "$_Timestamp"}},
T : {$switch :
{branches: [
{case: {$and: [{$gte : [{$hour : "$_Timestamp"}, 6]},{$lt : [{$hour : "$_Timestamp"}, 17]}]}, then : "T1"},
{case: {$and: [{$gte : [{$hour : "$_Timestamp"}, 17]},{$lt : [{$hour : "$_Timestamp"}, 22]}]}, then : "T2"}
],
default : "T3"
}}},
firstactive : {$first : "$Registers.totalconsumedactiveenergy"},
lastactive : {$last : "$Registers.totalconsumedactiveenergy"}
}},
{$group: {_id : {date : "$_id.date"}, data : {$push : {"k" : "$_id.T", "v" : ["$firstactive","$lastactive"]}}}},
{$project: {_id : 0, date : "$_id.date", "data" : {$arrayToObject : "$data"}}}
]).pretty()
answered Dec 31 '18 at 18:50
SaravanaSaravana
8,74421835
Thank you for this, really. I guess one thing is confusing the aggregation is that the documents have different devices with similar meter data that also needs to be grouped by "_DeviceID" so each device would have their own date and time ranges. Would that be possible to add that too? "_id" : { "date" : "2018-12-31", "T" : "T1" }, "firstactive" : 499796233.0, "lastactive" : 22693981.0 that's what I get, you can see lastactive is lower than the firstactive, it is because probably reading it from another device's data
– John Sarties
Dec 31 '18 at 19:11
1
you can add_DeviceIDin$group_id field and also you can pre$sortby_Timestamp
– Saravana
Dec 31 '18 at 19:39
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53989650%2fmultiple-timerange-group-in-days-of-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Please try the below aggregation pipeline, if you want the complete $first and $last documents you need to use $$ROOT
db.t18.aggregate([
{$group: {_id : {
date : {$dateToString: {format : "%Y-%m-%d", date : "$_Timestamp"}},
T : {$switch :
{branches: [
{case: {$and: [{$gte : [{$hour : "$_Timestamp"}, 6]},{$lt : [{$hour : "$_Timestamp"}, 17]}]}, then : "T1"},
{case: {$and: [{$gte : [{$hour : "$_Timestamp"}, 17]},{$lt : [{$hour : "$_Timestamp"}, 22]}]}, then : "T2"}
],
default : "T3"
}}},
firstactive : {$first : "$Registers.totalconsumedactiveenergy"},
lastactive : {$last : "$Registers.totalconsumedactiveenergy"}
}},
{$group: {_id : {date : "$_id.date"}, data : {$push : {"k" : "$_id.T", "v" : ["$firstactive","$lastactive"]}}}},
{$project: {_id : 0, date : "$_id.date", "data" : {$arrayToObject : "$data"}}}
]).pretty()
answered Dec 31 '18 at 18:50
SaravanaSaravana
8,74421835
Thank you for this, really. I guess one thing is confusing the aggregation is that the documents have different devices with similar meter data that also needs to be grouped by "_DeviceID" so each device would have their own date and time ranges. Would that be possible to add that too? "_id" : { "date" : "2018-12-31", "T" : "T1" }, "firstactive" : 499796233.0, "lastactive" : 22693981.0 that's what I get, you can see lastactive is lower than the firstactive, it is because probably reading it from another device's data
– John Sarties
Dec 31 '18 at 19:11
1
you can add_DeviceIDin$group_id field and also you can pre$sortby_Timestamp
– Saravana
Dec 31 '18 at 19:39
add a comment |
1
Please try the below aggregation pipeline, if you want the complete $first and $last documents you need to use $$ROOT
db.t18.aggregate([
{$group: {_id : {
date : {$dateToString: {format : "%Y-%m-%d", date : "$_Timestamp"}},
T : {$switch :
{branches: [
{case: {$and: [{$gte : [{$hour : "$_Timestamp"}, 6]},{$lt : [{$hour : "$_Timestamp"}, 17]}]}, then : "T1"},
{case: {$and: [{$gte : [{$hour : "$_Timestamp"}, 17]},{$lt : [{$hour : "$_Timestamp"}, 22]}]}, then : "T2"}
],
default : "T3"
}}},
firstactive : {$first : "$Registers.totalconsumedactiveenergy"},
lastactive : {$last : "$Registers.totalconsumedactiveenergy"}
}},
{$group: {_id : {date : "$_id.date"}, data : {$push : {"k" : "$_id.T", "v" : ["$firstactive","$lastactive"]}}}},
{$project: {_id : 0, date : "$_id.date", "data" : {$arrayToObject : "$data"}}}
]).pretty()
answered Dec 31 '18 at 18:50
SaravanaSaravana
8,74421835
Thank you for this, really. I guess one thing is confusing the aggregation is that the documents have different devices with similar meter data that also needs to be grouped by "_DeviceID" so each device would have their own date and time ranges. Would that be possible to add that too? "_id" : { "date" : "2018-12-31", "T" : "T1" }, "firstactive" : 499796233.0, "lastactive" : 22693981.0 that's what I get, you can see lastactive is lower than the firstactive, it is because probably reading it from another device's data
– John Sarties
Dec 31 '18 at 19:11
1
you can add_DeviceIDin$group_id field and also you can pre$sortby_Timestamp
– Saravana
Dec 31 '18 at 19:39
add a comment |
1
1
1
Please try the below aggregation pipeline, if you want the complete $first and $last documents you need to use $$ROOT
db.t18.aggregate([
{$group: {_id : {
date : {$dateToString: {format : "%Y-%m-%d", date : "$_Timestamp"}},
T : {$switch :
{branches: [
{case: {$and: [{$gte : [{$hour : "$_Timestamp"}, 6]},{$lt : [{$hour : "$_Timestamp"}, 17]}]}, then : "T1"},
{case: {$and: [{$gte : [{$hour : "$_Timestamp"}, 17]},{$lt : [{$hour : "$_Timestamp"}, 22]}]}, then : "T2"}
],
default : "T3"
}}},
firstactive : {$first : "$Registers.totalconsumedactiveenergy"},
lastactive : {$last : "$Registers.totalconsumedactiveenergy"}
}},
{$group: {_id : {date : "$_id.date"}, data : {$push : {"k" : "$_id.T", "v" : ["$firstactive","$lastactive"]}}}},
{$project: {_id : 0, date : "$_id.date", "data" : {$arrayToObject : "$data"}}}
]).pretty()
answered Dec 31 '18 at 18:50
SaravanaSaravana
8,74421835
Please try the below aggregation pipeline, if you want the complete $first and $last documents you need to use $$ROOT
db.t18.aggregate([
{$group: {_id : {
date : {$dateToString: {format : "%Y-%m-%d", date : "$_Timestamp"}},
T : {$switch :
{branches: [
{case: {$and: [{$gte : [{$hour : "$_Timestamp"}, 6]},{$lt : [{$hour : "$_Timestamp"}, 17]}]}, then : "T1"},
{case: {$and: [{$gte : [{$hour : "$_Timestamp"}, 17]},{$lt : [{$hour : "$_Timestamp"}, 22]}]}, then : "T2"}
],
default : "T3"
}}},
firstactive : {$first : "$Registers.totalconsumedactiveenergy"},
lastactive : {$last : "$Registers.totalconsumedactiveenergy"}
}},
{$group: {_id : {date : "$_id.date"}, data : {$push : {"k" : "$_id.T", "v" : ["$firstactive","$lastactive"]}}}},
{$project: {_id : 0, date : "$_id.date", "data" : {$arrayToObject : "$data"}}}
]).pretty()
answered Dec 31 '18 at 18:50
SaravanaSaravana
8,74421835
answered Dec 31 '18 at 18:50
SaravanaSaravana
8,74421835
answered Dec 31 '18 at 18:50
SaravanaSaravana
8,74421835
answered Dec 31 '18 at 18:50
SaravanaSaravana
8,74421835
8,74421835
Thank you for this, really. I guess one thing is confusing the aggregation is that the documents have different devices with similar meter data that also needs to be grouped by "_DeviceID" so each device would have their own date and time ranges. Would that be possible to add that too? "_id" : { "date" : "2018-12-31", "T" : "T1" }, "firstactive" : 499796233.0, "lastactive" : 22693981.0 that's what I get, you can see lastactive is lower than the firstactive, it is because probably reading it from another device's data
– John Sarties
Dec 31 '18 at 19:11
1
you can add_DeviceIDin$group_id field and also you can pre$sortby_Timestamp
– Saravana
Dec 31 '18 at 19:39
add a comment |
Thank you for this, really. I guess one thing is confusing the aggregation is that the documents have different devices with similar meter data that also needs to be grouped by "_DeviceID" so each device would have their own date and time ranges. Would that be possible to add that too? "_id" : { "date" : "2018-12-31", "T" : "T1" }, "firstactive" : 499796233.0, "lastactive" : 22693981.0 that's what I get, you can see lastactive is lower than the firstactive, it is because probably reading it from another device's data
– John Sarties
Dec 31 '18 at 19:11
1
you can add_DeviceIDin$group_id field and also you can pre$sortby_Timestamp
– Saravana
Dec 31 '18 at 19:39
Thank you for this, really. I guess one thing is confusing the aggregation is that the documents have different devices with similar meter data that also needs to be grouped by "_DeviceID" so each device would have their own date and time ranges. Would that be possible to add that too? "_id" : { "date" : "2018-12-31", "T" : "T1" }, "firstactive" : 499796233.0, "lastactive" : 22693981.0 that's what I get, you can see lastactive is lower than the firstactive, it is because probably reading it from another device's data
– John Sarties
Dec 31 '18 at 19:11
Thank you for this, really. I guess one thing is confusing the aggregation is that the documents have different devices with similar meter data that also needs to be grouped by "_DeviceID" so each device would have their own date and time ranges. Would that be possible to add that too? "_id" : { "date" : "2018-12-31", "T" : "T1" }, "firstactive" : 499796233.0, "lastactive" : 22693981.0 that's what I get, you can see lastactive is lower than the firstactive, it is because probably reading it from another device's data
– John Sarties
Dec 31 '18 at 19:11
1
1
you can add
_DeviceID in $group _id field and also you can pre $sort by _Timestamp– Saravana
Dec 31 '18 at 19:39
you can add
_DeviceID in $group _id field and also you can pre $sort by _Timestamp– Saravana
Dec 31 '18 at 19:39
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53989650%2fmultiple-timerange-group-in-days-of-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
can you post sample documents?
– Saravana
Dec 31 '18 at 17:49
Updated my question
– John Sarties
Dec 31 '18 at 17:59
the posted document doesn't have yearMonthDayUTC and timewithOffset fields
– Saravana
Dec 31 '18 at 18:01
Okay added the project stage in the query code
– John Sarties
Dec 31 '18 at 18:04