Mongodb: is it possible to do this in one query?

I am new to Mongodb, Here is my document format:

{
    "_id": {
        "$oid": "5ee023790a0e502e3a9ce9e7"
    },
    "data": {
        "Quick": [
            ["1591745491", "4", "uwp"],
            ["1591745492", "4", "uwp"],
            ["1591745516", "12", "Word"],
            ["1591747346", "8", "uwp"]
        ]
        "Key": [
            ["1591747446", "Num"]
        ]
        "Search": [
            ["1591745491", "tty"],
            ["1591745492", "erp"],
            ["1591745516", "Word"],
            ["1591747346", "uwp"]
        ]

    },
    "devicecode": "MP1G5L9EMP1G5L9E@LENOVO"
}

What I want to do is:

  • group by devicecode
  • for each group, count how many times they used "Quick", "key" and "Search" (count how many line under the name)

Currently I am using a python program to get this done. but I believe that should be a way to get it done within Mongodb.

The output format should look like this:

devicecode: MP1G5L9EMP1G5L9E@LENOVO, Quick: 400, key: 350, Search: 660
...

1 answer

  • answered 2020-10-16 04:33 GBackMania

    You could use aggregation framework to compute the length of individual arrays in the $set stage and then in the $group stage group-by device while summing up the computed array length values from the previous stage. Finally, in the $project stage map _id to devicecode and deselect _id.

      db.getCollection("testcollection").aggregate([
      {
        $set: {
          QuickLen: {
            $size: {
              $ifNull: [
                "$data.Quick",
                []
              ]
            }
          },
          KeyLen: {
            $size: {
              $ifNull: [
                "$data.Key",
                []
              ]
            }
          },
          SearchLen: {
            $size: {
              $ifNull: [
                "$data.Search",
                []
              ]
            }
          }
        }
      },
      {
        $group: {
          _id: "$devicecode",
          Quick: {
            $sum: "$QuickLen"
          },
          key: {
            $sum: "$KeyLen"
          },
          Search: {
            $sum: "$SearchLen"
          }
        }
      },
      {
        $project: {
          devicecode: "$_id",
          Quick: 1,
          key: 1,
          Search: 1,
          _id: 0
        }
      }
    ])