How to group an array of subdocuments by multiple fields?

Question

I have spent the better part of the day on this and am now out of ideas. Here is my collection:

[
  {
    "_id": "ID_XXX",
    "logs": [
      {
        "lead_id": 123,
        "list_id": "list_44",
        "order_id": "order_1"
      },
      {
        "lead_id": 124,
        "list_id": "list_44",
        "order_id": "order_2"
      }
    ]
  },
  {
    "_id": "ID_YYY",
    "logs": [
      {
        "lead_id": 125,
        "list_id": "list_44",
        "order_id": "order_2"
      },
      {
        "lead_id": 126,
        "list_id": "list_44",
        "order_id": "order_2"
      },
      {
        "lead_id": 127,
        "list_id": "list_44",
        "order_id": "order_3"
      },
      {
        "lead_id": 128,
        "list_id": "list_66",
        "order_id": "order_3"
      }
    ]
  }
]

I'm just trying to get the counts for list_id and order_id while preserving the _id of the document they are in. Here is my desired output:

[
  {
    "_id": "ID_XXX",
    "counts": [
      {
        "lists": {"list_44": 2},
      },
      {
        "orders": {"order_1": 1, "order_2": 1}
      }
    ]
  },
  {
    "_id": "ID_YYY",
    "counts": [
      {
        "lists": {"list_44": 3, "list_66": 1},
      },
      {
        "orders": {"order_2": 2, "order_3": 2}
      }
    ]
  }
]

I have tried way too many aggregate variations to list here, but the latest is this:

db.collection.aggregate([
  {
    $unwind: "$logs"
  },
  {
    $group: {
      _id: "$_id",
      lists: {
        $push: "$logs.list_id"
      },
      orders: {
        $push: "$logs.order_id"
      }
    }
  }
])

Which does not give me what I want. Can anyone point me in the right direction? Here is the playground link: https://mongoplayground.net/p/f-jk7lbSrJ0

Answer 1

• $reduce to iterate loop of logs, convert logs object to array in k(key) v(value) format using $objectToArray, $concatArrays with initialValue in $reduce,
• $filter above reduce result as input and filter required fields from logs
• $unwind deconstruct logs array

db.collection.aggregate([
  {
    $addFields: {
      logs: {
        $filter: {
          input: {
            $reduce: {
              input: "$logs",
              initialValue: [],
              in: { $concatArrays: ["$$value", { $objectToArray: "$$this" }] }
            }
          },
          cond: { $in: ["$$this.k", ["list_id", "order_id"]] }
        }
      }
    }
  },
  { $unwind: "$logs" },

• $group by _id and logs object and get the total count using $sum

  {
    $group: {
      _id: {
        _id: "$_id",
        logs: "$logs"
      },
      counts: { $sum: 1 }
    }
  },

• $group by _id and make lists array if logs.k is list_id and return in k and v format otherwise $$REMOVE, same as for orders make an array of order on the base of order_id
• $addFields to convert lists array from k and v format to object format using $arrayToObjectand same as fororders` array

  {
    $group: {
      _id: "$_id._id",
      lists: {
        $push: {
          $cond: [
            { $eq: ["$_id.logs.k", "list_id"] },
            {
              k: "$_id.logs.v",
              v: "$counts"
            },
            "$$REMOVE"
          ]
        }
      },
      orders: {
        $push: {
          $cond: [
            { $eq: ["$_id.logs.k", "order_id"] },
            {
              k: "$_id.logs.v",
              v: "$counts"
            },
            "$$REMOVE"
          ]
        }
      }
    }
  },
  {
    $addFields: {
      lists: { $arrayToObject: "$lists" },
      orders: { $arrayToObject: "$orders" }
    }
  }
])

Playground