Aggregation within nested array

Working with Data
1

Hello everyone, I started using mongodb after a long time, but I could not write the code to perform the following operation.

At first, I tried this code but didn’t get the desired result and couldn’t continue due to the example shown as an empty array.

{$lookup: {
  from: 'suspects',
  localField: 'suspects.id',
  foreignField: '_id',
  as: 'example1'
}}

lawyers document

_id
fullName
phoneNumber

suspects document

_id
fullName
phoneNumber

case document

_id
crimeNumber
eventDate
suspects: [
    {
       id // _id value in the suspects document
       note
       +++ populating data from suspects document

       lawyer: {
          id // _id value in the lawyers document
          note
          +++ populating data from lawyers document
       }
    }
    ...
    ...
]
3

You lookup from suspects but localField seems to refer to a field from suspects. It is the other way around. You will need to share the whole pipeline and real sample documents.

4

My aggregate function works but I have an issue If there are two or more suspects in the case then I have a duplicate result problem due to unwinding suspects. I tried to use $group after unwind suspectslookup but I got this message ‘no documents found’.
mongoplayground.net/p/v3n27aGUVCW

5

There is one thing that you really have to fix in your data before going forward since it will impact performance in very bad way.

Storing reference ids such in your suspects array as string rather than $oid is bad thrice.

1 - The string representation of an $oid takes more space (close to 17 bytes more)
2 - The string representation forces you to call $toObjectId before doing lookup so it is slower.
3 - Reverse $lookup (aggregation on lawyers collection to find its cases) cannot use indexes because it will be done on a computed value.

You do not need to $unwind in order to $lookup. It is probably faster not to $unwind because duplicate values should not be $lookup twice. They are in the $unwind case. But in your case since you have to call $toObjectId, you might not have the choice to $unwind. (You probably could use $map or $reduce to call $toObjectId) So you would not have to call $unwind, not have to call $toObjectId and you could use the simple $lookup form with localField and foreignField rather than $match. The stages to $unset:_id are probably extra work done with no added benefits.

As for your playground, I get:

query failed: (ConversionFailure) PlanExecutor error during aggregation :: caused by :: Unsupported conversion from array to objectId in $convert with no onError value

I modified your playground to integrate the ObjectId changes above, the removal of $unwind, what is left is 2 very simple $lookup. See: https://mongoplayground.net/p/WIHzX7ZzzIs.

The $lookup results are not merged into the original suspects array but it should be easy to do it with a simple $set stage that uses $map. Note that also put the $lookup results in a _tmp object. I always put temporary results in a top level _tmp object. This way a single $unset _tmp removes all my temporary results. This way I never forget a temporary value in my aggregation results.

6

Thank you for your time. I am a newbie to MongoDB pipeline operations. I understood the objectid issue and it can be configured as you said, but in the link you gave, the lawyer must be shown under the suspect to which it belongs, and the suspects will need to be merged with _tmp.suspects because the suspect in the case document can contain a note field and the lawyer in the case document.

7

I know that this is what you want. That is why I wrote

Easy but I did not have the time to do it yet.

8

Here is another playground that goes one step further by merging the suspect details into the suspects array.