mongodb can $unionWith use in $push

user_10787160 0
Viewed 53

I want to get related data based on current item processing.

Sample:

[
    { field1: 1, field2: 2, value: 12 },
    { field1: 1, field2: 2, value: 21 },
    { field1: 1, value: 1 },
    { field2: 2, value: 2 },
    { field1: 2, field2: 3, value: 23 }

];

and result:

[
    {
        _id: { field1: 1, field2: 2 },
        value: [12, 12],
        relatedValue: [1, 2], // of item 1 and 2 because field 1 = 1 or field 2 = 2
    },
];

Sample query:

db.collectionA.aggregate([
  {
    $match: { field1: 1 }
  },
  {
    "$group":{
      "_id":{
        "field1":"$field1",
        "field2":"$field2"
      },
      "alerts":{
        "$push":{
          "_id":"$_id",
          "value":"$value",
          "relatedData": {
            "$unionWith": {
               "coll": "collectionA",
               "pipeline": [{
                 "$match": {
                   "$or": [
                     { "field1": "$field1" },
                     { "field2": "$field2" }
                   ]
                 }
               }]   
             }     
          }
        }
      }
    }
  }
])

I tried run this query but error, Please help me fix or give a solution

// Edited: value should be array because I want to group data by field1, field2 and push all value of group to an array

mongodb aggregate
1 Answers

You're trying to use $unionWith within $group but it is a "pipeline stage" meaning it can't be used like that, the same way you can't use $group within a $group.

Additionally this stage is used to "union" two collections and not to populate data based on value matches ( which it seems you're trying to do here ), for this case you want to use $lookup, like so:

db.collection.aggregate([
  {
    $lookup: {
      from: "collection",
      let: {
        field1: "$field1",
        field2: "$field2",
        docId: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $or: [
                    {
                      $eq: [
                        "$$field1",
                        "$field1"
                      ]
                    },
                    {
                      $eq: [
                        "$$field2",
                        "$field2"
                      ]
                    }
                  ]
                },
                {
                  $ne: [
                    "$$docId",
                    "$_id"
                  ]
                }
              ]
            }
          }
        },
        {
          $project: {
            value: 1
          }
        }
      ],
      as: "relatedData"
    }
  },
  {
    $group: {
      _id: {
        field1: "$field1",
        field2: "$field2"
      },
      values: {
        $push: "$value"
      },
      relatedValue: {
        $push: {
          $map: {
            input: "$relatedData",
            in: "$$this.value"
          }
        }
      }
    }
  },
  {
    $project: {
      field1: "$_id.field1",
      field2: "$_id.field2",
      values: 1,
      relatedValues: {
        "$setDifference": [
          {
            "$reduce": {
              input: "$relatedValue",
              initialValue: [],
              in: {
                "$setUnion": [
                  "$$this",
                  "$$value"
                ]
              }
            }
          },
          "$values"
        ]
      }
    }
  }
])

Mongo Playground

user_107703700
Related