Querying nested MongoDB documents without arrays

Question

In this document "27", and "28" are days of the month, while "6" through "11" under "27" represents day's hour

{
        "values" : {
                "27" : {
                        "6" : {
                                "users" : [
                                        "5b5abc5ddd601f0b6681358a"
                                ]
                        },
                        "7" : {
                                "users" : [
                                        "5b5ac75cdd601f0b668157ff",
                                        "5b5acd0ddd601f0b66816803"
                                ]
                        },
                        "8" : {
                                "users" : [
                                        "5b5acd0ddd601f0b66816803"
                                ]
                        },
                        "9" : {
                                "users" : [
                                        "5b5acd0ddd601f0b66816803",
                                        "5b5ae89b781e011702f00812"
                                ]
                        },
                        "10" : {
                                "users" : [
                                        "5b5ae89b781e011702f00812"
                                ]
                        }
                },
                "28" : {
                        "11" : {
                                "users" : [
                                        "5b5abacadd601f0b6681312e"
                                ]
                        }
                }
        }
}

I want to to be able to query it in two ways first as a {day: users-count} example:

{"27" : 7,
"28" : 1 }

and other way would be {day : {hour : users-count} example:

{"27" : ["6" : 1,
        "7" : 2,
        "8" : 1,
        "9" : 2,
        "10": 1],

 "28" : ["11" :1]}

to use it in forecasting and time-series analysis.

What would be a smart and optimized way to do this?

Answer 1

You can try below aggregation

db.collection.aggregate([
  { "$replaceRoot": {
    "newRoot": {
      "$arrayToObject": {
        "$map": {
          "input": {
            "$map": {
              "input": { "$objectToArray": "$values" },
              "as": "val",
              "in": { "k": "$$val.k", "v": { "$objectToArray": "$$val.v" }}
            }
          },
          "as": "val",
          "in": {
            "k": "$$val.k",
            "v": {
              "$sum": {
                "$map": {
                  "input": "$$val.v",
                  "as": "v2",
                  "in": { "$size": "$$v2.v.users" }
                }
              }
            }
          }
        }
      }
    }
  }}
])

Output

[
  {
    "27": 7,
    "28": 1
  }
]

For the second output

db.collection.aggregate([
  { "$replaceRoot": {
    "newRoot": {
      "$arrayToObject": {
        "$map": {
          "input": {
            "$map": {
              "input": { "$objectToArray": "$values" },
              "as": "val",
              "in": { "k": "$$val.k", "v": { "$objectToArray": "$$val.v" }}
            }
          },
          "as": "val",
          "in": {
            "k": "$$val.k",
            "v": {
              "$arrayToObject": {
                "$map": {
                  "input": "$$val.v",
                  "as": "v2",
                  "in": { "k": "$$v2.k", "v": { "$size": "$$v2.v.users" }}
                }
              }
            }
          }
        }
      }
    }
  }}
])

Output

[
  {
    "27": {
      "10": 1,
      "6": 1,
      "7": 2,
      "8": 1,
      "9": 2
    },
    "28": {
      "11": 1
    }
  }
]

Another one

db.collection.aggregate([
  { "$replaceRoot": {
    "newRoot": {
      "$arrayToObject": {
        "$map": {
          "input": {
            "$map": {
              "input": { "$objectToArray": "$values" },
              "as": "val",
              "in": {
                "k": "$$val.k",
                "v": { "$objectToArray": "$$val.v" }
              }
            }
          },
          "as": "val",
          "in": {
            "k": "$$val.k",
            "v": [
              { "$arrayToObject": {
                "$map": {
                  "input": "$$val.v",
                  "as": "v2",
                  "in": { "k": "$$v2.k", "v": { "$size": "$$v2.v.users" }}
                }
              }}
            ]
          }
        }
      }
    }
  }}
])

Output

[
  {
    "27": [
      {
        "10": 1,
        "6": 1,
        "7": 2,
        "8": 1,
        "9": 2
      }
    ],
    "28": [
      {
        "11": 1
      }
    ]
  }
]