Regex in lookup through addFields result not working

Working with Data
,
1

I have groups and students collections.

Groups: {_id : ObjectId, title: String}
Students{mainGroups: String}

mainGroups is concat string, that all groups, for every student has taken in school.

My code is giving me, all students collection for all groups.

I want to aggregate groups collection with students. And get students collection in one array, for every groups if how many students it has.

[
  //mainGroups is String that`s why I convert Group`s $_id to String here
  {$addFields: {
    gid: {$toString:"$_id"}  
  }},
  {$project: {
     _id: 1,
     gid: 1,
     title:1
  }},
  { 
   $lookup: {
    "from": 'students',
    "let": {"groupId": "$gid"},
    pipeline: [
      {"$match": 
        {"$expr" :  
          {"mainGroups":{"$regex": "$$groupId", "$options" :"i"}}
        }
      }
    ],
    as: "student"
   }
  },
]

How can I get how many students have for every groups ?

2

It would be helpful if you could share sample documents from both collections. This way we could experiment without having to multiply effort to create our own documents from your description.

By doing that, you deprive yourself from the power of arrays, including indexing of individual elements. You make any use case slow since you need to use regex for any match. You make any use case slow since you have to $toString to convert an _id that you could store and lookup without any conversion. A concatenation of the string representation will take a lot more memory compared to an array of native $oid. Depending on your concat some non-existing group might become valid.

group_101 = "101"
group_201 = "201"
groups_concat = group_101 + group_201 => "101201"
// In groups_concat, the group 120 is then now $regex findable even if not a valid group
groups_array = [ group_101 , group_201 ]
// In groups_array, no confusion is possible
1 Like