How to lookup in array using pipeline variables?

[Laurence_Summers]

Hoping there’s some MongoDB experts who know the answer to this

I have a collection swaps which contains documents that I want to .find() all docs in which meet a match criteria, and then add a matches field which is an array of all the other swaps which ‘match’ with it.

Two example docs:

[{
    _id: ObjectId("507f1f77bcf86cd799439011"),
    from: ISODate("2023-01-21T06:30:00.000Z"),
    to: ISODate("2023-01-21T18:30:00.000Z"),
    matchStatus: 0,
    matchId: null,
    userId: ObjectId("ddbb8f3c59cf13467cbd6a532"),
    organisationId: ObjectId("246afaf417be1cfdcf55792be"),
    location: "Chertsey",
    type: "DCA",
    offers: [{
        from: ISODate("2023-01-23T05:00:00.000Z"),
        to: ISODate("2023-01-24T07:00:00.000Z"),
        locations: ["Chertsey", "Walton"],
        types: ["DCA", "SRV"],
    }]
}, {
    _id: ObjectId("21575faf348660e8960c0d931"),
    from: ISODate("2023-01-23T06:30:00.000Z"),
    to: ISODate("2023-01-23T18:30:00.000Z"),
    matchStatus: 0,
    matchId: null,
    userId: ObjectId("d6f10351dd8cf3462e3867f56"),
    organisationId: ObjectId("246afaf417be1cfdcf55792be"),
    location: "Chertsey",
    type: "DCA",
    offers: [{
        from: ISODate("2023-01-21T05:00:00.000Z"),
        to: ISODate("2023-01-21T07:00:00.000Z"),
        locations: ["Chertsey", "Walton"],
        types: ["DCA", "SRV"],
    }]
}]

These two documents should ‘match’ because doc two has at least one element in the offers array which has

• matchStatus === 0
• matchId === null
• organisationId === organisationId
• from <= doc1.from
• to => doc1.to
• locations contains doc1.location
• types contains doc1.type

Any matching documents should be added to the matches field for doc one:

//example result

{
    _id: ObjectId("507f1f77bcf86cd799439011"),
    from: ISODate("2023-01-21T06:30:00.000Z"),
    to: ISODate("2023-01-21T18:30:00.000Z"),
    matchStatus: 0,
    matchId: null,
    userId: ObjectId("ddbb8f3c59cf13467cbd6a532"),
    organisationId: ObjectId("246afaf417be1cfdcf55792be"),
    location: "Chertsey",
    type: "DCA",
    offers: [{
        from: ISODate("2023-01-23T05:00:00.000Z"),
        to: ISODate("2023-01-24T07:00:00.000Z"),
        locations: ["Chertsey", "Walton"],
        types: ["DCA", "SRV"],
    }],
    matches: [{
        _id: ObjectId("21575faf348660e8960c0d931"),
        from: ISODate("2023-01-23T06:30:00.000Z"),
        to: ISODate("2023-01-23T18:30:00.000Z"),
        matchStatus: 0,
        matchId: null,
        userId: ObjectId("d6f10351dd8cf3462e3867f56"),
        organisationId: ObjectId("246afaf417be1cfdcf55792be"),
        location: "Chertsey",
        type: "DCA",
        offers: [{
            from: ISODate("2023-01-21T05:00:00.000Z"),
            to: ISODate("2023-01-21T07:00:00.000Z"),
            locations: ["Chertsey", "Walton"],
            types: ["DCA", "SRV"],
        }]
    }]
}

Note doc two has been inserted into doc one’s matches array field

This is what I currently have which is based on other working queries (just finding matches, not finding all swaps which match a criteria and then adding all its matches).

1. I know I can’t use $elemMatch in a pipeline
2. The offers.map() bit is a bit of a hack anyway but won’t work here as offers isn’t an argument

module.exports.getFilteredSwaps = async ({
        userId,
        organisationId,
        status: statusArray = [],
        page = 1,
    }) =>
    SwapModel.aggregate([{
            $match: {
                organisationId: mongoose.Types.ObjectId(organisationId),
                userId: mongoose.Types.ObjectId(userId),
                matchStatus: {
                    $in: statusArray
                },
            },
        }, {
            $lookup: {
                from: "swaps",
                pipeline: [{
                    $match: {
                        organisationId: mongoose.Types.ObjectId(organisationId),
                        matchId: null,
                        matchStatus: 0,
                        //find docs in 'swaps' where the foreign doc.offers array contains a match for the parent's from/to/locations/types
                        offers: {
                            $elemMatch: {
                                from: {
                                    $lte: new Date("$from")
                                },
                                to: {
                                    $gte: new Date("$to")
                                },
                                locations: {
                                    $elemMatch: {
                                        $eq: "$location"
                                    }
                                },
                                types: {
                                    $elemMatch: {
                                        $eq: "$type"
                                    }
                                },
                            },
                        },
                        //find docs in 'swaps' where the foreign doc.from/to/location/type is found in any of the parent's offers array
                        $or: offers.map((x) => ({
                            from: {
                                $gte: new Date(x.from)
                            },
                            to: {
                                $lte: new Date(x.to)
                            },
                            location: {
                                $in: x.locations
                            },
                            type: {
                                $in: x.types
                            },
                        })),
                    },
                }],
                as: "matches"
            }
        },
        {
            $sort: {
                from: 1,
                to: 1
            }
        },
        {
            $skip: 12 * (page - 1)
        },
        {
            $limit: 12
        },
    ]);

How can I solve this? (p.s. some of this is from memory so there may be a typo or two!)