CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Java_begins
Java_begins

Reputation: 1679

MongoDB aggregate using $match with $expr with array

MongoDB 5.0.9

I am trying to get

  1. value of application within course and their specification
  2. value of paid application ( status : paid) based on course and their specification

courses collection having multiple courses with specification which might be there maybe not

[
    {
      "_id": {
        "$oid": "62aab6669b3740313d881a30"
      },
      "course_name": "Master",
      "fees": "Rs.1000.0/-",
      "course_specialization": [
        {
          "spec_name": "Social Work",
          "is_activated": true
        }
      ],
      "college_id": {
        "$oid": "628dfd41ef796e8f757a5c13"
      },
      "is_pg": true
    },
    {
      "_id": {
        "$oid": "62aab6669b3740313d881a38"
      },
      "college_id": {
        "$oid": "628dfd41ef796e8f757a5c13"
      },
      "course_name": "BBA",
      "fees": "Rs.1000.0/-",
      "is_pg": false,
      "course_specialization": null
    },
    {
      "_id": {
        "$oid": "628f3967cb69fc0789e69181"
      },
      "course_name": "BTech",
      "fees": "Rs.1000.0/-",
      "course_specialization": [
        {
          "spec_name": "Computer Science and Engineering",
          "is_activated": true
        },
        {
          "spec_name": "Mutiple Specs",
          "is_activated": true
        }
      ],
      "college_id": {
        "$oid": "628dfd41ef796e8f757a5c13"
      },
      "is_pg": false
    },
    {
      "_id": {
        "$oid": "628f35a1cb69fc0789e6917e"
      },
      "course_name": "Bachelor",
      "fees": "Rs.1000.0/-",
      "course_specialization": [
        {
          "spec_name": "Social Work",
          "is_activated": true
        }
      ],
      "college_id": {
        "$oid": "628dfd41ef796e8f757a5c13"
      },
      "is_pg": false
    }
  ],

Student Application forms collection where we are storing student application forms details

[
    {
      "_id": {
        "$oid": "62cd476adbc878a0490e20ee"
      },
      "spec_name1": "Social Work",
      "spec_name2": "",
      "spec_name3": "",
      "student_id": {
        "$oid": "62cd1374dbc878a0490e20a5"
      },
      "course_id": {
        "$oid": "62aab6669b3740313d881a30"
      },
      "current_stage": 2.5,
      "declaration": true,
      "payment_info": {
        "payment_id": "123458",
        "status": "paid"
      },
      "enquiry_date": {
        "$date": {
          "$numberLong": "1657620330432"
        }
      },
      "last_updated_time": {
        "$date": {
          "$numberLong": "1657621796062"
        }
      }
    },
    {
      "_id": {
        "$oid": "62cd476adbc878a0490e20ef"
      },
      "spec_name1": "",
      "spec_name2": "",
      "spec_name3": "",
      "student_id": {
        "$oid": "62cd1374dbc878a0490e20a5"
      },
      "course_id": {
        "$oid": "62aab6669b3740313d881a38"
      },
      "current_stage": 2.5,
      "declaration": true,
      "payment_info": {
        "payment_id": "123458",
        "status": "paid"
      },
      "enquiry_date": {
        "$date": {
          "$numberLong": "1657620330432"
        }
      },
      "last_updated_time": {
        "$date": {
          "$numberLong": "1657621796062"
        }
      }
    },
    {
      "_id": {
        "$oid": "62cdc12000b820f5ea58cc60"
      },
      "spec_name1": "Social Work",
      "spec_name2": "",
      "spec_name3": "",
      "student_id": {
        "$oid": "62cdad90a9b64d58b15e6976"
      },
      "course_id": {
        "$oid": "628f35a1cb69fc0789e6917e"
      },
      "current_stage": 6.25,
      "declaration": false,
      "payment_info": {
        "payment_id": "",
        "status": ""
      },
      "enquiry_date": {
        "$date": {
          "$numberLong": "1657651488511"
        }
      },
      "last_updated_time": {
        "$date": {
          "$numberLong": "1657651987155"
        }
      }
    }
  ]

Desired output with every specification within the course


[
    "_id": {
      "coursename": "Master",
      "spec": "Social Work",
      "Application_Count": 1,
      "Paid_Application_Count:0
    },
  {
    "_id": {
      "coursename": "Bachelor"
      "spec":"" ,
      "Application_Count": 1,
      "Paid_Application_Count:0
    },
      {
    "_id": {
      "coursename": "BBA"
      "spec":"" ,
      "Application_Count": 1,
      "Paid_Application_Count:1
    },
]

Aggregation Query

[{
 $match: {
  college_id: ObjectId('628dfd41ef796e8f757a5c13')
 }
}, {
 $project: {
  _id: 1,
  course_name: 1,
  course_specialization: 1
 }
}, {
 $unwind: {
  path: '$course_name',
  includeArrayIndex: 'course_index',
  preserveNullAndEmptyArrays: true
 }
}, {
 $unwind: {
  path: '$course_specialization',
  includeArrayIndex: 'course_specs_index',
  preserveNullAndEmptyArrays: true
 }
}, {
 $lookup: {
  from: 'studentApplicationForms',
  'let': {
   id: '$_id',
   spec: '$course_specialization.spec_name'
  },
  pipeline: [
   {
    $match: {
     $expr: {
      $and: [
       {
        $eq: [
         '$course_id',
         '$$id'
        ]
       },
       {
        $eq: [
         '$spec_name1',
         '$$spec'
        ]
       }
      ]
     }
    }
   },
   {
    $project: {
     student_id: 1,
     payment_info: 1,
     spec_name1: 1,
     spec_name2: 1,
     spec_name3: 1
    }
   }
  ],
  as: 'student_application'
 }
}, {
 $unwind: {
  path: '$student_application',
  includeArrayIndex: 'application',
  preserveNullAndEmptyArrays: true
 }
}, {
 $facet: {
  course: [
   {
    $group: {
     _id: {
      course_name: '$course_name',
      spec: '$course_specialization'
     },
     count: {
      $count: {}
     }
    }
   }
  ],
  declatration: [
   {
    $group: {
     _id: {
      course_name: '$course_name',
      spec: '$course_specialization'
     },
     count_dec: {
      $sum: {
       $cond: [
        '$student_application.declaration',
        1,
        0
       ]
      }
     }
    }
   }
  ],
  payment: [
   {
    $group: {
     _id: {
      course_name: '$course_name',
      spec: '$course_specialization'
     },
     payment: {
      $sum: {
       $eq: [
        '$student_application.payment_info.status',
        'paid'
       ]
      }
     }
    }
   }
  ]
 }
}]

Problem :

  1. I am able to get application count but it is not getting unique value if 2 specs are same then duplicate value is coming as you can see on sample application collection Social Work is in two different course . So my aggregations is not grouping them based in course name.specs
  2. Not able to find correct Paid_Application_Count and Application_Count

Update :

Updated JSON Data Matching use cases with different type of data

MongoDB Playground

Upvotes: 2

Views: 1161

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22316

You can do it in several different ways, I took the liberty to simplify the pipeline a little bit.

I will just mention that the structure does not fully make sense to me, and there are some additional contradictions between the sample input you provided and the "text" description/pipeline description. Just a tiny example is payment_info_status being paid in the sample and capture in the pipeline.

These things will not change the pipeline structure, will just need to be fixed by you based on the actual needs.

db.courses.aggregate([
  {
    $project: {
      _id: 1,
      course_name: 1,
      course_specialization: 1
    }
  },
  {
    $unwind: {
      path: "$course_specialization",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $lookup: {
      from: "studentApplicationForms",
      "let": {
        courseId: "$_id",
        spec: {
          $ifNull: [
            "$course_specialization.spec_name",
            ""
          ]
        }
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$spec_name1",
                    "$$spec"
                  ]
                },
                {
                  $eq: [
                    "$$courseId",
                    "$course_id"
                  ]
                }
              ]
            }
          }
        },
        {
          $project: {
            student_id: 1,
            payment_info: 1,
            spec_name1: 1,
            spec_name2: 1,
            spec_name3: 1,
            declaration: 1,
            
          }
        },
        {
          $group: {
            _id: null,
            count: {
              $sum: 1
            },
            declatration: {
              $sum: {
                $cond: [
                  "$declaration",
                  1,
                  0
                ]
              }
            },
            paid: {
              $sum: {
                $cond: [
                  {
                    $eq: [
                      "$payment_info.status",
                      "paid"
                    ]
                  },
                  1,
                  0
                ]
              }
            },
            
          }
        }
      ],
      as: "student_application"
    }
  },
  {
    $project: {
      _id: {
        coursename: "$course_name",
        spec: "$course_specialization.spec_name",
        Application_count: {
          $ifNull: [
            {
              $first: "$student_application.count"
            },
            0
          ]
        },
        Declaration_count: {
          $ifNull: [
            {
              $first: "$student_application.declatration"
            },
            0
          ]
        },
        Paid_Application_Count: {
          $ifNull: [
            {
              $first: "$student_application.paid"
            },
            0
          ]
        },
        
      }
    }
  }
])

Mongo Playground

Upvotes: 2

Related Questions