Aggregation like search of sub array field

Working with Data
1

HI All,

Someone please help me on this scenario.

I need to filter the data using like search of query. And filter data with some combination of fields.

My use case as below.

From below i need to filter data with

Input:

committeeId: COMM700010
data.studentName: Asc (type search value)

[{
  "_id": {
    "$oid": "6241bac527a9dba194a8a813"
  },
  "committeeId": "COMM700010",
  "title": "Undergraduate Level, Stage 1, Mechanical Engineering",
  "data": [
    {
      "studentName": "Ascot, Caroline",
      "studentId": "A00012345",
      "term": "Spring 2022",
      "department": "Mechanical Engineering",
      "programme": "Economics",
      "mode": "Full Time",
      "stage": "First Year",
      "stageAverage": 62.5,
      "weightedAverage": 75.2,
      "calculation": "Pass",
      "approvalStatus": "Approved",
      "gradeCommentExist": "No"
    },
    {
      "studentName": "Ashton, Carl",
      "studentId": "A00012346",
      "term": "Spring 2022",
      "department": "Mechanical Engineering",
      "programme": "Economics",
      "mode": "Full Time",
      "stage": "First Year",
      "stageAverage": 75.2,
      "weightedAverage": 63.5,
      "calculation": "Pass",
      "approvalStatus": "Approved",
      "gradeCommentExist": "No"
    }
]
},{
  "_id": {
    "$oid": "6241bac527a9dba194a8a814"
  },
  "committeeId": "COMM700011",
  "title": "Undergraduate Level, Stage 1, Mathematical and Financial Sciences",
  "data": [
    {
      "studentName": "Adele, Rogers",
      "studentId": "A00012346",
      "term": "Spring 2022",
      "department": "Engineering & Technology Innovation Management",
      "programme": "Economics",
      "mode": "Full Time",
      "stage": "First Year",
      "stageAverage": 75.2,
      "weightedAverage": 63.5,
      "calculation": "Pass",
      "approvalStatus": "Approved",
      "gradeCommentExist": "No"
    },
    {
      "studentName": "Jeremy, Campbell",
      "studentId": "A00012348",
      "term": "Spring 2022",
      "department": "Engineering & Technology Innovation Management",
      "programme": "Economics",
      "mode": "Full Time",
      "stage": "First Year",
      "stageAverage": 55.4,
      "weightedAverage": 58.2,
      "calculation": "Borderline",
      "approvalStatus": "Pending",
      "gradeCommentExist": "Yes"
    }]
}]

Output Expected:

[{
  "_id": {
    "$oid": "6241bac527a9dba194a8a813"
  },
  "committeeId": "COMM700010",
  "title": "Undergraduate Level, Stage 1, Mechanical Engineering",
  "data": [
    {
      "studentName": "Ascot, Caroline",
      "studentId": "A00012345",
      "term": "Spring 2022",
      "department": "Mechanical Engineering",
      "programme": "Economics",
      "mode": "Full Time",
      "stage": "First Year",
      "stageAverage": 62.5,
      "weightedAverage": 75.2,
      "calculation": "Pass",
      "approvalStatus": "Approved",
      "gradeCommentExist": "No"
    }
]
}]

Input:

committeeId: COMM700010
data.studentId: A00012349 (type search value)

Output Expected:

[{
  "_id": {
    "$oid": "6241bac527a9dba194a8a813"
  },
  "committeeId": "COMM700010",
  "title": "Undergraduate Level, Stage 1, Mechanical Engineering",
  "data": [
    {
      "studentName": "Olivia, Carter",
      "studentId": "A00012349",
      "term": "Spring 2022",
      "department": "Engineering & Technology Innovation Management",
      "programme": "Economics",
      "mode": "Full Time",
      "stage": "First Year",
      "stageAverage": 65.4,
      "weightedAverage": 68.2,
      "calculation": "Pass",
      "approvalStatus": "Approved",
      "gradeCommentExist": "Yes"
    }
  ]
}]

Multiple field values concatenate and need to filter the combination of concatenated values

Input:

committeeId: COMM700012
data.mode: Full time
data.stage: First Year
data.programme: Science
data.calculation: Pass

Output Expected:

[{
  "_id": {
    "$oid": "6241bac527a9dba194a8a813"
  },
  "committeeId": "COMM700012",
  "title": "Undergraduate Level, Stage 1, Mechanical Engineering",
  "data": [
    {
      "studentName": "Sharpe, Stephan",
      "studentId": "A00012360",
      "term": "Spring 2022",
      "department": "Mehanical Engineering",
      "programme": "Science",
      "mode": "Full Time",
      "stage": "First Year",
      "stageAverage": 75.4,
      "weightedAverage": 68.2,
      "calculation": "Pass",
      "approvalStatus": "Approved",
      "gradeCommentExist": "No"
    }
  ]
}]

Thanks,
Gnanavel N

2

Please read Formatting code and log snippets in posts and repost your documents. We cannot cut-n-paste them into our system to experiment.

Updated documents have been posted thanks.

1 Like
3

To select the documents it is $match like

match = { $match :
  { "committeeId" : "COMM700011" ,
    "data.studentId" : "A00012346"
  }
}

And then to filter the array to just keep the matching student you use $filter inside $set like:

filter = {
  '$set': {
    data: {
      '$filter': {
        input: '$data',
        as: 'item',
        cond: { '$eq': [ '$$item.studentId', 'A00012346' ] }
      }
    }
  }
}

So the aggregation will be

db.collection.aggregate( [ match , filter ] )
1 Like