Querying with dynamic keys

[Pugabyte (Griffin)]

I have a MongoDB schema that looks like this:

{ 
    "_id" : "b299e2d2-4011-4968-a55c-638d9c37664b", 
    "times" : {
        "2020-05-30" : 1685, 
        "2020-05-29" : 5470
    }
},
{ 
    "_id" : "0859e698-7e57-44ba-b84e-cf17ac9d0f77", 
    "times" : {
        "2020-05-30" : 1520, 
        "2020-05-29" : 10085
    }
}

and I want a query that sums all the values of each date together. I got this:

db.getCollection("hours").aggregate(
    { 
        $project : {
            '_id': "$_id",
            'sum': { $sum : [ "$times.2020-05-30", "$times.2020-05-29" ] }
        }
    },
    { $sort : { 'sum': -1 } }
);

but I don’t want to have to list out every date that I want to include, i just want all of them (or even better, a way to regex match them, e.g. 2020-05-*; is there any shortcut to this besides switching to mapreduce, or changing my data structure?

EDIT: I was able to solve the issue with the $objectToArray operator, like so:

db.hours.aggregate([
    { $project : { _id : "$_id", times : { $objectToArray: "$times" } } },
    { $project : { _id : "$_id", total : { $sum : "$times.v" } } },
    { $sort : { 'total': -1 } }
]);

So I’m trying to filter using the $match operator now (between the two projects):

{ $match : { "times.k" : { $regex : "2020-05-.*" } } },

And it’s filtering down to the records that have at least one key that pass that filter, but it’s still summing all the keys, rather than just the keys that match that filter. How can I fix this?

[Prasad_Saya]

Hello Pugabyte Griffin,

The { $project : { _id : "$_id", times : { $objectToArray: "$times" } } } returns an array of of times objects. To filter the array elements and then grouping and summing you can use the any of the following aggregates:

db.hours.aggregate([
  { 
      $project : { times : { $objectToArray: "$times" } } 
  },
  { 
      $unwind: "$times" 
  },
  { 
      $match : { "times.k" : { $regex : "2020-05-.*" } } 
  },
  { 
      $group: { _id: "$_id", total: { $sum: "$times.v" } } 
  },
  { 
      $sort : { total: -1 } 
  }
])

db.hours.aggregate([
  { 
      $project : { times : { $objectToArray: "$times" } } 
  },
  { 
      $unwind: "$times" 
  },
  { 
      $group: {
           _id: "$_id", 
           total: { 
               $sum: { 
                   $cond: [ 
                       { $eq: [ { $regexMatch: { input: "$times.k" , regex: "2020-05-.*" } }, true ] }, 
                       "$times.v", 0 
                   ] 
               } 
           } 
      } 
  },
  { 
      $sort : { total: -1 } 
  }
])

db.hours.aggregate([
  { 
      $project : { 
          total: { 
              $reduce: { 
                  input: { $objectToArray: "$times" },
                  initialValue: 0,
                  in: {
                      $let: {
                          vars: {
                              matches: { $regexMatch: { input: "$$this.k" , regex: "2020-05-.*" } }
                          },
                          in: { 
                              $cond: [ { $eq: [ "$$matches", true ] }, { $add: [ "$$value", "$$this.v" ] }, "$$value" ]
                          }
                      }
                  }
              }
          }
      }
  },
  {
      $sort: { total: -1 }
  }
])