I have three collections: DocumentationNumbers, AttributeValues, and AttributeValueLinks.

The relevant schemas are defined in the following way:

DocumentationNumbers {

    _id: any;
    value: string; 
    isActive: boolean; 
    projectId: ObjectId;
}

AttributeValueLinks {

   _id: ObjectId;
   attributeValueId: ObjectId; //foreignKey of an AttributeValues
   documentationNumberId: ObjectId; //foreignKey of DocumentationNumbers
} 


AttributeValues {

   _id: ObjectId;
   label: string;
} 

Furthermore, have an array full of Ids of AttributeValues:

const associatedAttributeValueIds: ObjectId[] = [x,y,z] //where x,y,z are Ids from AttributeValue

My goal is now to query all DocumentationNumbers that are linked with all Ids in the array associatedAttributeValueIds via the collection AttributeValueLinks.

For example:

associatedAttributeValueIds: ObjectId[] = [x,y,z]

And I have the following docs in the collection "AttributeValueLinks"

 {

       "_id"= ....,
       "attributeValueId" = x,
       documentationNumberId = 1

    },{

       "_id" = ....,
       "attributeValueId" = y,
       documentationNumberId = 1

    }, {

       "_id" = ....,
       "attributeValueId" = z,
       documentationNumberId: = 1

    }, {

       "_id" = ....,
       "attributeValueId" = z,
       documentationNumberId: = 0
    } 

then I want the following DocumentationNumber:

{
_id: 1,
...
}

I've tried the following query:

db.collection('DocumentationNumbers').aggregate([
                        {
                         $match: {
                                projectId: projectId,
                                isActive: true
                            },
    
                         $lookup:
                                {
                                    from: 'AttributeValueLinks',
                                    localField: '_id',
                                    pipeline: [
                                        {
                                            $match: {
                                                attributeValueId: {"$in": associatedAttributeValues},
                                                $project: {
                                                    _id: 1,
                                                },
                                                isActive: true
                                            }
                                        }
                                    ],
                                    foreignField: 'docNumId',
                                    as: 'attributeValueLinks'
                                }
                        },
                        {
                        $match: { // this stage is the bottleneck
                                "$attributeValueLinks": {
                                   $all: {
                                        _id: {
                                            $in: associatedAttributeValues
                                        }
                                }
                        },
                    },
    
                        }
                        ])

Unfortunately, the second aggregation goes terribly wrong, and I simply can't figure out how to get the desired result.

Any help highly appreciated.

CodePudding user response：

After wasting hours on this problem, these two code snippets did the job:

Here we filter out the field:

$addFields: {
                attributeValueIds: {
                    $map: {
                        input: "$attributeValueLinks",
                        in: "$$this.attributeValueId",
                    },
                },
            }

And this aggregation stage checks if two arrays have the same elements.

$match: {
  $expr: { $setEquals: [ "$attributeValueIds", associatedAttributeValues ] }  
}

Might help someone.