MongoDB create an array within an array

Question

I am struggling to get my head around MongoDB and aggregates and groups. I've spent about 3 days so far.

I have source data that looks like...

{
   "formName" : "my form",
   "updatedAt" : "2021-11-02T13:29:00.123Z",
},
{
   "formName" : "another form",
   "lastUpdated" : "2021-10-01T13:29:00.123123",
},

Note that there are potentially different date names, though these are the only differences.

I am attempting to achieve an output of...

{
    "_id": null,
    "text": "my form",  (NOTE: This is the formName)
    "children": [{
       "text" : 2021, (This is the year part of the updated)
       "children" : [
          {"text" : 1}, (These are the month part of the updated)
          {"text" : 2},
          {"text" : 3},
          {"text" : 4}
       ]
    },
    ]
}

So, basically a tree, which has formName, with child branch of years, with child branch of months.

I have tried all kinds of things, many don't work, such as nested $addToSet inside $groups.

I have been close, but I can't solve it.

This is the closest, but this doesn't work.

db.FormsStore.aggregate( [
  
  {$match:{myKey:"a guid to group my forms together"}},
  {$project: {formName:1, lastUpdated:1, updatedAt:1}},
  { 
    $group: { 
      _id:   { formName: "$formName" }, 
      Year: {$addToSet: {$year: {$dateFromString: { dateString: "$lastUpdated" }}}},
      Month: {$addToSet: {$month: {$dateFromString: { dateString: "$lastUpdated" }}}},
    } 
  }, 
  { 
    $group: {
      _id: { formName: "$_id.formName" }, 
     Time: {$addToSet: {year: "$Year", month: "$Month"}}
    } 
  } 
]
)

The output from that is showing...

{ 
    _id: { formName: 'One of my forms' },
    Time: [
      {
        year: [ 2021 ],
        month: [ 10, 11 ] 
      }
    ]
 }

This will all be used in C#

Your help would be greatly appreciated.

Answer 1

Query

• adds a new field "date" with the date on Date format
• group first the more specific (formname+year) to put the months in the array
• group then the less specific (formname) to put the years in the array

aggregate(
[{"$set": 
    {"date": 
      {"$cond": 
        ["$updatedAt", {"$dateFromString": {"dateString": "$updatedAt"}},
          {"$dateFromString": {"dateString": "$lastUpdated"}}]},
      "updatedAt": "$$REMOVE",
      "lastUpdated": "$$REMOVE"}},
  {"$group": 
    {"_id": {"text": "$formName", "year": {"$year": "$date"}},
      "children": {"$push": {"text": {"$month": "$date"}}}}},
  {"$group": 
    {"_id": "$_id.text",
      "children": 
      {"$push": {"text": "$_id.year", "children": "$children"}}}},
  {"$set": {"text": "$_id", "_id": "$$REMOVE"}}])

---

Edit

The bellow sorts also by year/month, and keeps only unique year/months per formName.

• the difference is the group by formName,year,month to take the unique (first aacumulator will take one only of those that have the same in all 3)
• replace-root (make that first document ROOT document)
• and then sort by those 3 fields (descending year,ascending month)
• group
• sort by 2 fields
• final group

PlayMongo
*mongoplaygroung loses the order of fields, run it on your driver also to be sure

aggregate(
[{"$set": 
    {"date": 
      {"$cond": 
        ["$updatedAt", {"$dateFromString": {"dateString": "$updatedAt"}},
          {"$dateFromString": {"dateString": "$lastUpdated"}}]},
      "updatedAt": "$$REMOVE",
      "lastUpdated": "$$REMOVE"}},
  {"$set": {"year": {"$year": "$date"}, "month": {"$month": "$date"}}},
  {"$group": 
    {"_id": {"formName": "$formName", "year": "$year", "month": "$month"},
      "doc": {"$first": "$$ROOT"}}},
  {"$replaceRoot": {"newRoot": "$doc"}},
  {"$sort": {"formName": 1, "year": -1, "month": 1}},
  {"$group": 
    {"_id": {"text": "$formName", "year": "$year"},
      "children": {"$push": {"text": "$month"}}}},
  {"$sort": {"_id.text": 1, "_id.year": -1}},
  {"$group": 
    {"_id": "$_id.text",
      "children": 
      {"$push": {"text": "$_id.year", "children": "$children"}}}},
  {"$set": {"text": "$_id", "_id": "$$REMOVE"}}])

With data

[
  {
    "formName": "my form",
    "updatedAt": "2021-11-02T23:30:15.123Z"
  },
  {
    "formName": "my form",
    "updatedAt": "2021-10-02T23:30:15.123Z"
  },
  {
    "formName": "my form",
    "updatedAt": "2020-06-02T23:30:15.123Z"
  },
  {
    "formName": "my form",
    "updatedAt": "2020-07-02T23:30:15.123Z"
  },
  {
    "formName": "another form",
    "updatedAt": "2021-10-01T23:30:15.123Z"
  },
  {
    "formName": "another form",
    "updatedAt": "2021-10-01T23:30:15.123Z"
  },
  {
    "formName": "another form",
    "updatedAt": "2021-09-01T23:30:15.123Z"
  },
  {
    "formName": "another form",
    "updatedAt": "2021-08-01T23:30:15.123Z"
  },
  {
    "formName": "another form",
    "updatedAt": "2020-10-01T23:30:15.123Z"
  }
]

I got results

[{
  "children": [
    {
      "text": 2021,
      "children": [
        {
          "text": 10
        },
        {
          "text": 11
        }
      ]
    },
    {
      "text": 2020,
      "children": [
        {
          "text": 6
        },
        {
          "text": 7
        }
      ]
    }
  ],
  "text": "my form"
},
{
  "children": [
    {
      "text": 2021,
      "children": [
        {
          "text": 8
        },
        {
          "text": 9
        },
        {
          "text": 10
        }
      ]
    },
    {
      "text": 2020,
      "children": [
        {
          "text": 10
        }
      ]
    }
  ],
  "text": "another form"
}]

Answer 2

I continued to work on it, and while this is also not quite right (yet), here is what I came up with.

db.FormsStore.aggregate([
  
  {$project: {formName:1, lastUpdated:1, updatedAt:1}},
  { 
    $group: { 
      _id: {formName: "$formName", Year: {$year: {$dateFromString: { dateString: "$updatedAt" }}}, Month: {$month: {$dateFromString: { dateString: "$updatedAt" }}}},
    } 
  },
  {
    $group: {
        _id: {formName: "$_id.formName", Year: "$_id.Year"}, 
        Months: {$addToSet: {Text: "$_id.Month"}}
    }
  },
  {
    $group: {
        _id:  "$_id.formName", Children: {$addToSet: {Text: "$_id.Year", Children: "$Months"}}, 
        
    }
  }
])

Getting all my data in the first group, then creating a set with the months in the second group, then creating a set with the years and adding the months to each year in the third group.