Mixing
Mongo query aggregate with a conditional group and another field
1
I followed this post by @Blakes-Seven
Mongodb aggregate $group for multiple date ranges
But I need to add an additional group by field "$User.Account" and I keep getting an error. When I take that out it works fine.
What I'm trying to do and I'm pretty sure the below won't do it is find the top N users within each of the date ranges...
{
"message" : "the group aggregate field 'User' must be defined as an expression inside an object",
"ok" : 0,
"code" : 15951,
"name" : "MongoError"
}
Any help would be greatly appreciated. I'm missing something...
> // work out dates somehow var today = new Date(),
> oneDay = ( 1000 * 60 * 60 * 24 ),
> ninetyDays = new Date( today.valueOf() - ( 90 * oneDay ) ),
> sixtyDays = new Date( today.valueOf() - ( 60 * oneDay ) ),
> thirtyDays = new Date( today.valueOf() - ( 30 * oneDay ) );
>
> db.logs.aggregate([
> { "$match": {
> "DateTime": { "$gte": ninetyDays },
> "Orgname": /Inc/
> }},
> { "$group": {
> "_id": {
> "$cond": [
> { "$lt": [ "$DateTime", sixtyDays ] },
> "61-90",
> { "$cond": [
> { "$lt": [ "$DateTime", thirtyDays ] },
> "31-60",
> "01-30"
> ]}
> ]
> },
> "User": "$User.Account",
> "count": { "$sum": 1 },
> }},
> { $sort: {"count": -1}
> },
> { $limit: 25} ])
Sample output
01-30 usera 45
01-30 userc 34
01-30 userf 28
01-30 userq 13
… 20 more rows...
01-30 usery 4
31-60 userb 55
… 23 more rows
31-60 userk 3
61-90 userm 78
61-90 userf 45
... 22 more rows...
61-90 usery 22
mongodb mongodb-query aggregation-framework
asked Jan 2 at 21:26
ChrisChris
206
add a comment |
1
I followed this post by @Blakes-Seven
Mongodb aggregate $group for multiple date ranges
But I need to add an additional group by field "$User.Account" and I keep getting an error. When I take that out it works fine.
What I'm trying to do and I'm pretty sure the below won't do it is find the top N users within each of the date ranges...
{
"message" : "the group aggregate field 'User' must be defined as an expression inside an object",
"ok" : 0,
"code" : 15951,
"name" : "MongoError"
}
Any help would be greatly appreciated. I'm missing something...
> // work out dates somehow var today = new Date(),
> oneDay = ( 1000 * 60 * 60 * 24 ),
> ninetyDays = new Date( today.valueOf() - ( 90 * oneDay ) ),
> sixtyDays = new Date( today.valueOf() - ( 60 * oneDay ) ),
> thirtyDays = new Date( today.valueOf() - ( 30 * oneDay ) );
>
> db.logs.aggregate([
> { "$match": {
> "DateTime": { "$gte": ninetyDays },
> "Orgname": /Inc/
> }},
> { "$group": {
> "_id": {
> "$cond": [
> { "$lt": [ "$DateTime", sixtyDays ] },
> "61-90",
> { "$cond": [
> { "$lt": [ "$DateTime", thirtyDays ] },
> "31-60",
> "01-30"
> ]}
> ]
> },
> "User": "$User.Account",
> "count": { "$sum": 1 },
> }},
> { $sort: {"count": -1}
> },
> { $limit: 25} ])
Sample output
01-30 usera 45
01-30 userc 34
01-30 userf 28
01-30 userq 13
… 20 more rows...
01-30 usery 4
31-60 userb 55
… 23 more rows
31-60 userk 3
61-90 userm 78
61-90 userf 45
... 22 more rows...
61-90 usery 22
mongodb mongodb-query aggregation-framework
asked Jan 2 at 21:26
ChrisChris
206
add a comment |
1
1
1
I followed this post by @Blakes-Seven
Mongodb aggregate $group for multiple date ranges
But I need to add an additional group by field "$User.Account" and I keep getting an error. When I take that out it works fine.
What I'm trying to do and I'm pretty sure the below won't do it is find the top N users within each of the date ranges...
{
"message" : "the group aggregate field 'User' must be defined as an expression inside an object",
"ok" : 0,
"code" : 15951,
"name" : "MongoError"
}
Any help would be greatly appreciated. I'm missing something...
> // work out dates somehow var today = new Date(),
> oneDay = ( 1000 * 60 * 60 * 24 ),
> ninetyDays = new Date( today.valueOf() - ( 90 * oneDay ) ),
> sixtyDays = new Date( today.valueOf() - ( 60 * oneDay ) ),
> thirtyDays = new Date( today.valueOf() - ( 30 * oneDay ) );
>
> db.logs.aggregate([
> { "$match": {
> "DateTime": { "$gte": ninetyDays },
> "Orgname": /Inc/
> }},
> { "$group": {
> "_id": {
> "$cond": [
> { "$lt": [ "$DateTime", sixtyDays ] },
> "61-90",
> { "$cond": [
> { "$lt": [ "$DateTime", thirtyDays ] },
> "31-60",
> "01-30"
> ]}
> ]
> },
> "User": "$User.Account",
> "count": { "$sum": 1 },
> }},
> { $sort: {"count": -1}
> },
> { $limit: 25} ])
Sample output
01-30 usera 45
01-30 userc 34
01-30 userf 28
01-30 userq 13
… 20 more rows...
01-30 usery 4
31-60 userb 55
… 23 more rows
31-60 userk 3
61-90 userm 78
61-90 userf 45
... 22 more rows...
61-90 usery 22
mongodb mongodb-query aggregation-framework
asked Jan 2 at 21:26
ChrisChris
206
I followed this post by @Blakes-Seven
Mongodb aggregate $group for multiple date ranges
But I need to add an additional group by field "$User.Account" and I keep getting an error. When I take that out it works fine.
What I'm trying to do and I'm pretty sure the below won't do it is find the top N users within each of the date ranges...
{
"message" : "the group aggregate field 'User' must be defined as an expression inside an object",
"ok" : 0,
"code" : 15951,
"name" : "MongoError"
}
Any help would be greatly appreciated. I'm missing something...
> // work out dates somehow var today = new Date(),
> oneDay = ( 1000 * 60 * 60 * 24 ),
> ninetyDays = new Date( today.valueOf() - ( 90 * oneDay ) ),
> sixtyDays = new Date( today.valueOf() - ( 60 * oneDay ) ),
> thirtyDays = new Date( today.valueOf() - ( 30 * oneDay ) );
>
> db.logs.aggregate([
> { "$match": {
> "DateTime": { "$gte": ninetyDays },
> "Orgname": /Inc/
> }},
> { "$group": {
> "_id": {
> "$cond": [
> { "$lt": [ "$DateTime", sixtyDays ] },
> "61-90",
> { "$cond": [
> { "$lt": [ "$DateTime", thirtyDays ] },
> "31-60",
> "01-30"
> ]}
> ]
> },
> "User": "$User.Account",
> "count": { "$sum": 1 },
> }},
> { $sort: {"count": -1}
> },
> { $limit: 25} ])
Sample output
01-30 usera 45
01-30 userc 34
01-30 userf 28
01-30 userq 13
… 20 more rows...
01-30 usery 4
31-60 userb 55
… 23 more rows
31-60 userk 3
61-90 userm 78
61-90 userf 45
... 22 more rows...
61-90 usery 22
mongodb mongodb-query aggregation-framework
mongodb mongodb-query aggregation-framework
asked Jan 2 at 21:26
ChrisChris
206
asked Jan 2 at 21:26
ChrisChris
206
edited Jan 3 at 12:55
Chris
asked Jan 2 at 21:26
ChrisChris
206
asked Jan 2 at 21:26
ChrisChris
206
asked Jan 2 at 21:26
ChrisChris
206
206
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
You can follow below syntax to add another field in $group expression
db.logs.aggregate([
{ "$match": {
"DateTime": { "$gte": ninetyDays },
"Orgname": /Inc/
}},
{ "$group": {
"_id": {
"User": "$User.Account",
"date": {
"$cond": [
{ "$lt": [ "$DateTime", sixtyDays ] },
"61-90",
{ "$cond": [
{ "$lt": [ "$DateTime", thirtyDays ] },
"31-60",
"01-30"
]}
]
}
},
"count": { "$sum": 1 },
}},
{ "$sort": { "count": -1 }},
{ "$limit": 25}
])
answered Jan 3 at 8:15
Anthony WinzletAnthony Winzlet
18.1k42345
Thanks for getting back to me! I tried that and although I don't get the error any longer, the end result isn't what I wanted. I want to get the top 25 results within each day's bucket. So I should end up with 75 rows and not just 25. I'm not sure I can do this in one run.
– Chris
Jan 3 at 12:46
Ok then please explain what do you need. And it would be better if you show some sample collection and the output
– Anthony Winzlet
Jan 3 at 12:49
I didn't see the subtle change you made by making the conditional section into a group! That was a game changer. So how do I sort and limit within one group at a time? Thanks so much for helping!
– Chris
Jan 3 at 13:12
So above query is now working? So how do I sort and limit within one group at a time? Didn't get this
– Anthony Winzlet
Jan 3 at 13:18
It's working better but I can't seem to get 75 rows, 25 in each date category. I only get 25 rows total the top 25 regardless of date category.
– Chris
Jan 3 at 13:25
|
show 3 more comments
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%2f54013381%2fmongo-query-aggregate-with-a-conditional-group-and-another-field%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
You can follow below syntax to add another field in $group expression
db.logs.aggregate([
{ "$match": {
"DateTime": { "$gte": ninetyDays },
"Orgname": /Inc/
}},
{ "$group": {
"_id": {
"User": "$User.Account",
"date": {
"$cond": [
{ "$lt": [ "$DateTime", sixtyDays ] },
"61-90",
{ "$cond": [
{ "$lt": [ "$DateTime", thirtyDays ] },
"31-60",
"01-30"
]}
]
}
},
"count": { "$sum": 1 },
}},
{ "$sort": { "count": -1 }},
{ "$limit": 25}
])
answered Jan 3 at 8:15
Anthony WinzletAnthony Winzlet
18.1k42345
Thanks for getting back to me! I tried that and although I don't get the error any longer, the end result isn't what I wanted. I want to get the top 25 results within each day's bucket. So I should end up with 75 rows and not just 25. I'm not sure I can do this in one run.
– Chris
Jan 3 at 12:46
Ok then please explain what do you need. And it would be better if you show some sample collection and the output
– Anthony Winzlet
Jan 3 at 12:49
I didn't see the subtle change you made by making the conditional section into a group! That was a game changer. So how do I sort and limit within one group at a time? Thanks so much for helping!
– Chris
Jan 3 at 13:12
So above query is now working? So how do I sort and limit within one group at a time? Didn't get this
– Anthony Winzlet
Jan 3 at 13:18
It's working better but I can't seem to get 75 rows, 25 in each date category. I only get 25 rows total the top 25 regardless of date category.
– Chris
Jan 3 at 13:25
|
show 3 more comments
1
You can follow below syntax to add another field in $group expression
db.logs.aggregate([
{ "$match": {
"DateTime": { "$gte": ninetyDays },
"Orgname": /Inc/
}},
{ "$group": {
"_id": {
"User": "$User.Account",
"date": {
"$cond": [
{ "$lt": [ "$DateTime", sixtyDays ] },
"61-90",
{ "$cond": [
{ "$lt": [ "$DateTime", thirtyDays ] },
"31-60",
"01-30"
]}
]
}
},
"count": { "$sum": 1 },
}},
{ "$sort": { "count": -1 }},
{ "$limit": 25}
])
answered Jan 3 at 8:15
Anthony WinzletAnthony Winzlet
18.1k42345
Thanks for getting back to me! I tried that and although I don't get the error any longer, the end result isn't what I wanted. I want to get the top 25 results within each day's bucket. So I should end up with 75 rows and not just 25. I'm not sure I can do this in one run.
– Chris
Jan 3 at 12:46
Ok then please explain what do you need. And it would be better if you show some sample collection and the output
– Anthony Winzlet
Jan 3 at 12:49
I didn't see the subtle change you made by making the conditional section into a group! That was a game changer. So how do I sort and limit within one group at a time? Thanks so much for helping!
– Chris
Jan 3 at 13:12
So above query is now working? So how do I sort and limit within one group at a time? Didn't get this
– Anthony Winzlet
Jan 3 at 13:18
It's working better but I can't seem to get 75 rows, 25 in each date category. I only get 25 rows total the top 25 regardless of date category.
– Chris
Jan 3 at 13:25
|
show 3 more comments
1
1
1
You can follow below syntax to add another field in $group expression
db.logs.aggregate([
{ "$match": {
"DateTime": { "$gte": ninetyDays },
"Orgname": /Inc/
}},
{ "$group": {
"_id": {
"User": "$User.Account",
"date": {
"$cond": [
{ "$lt": [ "$DateTime", sixtyDays ] },
"61-90",
{ "$cond": [
{ "$lt": [ "$DateTime", thirtyDays ] },
"31-60",
"01-30"
]}
]
}
},
"count": { "$sum": 1 },
}},
{ "$sort": { "count": -1 }},
{ "$limit": 25}
])
answered Jan 3 at 8:15
Anthony WinzletAnthony Winzlet
18.1k42345
You can follow below syntax to add another field in $group expression
db.logs.aggregate([
{ "$match": {
"DateTime": { "$gte": ninetyDays },
"Orgname": /Inc/
}},
{ "$group": {
"_id": {
"User": "$User.Account",
"date": {
"$cond": [
{ "$lt": [ "$DateTime", sixtyDays ] },
"61-90",
{ "$cond": [
{ "$lt": [ "$DateTime", thirtyDays ] },
"31-60",
"01-30"
]}
]
}
},
"count": { "$sum": 1 },
}},
{ "$sort": { "count": -1 }},
{ "$limit": 25}
])
answered Jan 3 at 8:15
Anthony WinzletAnthony Winzlet
18.1k42345
answered Jan 3 at 8:15
Anthony WinzletAnthony Winzlet
18.1k42345
answered Jan 3 at 8:15
Anthony WinzletAnthony Winzlet
18.1k42345
answered Jan 3 at 8:15
Anthony WinzletAnthony Winzlet
18.1k42345
18.1k42345
Thanks for getting back to me! I tried that and although I don't get the error any longer, the end result isn't what I wanted. I want to get the top 25 results within each day's bucket. So I should end up with 75 rows and not just 25. I'm not sure I can do this in one run.
– Chris
Jan 3 at 12:46
Ok then please explain what do you need. And it would be better if you show some sample collection and the output
– Anthony Winzlet
Jan 3 at 12:49
I didn't see the subtle change you made by making the conditional section into a group! That was a game changer. So how do I sort and limit within one group at a time? Thanks so much for helping!
– Chris
Jan 3 at 13:12
So above query is now working? So how do I sort and limit within one group at a time? Didn't get this
– Anthony Winzlet
Jan 3 at 13:18
It's working better but I can't seem to get 75 rows, 25 in each date category. I only get 25 rows total the top 25 regardless of date category.
– Chris
Jan 3 at 13:25
|
show 3 more comments
Thanks for getting back to me! I tried that and although I don't get the error any longer, the end result isn't what I wanted. I want to get the top 25 results within each day's bucket. So I should end up with 75 rows and not just 25. I'm not sure I can do this in one run.
– Chris
Jan 3 at 12:46
Ok then please explain what do you need. And it would be better if you show some sample collection and the output
– Anthony Winzlet
Jan 3 at 12:49
I didn't see the subtle change you made by making the conditional section into a group! That was a game changer. So how do I sort and limit within one group at a time? Thanks so much for helping!
– Chris
Jan 3 at 13:12
So above query is now working? So how do I sort and limit within one group at a time? Didn't get this
– Anthony Winzlet
Jan 3 at 13:18
It's working better but I can't seem to get 75 rows, 25 in each date category. I only get 25 rows total the top 25 regardless of date category.
– Chris
Jan 3 at 13:25
Thanks for getting back to me! I tried that and although I don't get the error any longer, the end result isn't what I wanted. I want to get the top 25 results within each day's bucket. So I should end up with 75 rows and not just 25. I'm not sure I can do this in one run.
– Chris
Jan 3 at 12:46
Thanks for getting back to me! I tried that and although I don't get the error any longer, the end result isn't what I wanted. I want to get the top 25 results within each day's bucket. So I should end up with 75 rows and not just 25. I'm not sure I can do this in one run.
– Chris
Jan 3 at 12:46
Ok then please explain what do you need. And it would be better if you show some sample collection and the output
– Anthony Winzlet
Jan 3 at 12:49
Ok then please explain what do you need. And it would be better if you show some sample collection and the output
– Anthony Winzlet
Jan 3 at 12:49
I didn't see the subtle change you made by making the conditional section into a group! That was a game changer. So how do I sort and limit within one group at a time? Thanks so much for helping!
– Chris
Jan 3 at 13:12
I didn't see the subtle change you made by making the conditional section into a group! That was a game changer. So how do I sort and limit within one group at a time? Thanks so much for helping!
– Chris
Jan 3 at 13:12
So above query is now working? So how do I sort and limit within one group at a time? Didn't get this
– Anthony Winzlet
Jan 3 at 13:18
So above query is now working? So how do I sort and limit within one group at a time? Didn't get this
– Anthony Winzlet
Jan 3 at 13:18
It's working better but I can't seem to get 75 rows, 25 in each date category. I only get 25 rows total the top 25 regardless of date category.
– Chris
Jan 3 at 13:25
It's working better but I can't seem to get 75 rows, 25 in each date category. I only get 25 rows total the top 25 regardless of date category.
– Chris
Jan 3 at 13:25
|
show 3 more comments
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%2f54013381%2fmongo-query-aggregate-with-a-conditional-group-and-another-field%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
