Aggregate to Output Nested Lists

Question

I've been facing some problems getting the output that I want. Below is the sample data structure from my collection named "testdata_4":

[
    {
        "_id": 1,
        "Record": 1,
        "Link": "www.google.com",
        "Link_Title": "Google",
        "Location": ["loc1", "loc2", "loc3", "loc4"],
        "Date": 2017,
        "People": ["ppl1", "ppl2", "ppl3", "ppl4"]
    },
    {
        "_id": 2,
        "Record": 2,
        "Link": "www.facebook.com",
        "Link_Title": "Facebook",
        "Location": ["loc1", "loc2", "loc3", "loc4"],
        "Date": 2016,
        "People": ["ppl1", "ppl2", "ppl3", "ppl4"]
    }
]

The query that I've tried to use is this :

db.testdata_4.aggregate([{
    "$unwind": "$Location"
},{
    "$group": {
        "_id": {
            "Locations": "$Location",
            "Year": "$Date"
        },
        Links: {
            $addToSet: "$Link"
        },
        Titles: {
            $addToSet: "$Title"
        }
    }
}, {
    "$sort": { "_id.Year": 1 }
},{
    "$group": {
        "_id": "$_id.Locations",
        Records: {
            $push: {
                "Year": "$_id.Year",
                "Links": { $setUnion: ["$Links", "$Titles"]}
            }
        }
    }
},{
    "$sort": { "_id": 1 }
}]).toArray()

And the output that I've gotten from the query above is this :

[
    {
        "_id" : "loc2",
        "Records" : [
            {
                "Year" : 2016,
                "Links" : [
                    "CooCoo",
                    "Facebook",
                    "Google",
                    "www.coocoo.com",
                    "www.facebook.com",
                    "www.google.com"
                ]
            }
        ]
    },
    {
        "_id" : "loc3",
        "Records" : [
            {
                "Year" : 2017,
                "Links" : [
                    "CooCoo",
                    "Facebook",
                    "www.coocoo.com",
                    "www.facebook.com"
                ]
            }
        ]
    }
]

However, the output that I've gotten above is a tad bit off from the output that I wish to get, which should look like the sample output below (re-using the output above) :

[
    {
        "_id" : "loc2",
        "Records" : [
            {
                "Year" : 2016,
                "Links" : [
                    {"Title":"CooCoo", "Link":"www.coocoo.com"},
                    {"Title":"Facebook", "Link":"www.facebook.com"},
                    {"Title":"Google", "Link":"www.google.com"}
                ]
            }
        ]
    },
    {
        "_id" : "loc3",
        "Records" : [
            {
                "Year" : 2017,
                "Links" : [
                    {"Title": "CooCoo", "Link":"www.coocoo.com"},
                    {"Title": "Facebook", "Link":"www.facebook.com"}                    
                ]
            }
        ]
    }
]

So my question is, is it possible for me to aggregate and get the output like I want above, or not possible at all? If it's possible, any solution provided will be most welcomed as long as it helps me progress just a bit! Thanks in advance!

Answer 1

This query may also will give you expected output

db.testdata_4.aggregate([
  {"$unwind": "$Location"},
  {"$group": {
    _id: {"Locations": "$Location","Year": "$Date"},
    Links: { $addToSet: {Link: "$Link", Title: "$Link_Title"}}
  }},
  {"$sort": {"_id.Year": 1}},
  {"$group": {
    "_id": "$_id.Locations",
    Records: {$push: {"Year": "$_id.Year", "Links": "$Links"}}
  }},
  {"$sort": {"_id": 1}}
]).toArray()

Answer 2

If I read your intent correctly, then you group on everything for distinct values rather than $addToSet:

db.testdata_4.aggregate([
  { "$unwind": "$Location" },
  { "$group": {
    "_id": {
      "Location": "$Location",
      "Year": "$Date",
      "Title": "$Link_Title",
      "Link": "$Link"
    }
  }},
  { "$group": {
    "_id": {
      "Location": "$_id.Location",
      "Year": "$_id.Year",
    },
    "Links": { "$push": {
      "Title": "$_id.Title",
      "Link": "$_id.Link"
    }}
  }},
  { "$sort": { "_id.Year": 1 } },
  { "$group": {
    "_id": "$_id.Location",
    "Records": {
      "$push": {
        "Year": "$_id.Year",
        "Links": "$Links"
      }
    }
  }}
])

So after you $unwind the array you put everything into the _id key of $group to get the distinct values.

Then it is just a matter of grouping first by location and year and creating the "Links" array, then group again on just the location to create the "Records" array.