CODEWITHSUNDEEP
mongodbmongodb-querymongolite
Yanis
Yanis

Reputation: 91

Mongolite Aggregate Query with Added Fields

Problem

I have a collection hotelreviews_collection containing 1 million rows (documents) of reviews with various metadata. I would like to group by the Hotel_Name field, count the number of times this hotel has showed up, but also get the fields "lat", "lng" and "Average_Score" with my query. The three extra rows are the same for each Hotel_Name.

I am doing the queries in R using the mongolite library connected to a local MongoDB.

My Attempt

I have gotten to retrieving the Hotel_Names and counting their appearances using the code below, but cannot for the life of me get the other fields to work.

Current Code

overviewData <- M_CONNECTION$aggregate('[{"$group":{"_id":"$Hotel_Name", "count": {"$sum":1}, "average":{"$avg":"$distance"}}}]',
  options = '{"allowDiskUse":true}')

I am completely lost on this, any and all help would be greatly appreciated.

Upvotes: 0

Views: 320

Answers (1)

Yanis
Yanis

Reputation: 91

I have solved my issue using the following code.

db.getCollection("hotelreviews_collection").aggregate(
    [
        { 
            "$group" : {
                "_id" : {
                    "Hotel_Name" : "$Hotel_Name", 
                    "lat" : "$lat", 
                    "lng" : "$lng", 
                    "Average_Score" : "$Average_Score"
                }, 
                "COUNT(Hotel_Name)" : {
                    "$sum" : NumberInt(1)
                }
            }
        }, 
        { 
            "$project" : {
                "Hotel_Name" : "$_id.Hotel_Name", 
                "lat" : "$_id.lat", 
                "lng" : "$_id.lng", 
                "Average_Score" : "$_id.Average_Score", 
                "COUNT(Hotel_Name)" : "$COUNT(Hotel_Name)", 
                "_id" : NumberInt(0)
            }
        }
    ]
)

Upvotes: 0

Related Questions