MongoDB create an array within an array-CodePudding

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.

CodePudding user response：

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

Test code here

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"}}])

CodePudding user response：

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.