Getting an array out of the input using aggregate

user_326639 0
Viewed 20

My input file looks like this:

[
    {
        "type" : "asdf",
        "properties" : {
            "Name" : "First center",
            "Code" : "ABCD",
            "Address" : "Emmastr 14",
            "City" : "Rotterdam",
            "Postcode" : 55968,
        }
    },
    {
        "type" : "qwer",
        "properties" : {
            "Name" : "Second center",
            "Code" : "OTHER",
            "Address" : "Havenstraat 15",
            "City" : "Rotterdam",
            "Postcode" : 88767,
        }
    },
    {
        "type" : "zxcv",
        "properties" : {
            "Name" : "Third center",
            "Code" : "ABCD",
            "Address" : "Kerkstraat 16",
            "City" : "Amsterdam",
            "Postcode" : 33948,
        }
    },
    {
        "type" : "tyiu",
        "properties" : {
            "Name" : "Fourth center",
            "Code" : "ABCD",
            "Address" : "Zeestraat 17",
            "City" : "Amsterdam",
            "Postcode" : 56475,
        }
    }
]

I've been tasked to present this information grouped per city (a document for each city). Only the items that have Code="ABCD" should appear in the output. Output should be ordered by city name (_id). Output should be written to a new collection.

So the output I'm looking for is something like this:

_id: "Amsterdam",
center: [
    {"Name": "Third center" , "Postcode": 33948, "Address": "Kerkstraat 16"},
    {"Name": "Fourth center" , "Postcode": 56475, "Address": "Zeestraat 17"}
]

_id: "Rotterdam",
center: [
    {"Name": "First center" , "Postcode": 55968, "Address": "Emmastr 14"}
]

This little snippet filter by "ABCD", groups by city and writes the output to a new collection.

db.centers.aggregate ([
{$match: {"properties.Code": "ABCD"}}
,{ $group: {_id: "$properties.City"}}
,{ $out: "newColl"}
])

But I'm not getting much further because of lack of hands on experience. I struggle getting an array out of something that's not an array in the input. Is there anyone that could help?

mongodb
1 Answers
  • $push to make array of required fields
  • $sort by _id in ascending order
db.centers.aggregate([
  { $match: { "properties.Code": "ABCD" } },
  {
    $group: {
      _id: "$properties.City",
      center: {
        $push: {
          Name: "$properties.Name",
          Postcode: "$properties.Postcode",
          Address: "$properties.Address"
        }
      }
    }
  },
  { $sort: { _id: 1 } },
  { $out: "newColl" }
])

Playground

user_89871280
Related