MongoDB group by array inner-elements
up vote
32
down vote
favorite
I've got a list of articles, and each of them has an array property which lists various individuals mentioned in them:
_id: {
$oid: "52b632a9e4f2ba13c82ccd23"
},
providerName: "The Guardian",
url: "http://feeds.theguardian.com/c/34708/f/663860/s/3516cebc/sc/38/l/0L0Stheguardian0N0Cmusic0C20A130Cdec0C220Cwaterboys0Efishermans0Eblues0Etour0Ehammersmith/story01.htm",
subject: "The Waterboys – review",
class_artist: [
"paul mccartney"
]
I've been trying (unsuccessfully) to get a list of all the individual artists (class_artist), based on the number of articles they've been tagged in within the past 7 days.
I've gotten as far as:
var date = new Date();
date.setDate(date.getDate() - 7);
db.articles.group({
key: { class_artist: 1 },
cond: { class_date: { $gt: date } },
reduce: function ( curr, result ) { result.cnt++; },
initial: { cnt : 0 }
}).sort({cnt: -1});
But unfortunately, it doesn't count them based on the individual array values, but by array compositions (that is, lists of artists).
I tried using the $unwind function, but have not been able to make it work.
mongodb mongodb-query aggregation-framework
edited Jun 14 at 16:21
mbuechmann
2,75221124
asked Feb 2 '14 at 9:33
Gil Adirim
79011024
add a comment |
up vote
32
down vote
favorite
I've got a list of articles, and each of them has an array property which lists various individuals mentioned in them:
_id: {
$oid: "52b632a9e4f2ba13c82ccd23"
},
providerName: "The Guardian",
url: "http://feeds.theguardian.com/c/34708/f/663860/s/3516cebc/sc/38/l/0L0Stheguardian0N0Cmusic0C20A130Cdec0C220Cwaterboys0Efishermans0Eblues0Etour0Ehammersmith/story01.htm",
subject: "The Waterboys – review",
class_artist: [
"paul mccartney"
]
I've been trying (unsuccessfully) to get a list of all the individual artists (class_artist), based on the number of articles they've been tagged in within the past 7 days.
I've gotten as far as:
var date = new Date();
date.setDate(date.getDate() - 7);
db.articles.group({
key: { class_artist: 1 },
cond: { class_date: { $gt: date } },
reduce: function ( curr, result ) { result.cnt++; },
initial: { cnt : 0 }
}).sort({cnt: -1});
But unfortunately, it doesn't count them based on the individual array values, but by array compositions (that is, lists of artists).
I tried using the $unwind function, but have not been able to make it work.
mongodb mongodb-query aggregation-framework
edited Jun 14 at 16:21
mbuechmann
2,75221124
asked Feb 2 '14 at 9:33
Gil Adirim
79011024
add a comment |
up vote
32
down vote
favorite
up vote
32
down vote
favorite
I've got a list of articles, and each of them has an array property which lists various individuals mentioned in them:
_id: {
$oid: "52b632a9e4f2ba13c82ccd23"
},
providerName: "The Guardian",
url: "http://feeds.theguardian.com/c/34708/f/663860/s/3516cebc/sc/38/l/0L0Stheguardian0N0Cmusic0C20A130Cdec0C220Cwaterboys0Efishermans0Eblues0Etour0Ehammersmith/story01.htm",
subject: "The Waterboys – review",
class_artist: [
"paul mccartney"
]
I've been trying (unsuccessfully) to get a list of all the individual artists (class_artist), based on the number of articles they've been tagged in within the past 7 days.
I've gotten as far as:
var date = new Date();
date.setDate(date.getDate() - 7);
db.articles.group({
key: { class_artist: 1 },
cond: { class_date: { $gt: date } },
reduce: function ( curr, result ) { result.cnt++; },
initial: { cnt : 0 }
}).sort({cnt: -1});
But unfortunately, it doesn't count them based on the individual array values, but by array compositions (that is, lists of artists).
I tried using the $unwind function, but have not been able to make it work.
mongodb mongodb-query aggregation-framework
edited Jun 14 at 16:21
mbuechmann
2,75221124
asked Feb 2 '14 at 9:33
Gil Adirim
79011024
I've got a list of articles, and each of them has an array property which lists various individuals mentioned in them:
_id: {
$oid: "52b632a9e4f2ba13c82ccd23"
},
providerName: "The Guardian",
url: "http://feeds.theguardian.com/c/34708/f/663860/s/3516cebc/sc/38/l/0L0Stheguardian0N0Cmusic0C20A130Cdec0C220Cwaterboys0Efishermans0Eblues0Etour0Ehammersmith/story01.htm",
subject: "The Waterboys – review",
class_artist: [
"paul mccartney"
]
I've been trying (unsuccessfully) to get a list of all the individual artists (class_artist), based on the number of articles they've been tagged in within the past 7 days.
I've gotten as far as:
var date = new Date();
date.setDate(date.getDate() - 7);
db.articles.group({
key: { class_artist: 1 },
cond: { class_date: { $gt: date } },
reduce: function ( curr, result ) { result.cnt++; },
initial: { cnt : 0 }
}).sort({cnt: -1});
But unfortunately, it doesn't count them based on the individual array values, but by array compositions (that is, lists of artists).
I tried using the $unwind function, but have not been able to make it work.
mongodb mongodb-query aggregation-framework
mongodb mongodb-query aggregation-framework
edited Jun 14 at 16:21
mbuechmann
2,75221124
asked Feb 2 '14 at 9:33
Gil Adirim
79011024
edited Jun 14 at 16:21
mbuechmann
2,75221124
asked Feb 2 '14 at 9:33
Gil Adirim
79011024
edited Jun 14 at 16:21
mbuechmann
2,75221124
edited Jun 14 at 16:21
mbuechmann
2,75221124
edited Jun 14 at 16:21
mbuechmann
2,75221124
2,75221124
asked Feb 2 '14 at 9:33
Gil Adirim
79011024
asked Feb 2 '14 at 9:33
Gil Adirim
79011024
asked Feb 2 '14 at 9:33
Gil Adirim
79011024
79011024
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
100
down vote
accepted
What framework are you using? This is not MongoDB shell and looks like some weird wrapper around MapReduce. In that case $unwind would not be available, and you need it for user in the aggregation framework. Here's what you want in the mongo shell:
db.articles.aggregate([
{$match: { class_date: { $gte: date } } },
{$project: { _id: 0, class_artist: 1 } },
{$unwind: "$class_artist" },
{$group: { _id: "$class_artist", tags: { $sum: 1 } }},
{$project: { _id: 0,class_artist: "$_id", tags: 1 } },
{$sort: { tags: -1 } }
])
So efficiently:
Filter by date because you already set a var for the last 7 days
Project only the field(s) we need { We need only one! }
Unwind the array so we now have a record for every array element in every document
Group on the Artist from the expanded documents- Project into a document format you can use as group messed around with _id
Sort the results in reverse order to see the top tagged first
And the great thing about aggregation is you can gradually build up those stages to see what is going on.
Shake and bake into your own driver implmentation or ODM framework as required.
answered Feb 2 '14 at 10:11
Neil Lunn
96.6k22170180
Thanks for the rapid response! Works like a charm.
– Gil Adirim
Feb 2 '14 at 10:22
3
FYI, the "weird wrapper" format is thegroup()command which is implemented in JavaScript and predates the Aggregation Framework. See also: MongoDB aggregation comparison: group(), $group and MapReduce.
– Stennie
Feb 2 '14 at 11:34
For some reason the aggregate method always seemed confusing but answer really gave me that 'lightbulb' moment...great job =)
– afreeland
Aug 25 '15 at 20:22
thank you so much for explaining and referencing every step! Very easy to adapt to my specific situation!
– user3616725
Apr 27 '17 at 13:15
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
});
}
});
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%2f21509045%2fmongodb-group-by-array-inner-elements%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
up vote
100
down vote
accepted
What framework are you using? This is not MongoDB shell and looks like some weird wrapper around MapReduce. In that case $unwind would not be available, and you need it for user in the aggregation framework. Here's what you want in the mongo shell:
db.articles.aggregate([
{$match: { class_date: { $gte: date } } },
{$project: { _id: 0, class_artist: 1 } },
{$unwind: "$class_artist" },
{$group: { _id: "$class_artist", tags: { $sum: 1 } }},
{$project: { _id: 0,class_artist: "$_id", tags: 1 } },
{$sort: { tags: -1 } }
])
So efficiently:
Filter by date because you already set a var for the last 7 days
Project only the field(s) we need { We need only one! }
Unwind the array so we now have a record for every array element in every document
Group on the Artist from the expanded documents- Project into a document format you can use as group messed around with _id
Sort the results in reverse order to see the top tagged first
And the great thing about aggregation is you can gradually build up those stages to see what is going on.
Shake and bake into your own driver implmentation or ODM framework as required.
answered Feb 2 '14 at 10:11
Neil Lunn
96.6k22170180
Thanks for the rapid response! Works like a charm.
– Gil Adirim
Feb 2 '14 at 10:22
3
FYI, the "weird wrapper" format is thegroup()command which is implemented in JavaScript and predates the Aggregation Framework. See also: MongoDB aggregation comparison: group(), $group and MapReduce.
– Stennie
Feb 2 '14 at 11:34
For some reason the aggregate method always seemed confusing but answer really gave me that 'lightbulb' moment...great job =)
– afreeland
Aug 25 '15 at 20:22
thank you so much for explaining and referencing every step! Very easy to adapt to my specific situation!
– user3616725
Apr 27 '17 at 13:15
add a comment |
up vote
100
down vote
accepted
What framework are you using? This is not MongoDB shell and looks like some weird wrapper around MapReduce. In that case $unwind would not be available, and you need it for user in the aggregation framework. Here's what you want in the mongo shell:
db.articles.aggregate([
{$match: { class_date: { $gte: date } } },
{$project: { _id: 0, class_artist: 1 } },
{$unwind: "$class_artist" },
{$group: { _id: "$class_artist", tags: { $sum: 1 } }},
{$project: { _id: 0,class_artist: "$_id", tags: 1 } },
{$sort: { tags: -1 } }
])
So efficiently:
Filter by date because you already set a var for the last 7 days
Project only the field(s) we need { We need only one! }
Unwind the array so we now have a record for every array element in every document
Group on the Artist from the expanded documents- Project into a document format you can use as group messed around with _id
Sort the results in reverse order to see the top tagged first
And the great thing about aggregation is you can gradually build up those stages to see what is going on.
Shake and bake into your own driver implmentation or ODM framework as required.
answered Feb 2 '14 at 10:11
Neil Lunn
96.6k22170180
Thanks for the rapid response! Works like a charm.
– Gil Adirim
Feb 2 '14 at 10:22
3
FYI, the "weird wrapper" format is thegroup()command which is implemented in JavaScript and predates the Aggregation Framework. See also: MongoDB aggregation comparison: group(), $group and MapReduce.
– Stennie
Feb 2 '14 at 11:34
For some reason the aggregate method always seemed confusing but answer really gave me that 'lightbulb' moment...great job =)
– afreeland
Aug 25 '15 at 20:22
thank you so much for explaining and referencing every step! Very easy to adapt to my specific situation!
– user3616725
Apr 27 '17 at 13:15
add a comment |
up vote
100
down vote
accepted
up vote
100
down vote
accepted
What framework are you using? This is not MongoDB shell and looks like some weird wrapper around MapReduce. In that case $unwind would not be available, and you need it for user in the aggregation framework. Here's what you want in the mongo shell:
db.articles.aggregate([
{$match: { class_date: { $gte: date } } },
{$project: { _id: 0, class_artist: 1 } },
{$unwind: "$class_artist" },
{$group: { _id: "$class_artist", tags: { $sum: 1 } }},
{$project: { _id: 0,class_artist: "$_id", tags: 1 } },
{$sort: { tags: -1 } }
])
So efficiently:
Filter by date because you already set a var for the last 7 days
Project only the field(s) we need { We need only one! }
Unwind the array so we now have a record for every array element in every document
Group on the Artist from the expanded documents- Project into a document format you can use as group messed around with _id
Sort the results in reverse order to see the top tagged first
And the great thing about aggregation is you can gradually build up those stages to see what is going on.
Shake and bake into your own driver implmentation or ODM framework as required.
answered Feb 2 '14 at 10:11
Neil Lunn
96.6k22170180
What framework are you using? This is not MongoDB shell and looks like some weird wrapper around MapReduce. In that case $unwind would not be available, and you need it for user in the aggregation framework. Here's what you want in the mongo shell:
db.articles.aggregate([
{$match: { class_date: { $gte: date } } },
{$project: { _id: 0, class_artist: 1 } },
{$unwind: "$class_artist" },
{$group: { _id: "$class_artist", tags: { $sum: 1 } }},
{$project: { _id: 0,class_artist: "$_id", tags: 1 } },
{$sort: { tags: -1 } }
])
So efficiently:
Filter by date because you already set a var for the last 7 days
Project only the field(s) we need { We need only one! }
Unwind the array so we now have a record for every array element in every document
Group on the Artist from the expanded documents- Project into a document format you can use as group messed around with _id
Sort the results in reverse order to see the top tagged first
And the great thing about aggregation is you can gradually build up those stages to see what is going on.
Shake and bake into your own driver implmentation or ODM framework as required.
answered Feb 2 '14 at 10:11
Neil Lunn
96.6k22170180
answered Feb 2 '14 at 10:11
Neil Lunn
96.6k22170180
answered Feb 2 '14 at 10:11
Neil Lunn
96.6k22170180
answered Feb 2 '14 at 10:11
Neil Lunn
96.6k22170180
96.6k22170180
Thanks for the rapid response! Works like a charm.
– Gil Adirim
Feb 2 '14 at 10:22
3
FYI, the "weird wrapper" format is thegroup()command which is implemented in JavaScript and predates the Aggregation Framework. See also: MongoDB aggregation comparison: group(), $group and MapReduce.
– Stennie
Feb 2 '14 at 11:34
For some reason the aggregate method always seemed confusing but answer really gave me that 'lightbulb' moment...great job =)
– afreeland
Aug 25 '15 at 20:22
thank you so much for explaining and referencing every step! Very easy to adapt to my specific situation!
– user3616725
Apr 27 '17 at 13:15
add a comment |
Thanks for the rapid response! Works like a charm.
– Gil Adirim
Feb 2 '14 at 10:22
3
FYI, the "weird wrapper" format is thegroup()command which is implemented in JavaScript and predates the Aggregation Framework. See also: MongoDB aggregation comparison: group(), $group and MapReduce.
– Stennie
Feb 2 '14 at 11:34
For some reason the aggregate method always seemed confusing but answer really gave me that 'lightbulb' moment...great job =)
– afreeland
Aug 25 '15 at 20:22
thank you so much for explaining and referencing every step! Very easy to adapt to my specific situation!
– user3616725
Apr 27 '17 at 13:15
Thanks for the rapid response! Works like a charm.
– Gil Adirim
Feb 2 '14 at 10:22
Thanks for the rapid response! Works like a charm.
– Gil Adirim
Feb 2 '14 at 10:22
3
3
FYI, the "weird wrapper" format is the
group() command which is implemented in JavaScript and predates the Aggregation Framework. See also: MongoDB aggregation comparison: group(), $group and MapReduce.– Stennie
Feb 2 '14 at 11:34
FYI, the "weird wrapper" format is the
group() command which is implemented in JavaScript and predates the Aggregation Framework. See also: MongoDB aggregation comparison: group(), $group and MapReduce.– Stennie
Feb 2 '14 at 11:34
For some reason the aggregate method always seemed confusing but answer really gave me that 'lightbulb' moment...great job =)
– afreeland
Aug 25 '15 at 20:22
For some reason the aggregate method always seemed confusing but answer really gave me that 'lightbulb' moment...great job =)
– afreeland
Aug 25 '15 at 20:22
thank you so much for explaining and referencing every step! Very easy to adapt to my specific situation!
– user3616725
Apr 27 '17 at 13:15
thank you so much for explaining and referencing every step! Very easy to adapt to my specific situation!
– user3616725
Apr 27 '17 at 13:15
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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%2f21509045%2fmongodb-group-by-array-inner-elements%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
