$group to get all related data in array

Question

I want to query documents and return a list of objects representing all categories, with an array of objects within each category object, containing the documents matching that category.

If it helps to explain: the database has a list of documents representing news articles, and I need to display them grouped by category on the front end, so I'll need an object for each category, with each category object containing an array of the articles (as objects) to display under that category.

I have documents of the following form:

{ 
    "date" : ISODate("2018-06-25T00:00:00.000+0000"), 
    "detail" : {
        "category" : "world", 
        "title" : "stuff is happening", 
        "content" : "lorem ipsum dolor"
    } 
}
{ 
    "date" : ISODate("2018-03-08T00:00:00.000+0000"), 
    "detail" : {
        "category" : "national", 
        "title" : "more stuff is happening", 
        "content" : "sit amet"
    } 
}
{ 
    "date" : ISODate("2018-02-02T00:00:00.000+0000"), 
    "detail" : {
        "category" : "local", 
        "title" : "local stuff is happening", 
        "content" : "not much happening locally"
    }
}

...and I would like my result to look something like this:

{  _id: "world",
   count: 3,
   "articles" : {[
       {
       "date" : ISODate("2018-06-25T00:00:00.000+0000"), 
       "detail" : {
           "category" : "world", 
           "title" : "stuff is happening", 
           "content" : "lorem ipsum dolor"
       },
       {
       "date" : ISODate("2018-06-25T00:00:00.000+0000"), 
       "detail" : {
           "category" : "world", 
           "title" : "stuff is happening #2", 
           "content" : "lorem ipsum dolor"
       },
       {
       "date" : ISODate("2018-06-25T00:00:00.000+0000"), 
       "detail" : {
           "category" : "world", 
           "title" : "stuff is happening #3", 
           "content" : "lorem ipsum dolor"
       }
    ]}
}
{ _id: "national",
   count: 1,
   "articles" : {[
     {
       "date" : ISODate("2018-03-08T00:00:00.000+0000"), 
       "detail" : {
           "category" : "national", 
           "title" : "more stuff is happening", 
           "content" : "sit amet"
       } 
     }
}

...and so on. So far, I have this query:

db.news_feed.aggregate([
{ $match: {} },
{ $group: {_id:'$detail.category', count: { $sum:1 } } },
{ $addFields: {
                    "article": "$$ROOT"
                }
} 
])

This gets me a summarized count of each category, but obviously the $$ROOT inserts the result after $group, not the original document(s):

{ 
    "_id" : "world", 
    "count" : 3.0, 
    "articles" : {
        "_id" : "world", 
        "count" : 3.0
    }
}
{ 
    "_id" : "national", 
    "count" : 1.0, 
    "brief" : {
        "_id" : "national", 
        "count" : 1.0
    }
}
{ 
    "_id" : "local", 
    "count" : 5.0, 
    "brief" : {
        "_id" : "local", 
        "count" : 5.0
    }
}
.
.

What can I do to insert an array of "articles" that are the original articles which are being summed up?

Ashh · Accepted Answer · 2018-12-11 16:57:17Z

1

You need to use $push accumulator with the $group stage

db.news_feed.aggregate([
  { "$group": {
    "_id": "$detail.category",
    "articles": { "$push": "$$ROOT" },
    "count": { "$sum": 1 }
  }}
])

answered Dec 11, 2018 at 16:57

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Marquez Over a year ago

Thanks so much! The $push accumulator was exactly what I needed, but I didn't even know it existed. Today I learned.

Collectives™ on Stack Overflow

$group to get all related data in array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related