Home > Mobile >  How to combine two aggregate function result in MongoDB
How to combine two aggregate function result in MongoDB

Time:09-21

I have these two aggregations, as shown below. The first one returns the clients that have zero visits(no visit objects created for the client). The second one returns the clients with less visits than the others(at least 5). I want to combine these two aggregations results into one so that they will be ordered like this:

[ no visits clients,

least visits clients ]

Is that possible without simply using Array concat method?

these two aggregations:

 let clients = await clientModel.aggregate([
    {
      $lookup: {
        from: "visits",
        localField: "_id",
        foreignField: "client",
        as: "visits",
      },
    },
    {
      $project: {
        _id: 1,
        name: 1,
        count: {
          $size: "$visits",
        },
      },
    },
    {
      $match: {
        count: 0,
      },
    },
    {
      $project: {
        _id: 1,
        name: 1,
      },
    },
  ]);

with this result :

 "Zero visits": [
    {
        "_id": "6182ebe5ea218257521cdc36",
        "name": "cleint_807"
    },
    {
        "_id": "6182ebfaea218257521cdc9a",
        "name": "cleint_907"
    },
    {
        "_id": "6182ec02ea218257521cdcbe",
        "name": "cleint_943"
    },
    {
        "_id": "6182ec20ea218257521cdd48",
        "name": "cleint_71"
    },
    {
        "_id": "6182ec29ea218257521cdd74",
        "name": "cleint_115"
    },
    {
        "_id": "6182ec54ea218257521cde5a",
        "name": "cleint_345"
    },
    {
        "_id": "6182ec61ea218257521cdea3",
        "name": "cleint_418"

},
    {
        "_id": "6182ec71ea218257521cdef4",
        "name": "cleint_499"
    },
    {
        "_id": "6182ec96ea218257521cdfbc",
        "name": "cleint_699"
    }
],

Second aggregation:

    visits = await visitModel.aggregate([
      { $match: { time: { $lte:  to, $gte:  from } } },
      {
        $project: {
          date: {
            $toDate: "$time",
          },
          client: 1,
        },
      },
      {
        $project: {
          day: {
            $dayOfWeek: "$date",
          },
          client: 1,
        },
      },
      {
        $match: {
          day: 2,
        },
      },
      {
        $group: {
          _id: {
            client: "$client",
          },
          count: {
            $sum: 1,
          },
        },
      },
      {
        $sort: {
          count: 1,
        },
      },
      {
        $limit: 10,
      },
      {
        $lookup: {
          from: "clients",
          localField: "_id.client",
          foreignField: "_id",
          as: "client",
        },
      },
      {
        $unwind: {
          path: "$client",
          preserveNullAndEmptyArrays: false,
        },
      },
      {
        $project: {
          _id: "$client._id",
          name: "$client.name",
        },
      },
    ]);
  

with this result :

"Less visits": [
    {
        "_id": "6182eb73ea218257521cd9f6",
        "name": "cleint_231"
    },
    {
        "_id": "6182ebe9ea218257521cdc48",
        "name": "cleint_825"
    },
    {
        "_id": "6182ec7dea218257521cdf35",
        "name": "cleint_564"
    },
    {
        "_id": "6182ec2cea218257521cdd83",
        "name": "cleint_130"
    },
    {
        "_id": "6182ebd6ea218257521cdbe8",
        "name": "cleint_729"
    },
    {
        "_id": "6182ebc6ea218257521cdb9c",
        "name": "cleint_653"
    },
    {
        "_id": "6182ec0bea218257521cdced",
        "name": "cleint_990"
    },
    {
        "_id": "6182ebd3ea218257521cdbd8",
        "name": "cleint_713"
    },
    {
        "_id": "6182ec81ea218257521cdf48",
        "name": "cleint_583"
    },
    {
        "_id": "6182ec2cea218257521cdd85",
        "name": "cleint_132"
    }
]

Response

   res.json({
    success: true,
    "Zero visits": clients,
    "Less visits": visits,
  });
};

I need to combine both results

CodePudding user response:

Use $unionWith:

clientModel.aggregate([
   <stage1>, ...
   { $unionWith: { coll: "visitModel", pipeline: [ <stage1>, ... ] } }
])
  • Related