MongoDB facet subpipeline array filter with condition

Question

I have aggregation with query as https://mongoplayground.net/p/jxaI7MAyBmJ

I have project type, count and empName by adding stages under data to filter with below 2 condition

type as a2 and
empName (type as a1) should need to equal with empName (type as a2).

db.collection.aggregate([
  {
    $facet: {
      "data": [
        {
          $match: {
            type: {
              $in: [
                "a1",
                "a2"
              ]
            }
          }
        },
        {
          $group: {
            _id: {
              emp_name: "$emp_name",
              type: "$type"
            },
            count: {
              $sum: 1
            }
          }
        },
        {
          $project: {
            empName: "$_id.emp_name",
            type: "$_id.type",
            count: 1,
            _id: 0
          }
        }
        
        //Have to ADD sub stages here with the above mentioned 2 condition
      ]
    }
  }
])

I tried on applying this condition however unable to resolve this.

Thanks in advance

[{"data":[{"count":1,"empName":"John","type":"a2"},{"count":1,"empName":"Jane","type":"a2"},{"count":2,"empName":"Bob","type":"a2"}]}] this is the output — Prakash
– Prakash, Commented Feb 20, 2024 at 8:10
it has to show only type with a2 and empName should need to equal in both type a2 and a1 — Prakash
– Prakash, Commented Feb 20, 2024 at 8:11
Thanks @cmgchess mentioned condition have to add after the project in the facet, update the question and also mongoplayground.net/p/jxaI7MAyBmJ — Prakash
– Prakash, Commented Feb 21, 2024 at 6:02

Wernfried Domscheit · Accepted Answer · 2024-02-21 07:15:08Z

-1

I still don't understand what you are looking for, but this one gives the desired result:

db.collection.aggregate([
   { $match: { type: { $in: ["a1", "a2"] } } },
   {
      $facet: {
         a1: [
            { $match: { type: "a1" } },
            { $project: { _id: 0, emp_name: 1 } }
         ],
         a2: [
            { $match: { type: "a2" } }
         ],
      }
   },
   { $unwind: "$a2" },
   { $match: { $expr: { $in: ["$a2.emp_name", "$a1.emp_name"] } } },
   {
      $group: {
         _id: "$a2.emp_name",
         count: { $count: {} },
      }
   },
   {
      $project: {
         _id: 0,
         type: "a2",
         count: 1,
         empName: "$_id"
      }
   }
])

Mongo Playground

edited Feb 21, 2024 at 7:15

answered Feb 20, 2024 at 8:16

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Prakash Over a year ago

Thanks @Wernfried Here data Array has list of JSONObject with empName, type and count. we have to apply the mentioned condition after the project. Shared query is not applied with condition empName (type as a1) should need to equal with empName (type as a2) and also type as a2 has to apply after the project substage

Wernfried Domscheit Over a year ago

Please see my update. Compared to proposal from @cmgchess is uses one $group less, thus it could be faster

Collectives™ on Stack Overflow

MongoDB facet subpipeline array filter with condition

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related