how to create an array on the output of a response using aggregate in Mongodb

Question

I have in my collection a list of objects with this structure:

[
 {
    "country": "colombia",
    "city":"medellin",
    "calification": [
        {
            "_id": 1,
            "stars": 5
        },
        {
            "_id": 2,
            "stars": 3
        }
    ]
},
{
    "country": "colombia",
    "city":"manizales",
    "calification": [
        {
            "_id": 1,
            "stars": 5
        },
        {
            "_id": 2,
            "stars": 5
        }
    ]
},

{
    "country": "argentina",
    "city":"buenos aires",
    "calification": [
        {
            "_id": 1,
            "stars": 5
        },
    ]
},
{
    "country": "perú",
    "city":"cusco",
    "calification": [
        {
            "_id": 3,
            "stars": 3
        },
    ]
  }
]

I am trying to make a filter so that the output is an amount of arrays for each country. this is the example of the output i want.

avg would be result sum 'stars'/ calification.length

{
  "colombia": [
    {
      "city": "medellin",
      "avg": 4,
      "calification": [
        {
          "_id": 1,
          "stars": 5
        },
        {
          "_id": 2,
          "stars": 3
        }
      ]
    },
    {
      "city": "manizales",
      "avg": 5,
      "calification": [
        {
          "_id": 1,
          "stars": 5
        },
        {
          "_id": 2,
          "stars": 3
        }
      ]
    }
  ],
  "argentina": {
    "city": "buenos aires",
    "avg": 5,
    "calification": [
      {
        "_id": 1,
        "stars": 5
      }
    ]
  },
  "peru": {
    "city": "cusco",
    "avg": 4,
    "calification": [
      {
        "_id": 1,
        "stars": 4
      }
    ]
  }
}

I am trying to do this:

    Alcalde.aggregate([

        {
            $addFields: {
                colombia: {
                    "$push": {

                        "$cond": [{ $eq: ["$country", "'Colombia'"] }, true, null]


                    }


                }

            }
        },
        {
            $project: { colombia: "$colombia" }
        }
    ]

how can i do it

Valijon · Accepted Answer · 2020-04-02 22:02:53Z

1

We can make it more elegant.

MongoDB has $avg operator, let's use it. Also, we can use $group operator to group cities for the same country.

At the end, applying $replaceRoot + $arrayToObject** we transform into desired result.

** it's because we cannot use such expression: {"$country":"$city"}

$replaceRoot                                 $arrayToObject

data : {              {                  [                             {
  "key" : "val",  -->   "key" : "val",     {k:"key", v: "val"},  -->     "key" : "val",
  "key2" : "val2"       "key2" : "val2"    {k:"key2", v: "val2"}         "key2" : "val2"
}                     }                  ]                             }

Try this one:

Alcalde.aggregate([
  {
    $group: {
      _id: "$country",
      city: {
        $push: {
          "city": "$city",
          "avg": { $avg: "$calification.stars"},
          "calification": "$calification"
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $arrayToObject: [ [{ "k": "$_id", "v": "$city"}] ]
      }
    }
  }
])

MongoPlayground

EDIT: Generic way to populate city inner object

$$ROOT is variable which stores root document
$mergeObjects adds / override fields to final object

Alcalde.aggregate([
  {
    $group: {
      _id: "$country",
      city: {
        $push: {
          $mergeObjects: [
            "$$ROOT",
            {
              "avg": { "$avg": "$calification.stars" }
            }
          ]
        }
      }
    }
  },
  {
    $project: {
      "city.country": 0
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $arrayToObject: [
          [ { "k": "$_id", "v": "$city" } ]
        ]
      }
    }
  }
])

MongoPlayground

edited Apr 2, 2020 at 22:02

answered Apr 2, 2020 at 20:32

Valijon

13.2k4 gold badges40 silver badges74 bronze badges

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

8 Comments

yavg Over a year ago

Thanks a lot! How I would like to be at your level. I have one last question, you manually include " city ":" $city ","calification": "$calification" , is there a way to import the rest of the collection fields without declaring them manually? (in my real example I have many other fields)

Valijon Over a year ago

@yavg Check it again, I've added generic way. Practice, practice and practice :-)

yavg Over a year ago

I am quite grateful and it gives me some shame with you. I have had a question. Is it possible to filter ONLY in the case of Colombia that returns only what refers to the city of Medellin?

Valijon Over a year ago

@yavg Sure. Check it

yavg Over a year ago

Thank you very much, but it doesn't work. It shows the other city in Colombia that is "Manizales". And it stops showing the rest of the countries and their cities. What I want is that in the case of Colombia, it only shows the city of "Medellin"

|

Collectives™ on Stack Overflow

how to create an array on the output of a response using aggregate in Mongodb

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related