MongoDB Aggregate a sub field of an array (Array is main field)

Question

I have a database in MongoDB. There are three main fields: _id, Reviews, HotelInfo. Reviews and HotelInfo are arrays. Reviews has a field called Author. I would like to print out every author name (once) and the amount of times they appear within the dataset.

I tried:

db.reviews.aggregate( {$group : { _id : '$Reviews.Author', count : {$sum : 1}}} ).pretty()

A part of the results were:

"_id" : [ "VijGuy", "Stephtastic", "dakota431", "luce_sociator", "ArkansasMomOf3", "ccslilqt6969", "RJeanM", "MissDusty", "sammymd", "A TripAdvisor Member", "A TripAdvisor Member" ], "count" : 1

How it should be:

{ "_id" : "VijGuy", "count" : 1 }, { "_id" : "Stephtastic", "count" : 1 }

I posted the JSON format below.

Any idea on how to do this would be appreciated

JSON Format

({ $unwind: '$Reviews' }, {$group : { _id : '$Reviews.Author', count : {$sum : 1}}}) — elad frizi
– elad frizi, Commented Dec 8, 2018 at 22:18

elad frizi · Accepted Answer · 2018-12-08 22:41:37Z

Lets assume that this is our collection.

[{ 
_id: 1, 
Reviews: [{Author: 'elad' , txt: 'good'}, {Author: 'chen', txt: 'bad'}]
},
{
_id: 2,
Reviews: [{Author: 'elad', txt : 'nice'}]
}]

to get the data as you want we need to first use the unwind stage and then the group stage.

[{ $unwind: '$Reviews' }, {$group : { _id : '$Reviews.Author', count : {$sum : 1}}}]

You need to first unwind the collection by the Reviews field. after the unwind stage our data in the pipeline will look like this.

{_id:1, Reviews: {Author: 'elad' , txt: 'good'}},
{_id:1, Reviews: {Author: 'chen' , txt: 'bad'}},
{_id:2, Revies:  {Author: 'elad', txt : 'nice'}

The unwind created a document for each element in Reviews array with the element itself and his host document. Now its easy to group in useful ways as you want. Now we can use the same group that you wrote and we will get the results.

after the group our data will look like this:

[{_id: 'elad',sum:2},{_id: 'chen', sum: 1}]

Unwind is a very important pipeline stage in the aggregation framework. Its help us transform complex and nested documents into flat and simple, and that help us to query the data in different ways.

What's the $unwind operator in MongoDB?

Collectives™ on Stack Overflow

MongoDB Aggregate a sub field of an array (Array is main field)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related