Group if only specific condition fulfil in MongoDB

user_14972015 0
Viewed 251

I want to group some specific records by their pid (only groups) if the number of group events are more than 3.

Example:

Here the number of events for group (pid: 200) is 4 and must be grouped.

- Events -
----------
{_id: ObjectId, type: 'private', pid: 140, data: {...}},
{_id: ObjectId, type: 'group', pid: 800, data: {...}}, // << group 800
{_id: ObjectId, type: 'group', pid: 200, data: {...}}, // << group 200
{_id: ObjectId, type: 'group', pid: 200, data: {...}}, // << group 200
{_id: ObjectId, type: 'private', pid: 140, data: {...}},
{_id: ObjectId, type: 'group', pid: 200, data: {...}}, // << group 200
{_id: ObjectId, type: 'group', pid: 200, data: {...}}, // << group 200
{_id: ObjectId, type: 'private', pid: 130, data: {...}},

Here the group (pid: 200) is grouped and is_too_long: true is added to the record. And group (pid: 800) is not grouped as expected.

- Results -
-----------
{_id: ObjectId, type: 'private', pid: 140, data: {...}},
{_id: ObjectId, type: 'group', pid: 800, data: {...}, is_too_long: false}, // << group 800
{_id: ObjectId, type: 'private', pid: 140, data: {...}},
{_id: ObjectId, type: 'group', pid: 200, is_too_long: true}}, // << group 200 (with [is_too_long: true])
{_id: ObjectId, type: 'private', pid: 130, data: {...}, is_too_long: false},

I tried some other queries but none worked! Please help me!

mongoose aggregation-framework mongodb group-by
1 Answers
  • $facet to separate private and group documents,
  • $group by pid and group document in a array
  • $cond check condition if grouped documents are greater than or equal to 3 then slice single element from array and set is_too_long: true otherwise false
  • $map to iterate loop of docs array and put boolean property is_too_long
  • $unwind deconstruct root array
  • $replaceRoot to replace root object to root
  • $concatArrays, concat private and group array
  • $unwind deconstruct root array
  • $replaceRoot to replace root object in root
db.collection.aggregate([
  {
    $facet: {
      private: [{ $match: { type: "private" } }],
      group: [
        { $match: { type: "group" } },
        {
          $group: {
            _id: "$pid",
            root: { $push: "$$ROOT" }
          }
        },
        {
          $project: {
            root: {
              $cond: [
                { $gte: [{ $size: "$root" }, 3] },
                {
                  is_too_long: true,
                  docs: { $slice: ["$root", 1] }
                },
                {
                  is_too_long: false,
                  docs: "$root"
                }
              ]
            }
          }
        },
        {
          $project: {
            root: {
              $map: {
                input: "$root.docs",
                in: { $mergeObjects: ["$$this", { is_too_long: "$root.is_too_long" }] }
              }
            }
          }
        },
        { $unwind: "$root" },
        { $replaceRoot: { newRoot: "$root" } }
      ]
    }
  },
  { $project: { root: { $concatArrays: ["$private", "$group"] } } },
  { $unwind: "$root" },
  { $replaceRoot: { newRoot: "$root" } }
])

Playground

user_89871280
Related