Generating dynamic $or using pipeline variable?

Hi all, hoping someone can help as I am truly stuck!

I have this query

SwapModel.aggregate([
    {
        $match: {
            organisationId: mongoose.Types.ObjectId(organisationId),
            matchId: null,
            matchStatus: 0,
            offers: {
                $elemMatch: {
                    from: { $lte: new Date(from) },
                    to: { $gte: new Date(to) },
                    locations: { $elemMatch: { $eq: location } },
                    types: { $elemMatch: { $eq: type } },
                },
            },
//problem is HERE
            $or: {
                $map: {
                    input: "$offers",
                    as: "offer",
                    in: {
                        from: { $gte: new Date("$$offer.from") },
                        to: { $lte: new Date("$$offer.to") },
                        location: { $in: "$$offer.locations" },
                        type: { $in: "$$offer.types" },
                    },
                },
            },
        },
    },
    { ...swapUserLookup },
    { $unwind: "$matchedUser" },
    { $sort: { from: 1, to: 1 } },
]);

I’m trying to use the results of the $match document to generate an array for $or. My data looks like this:

[{
    _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"],
    }]
}]


I want the $or to match all documents that have the corresponding from/to/location/type as the current document - the idea is two shifts that could be swapped

If the offers are known (passed as an array to the function calling `aggregate`), I can do this with:

$or: offers.map((x) => ({
            from: { $gte: new Date(x.from) },
            to: { $lte: new Date(x.to) },
            location: { $in: x.locations },
            type: { $in: x.types },
        }))

BUT I want to be able to do this in an aggregation pipeline when the offers will only be known from the current document, $offers

Is this possible? I’ve tried $in, $map, $lookup, $filter, $getField but can’t get it right and can’t get anything from Google as it thinks I want $in (which is the opposite of what I need).

I’m pretty new to MongoDB and am probably approaching this completely wrong but I’d really appreciate any help!

It looks like you have some misunderstanding about an aggregation pipeline.

All the stages of a pipeline are executed on the server. Your offers.map((x) …) JS code cannot be executed in between stages (unless you split your pipeline in 2 and do 2 database access).

If you want to find other documents within the same collection based on some documents you will need to do a $lookup. Your $match selects some documents, the the following $lookup will use what it is your in: as the $match stage of the internal lookup. This should look like:

{ $match : { /* your current $match */ } } ,
{ $unwind : "$offers" } ,
{ $lookup : {
    from : /* SwapModel collection */ ,
    let : {
        from : "$$offers.from" ,
        to:  "$$offers.to" ,
        location : "$$offers.locations" ,
        type : "$$offers.types" ,
    }
    pipeline : [
        { $match : { $expr : $and : [
             { $gte : [ "$from" , "$$from" } } ,
             { $lte : [ "$to" , "$$to" } } ,
             { $in : [ "$locations" , "$$location" } } ,
             { $in : [ "$types" , "$$type" } } ,
        ] } }
    ]
} }

I am not too sure about $in above but it is a starting point.

I try to test the pipeline with your sample documents and it looks like all of the ObjectId’s are wrong. They all generate the error:

Argument passed in must be a string of 12 bytes or a string of 24 hex characters

It looks like one hex character was added to each.

So I used the string in order to test and found some issues with my original pipeline.

The let of the $lookup should be replaced by

let : {
        from : "$offers.from" ,
        to:  "$offers.to" ,
        location : "$offers.locations" ,
        type : "$offers.types" ,
    }

There was an extra $ sign to each.

As suspected my $in arewrong in the first draft. They should be $eq since we $unwind

{ $eq : [ "$$location" , "$location ] }
and
{ $eq : [ $$type" , "$type" ] }

Thanks for taking the time to reply Steve!

I do understand the basic principal of pipeline execution, I’m currently using the .map() to build the array used by $ using a passed argument offer or as I don’t know what the approach should be.

This is the current working code:

module.exports.getSwapMatches = async ({
    from,
    to,
    location,
    type,
    offers,
    organisationId,
}) =>
    SwapModel.aggregate([
        {
            $match: {
                organisationId: mongoose.Types.ObjectId(organisationId),
                matchId: null,
                matchStatus: 0,
                offers: {
                    $elemMatch: {
                        from: { $lte: new Date(from) },
                        to: { $gte: new Date(to) },
                        locations: { $elemMatch: { $eq: location } },
                        types: { $elemMatch: { $eq: type } },
                    },
                },
                $or: offers.map((x) => ({
                    from: { $gte: new Date(x.from) },
                    to: { $lte: new Date(x.to) },
                    location: { $in: x.locations },
                    type: { $in: x.types },
                })),
            },
        },
        { ...swapUserLookup },
        { $unwind: "$matchedUser" },
        { $sort: { from: 1, to: 1 } },
    ]);

so as you can imagine the .map just returns an array to $or so it ends up like

$or: [{
        from: {
            $gte: new Date("2023-01-23T06:30:00.000Z")
        },
        to: {
            $lte: new Date("2023-01-23T18:30:00.000Z")
        },
        location: {
            $in: ["Chertsey", "Walton"]
        },
        type: {
            $in: ["DCA", "SRV"]
        },
    },
    ...
]

for every item in the offers argument

What I want to learn is how to do this without passing an offers argument, just matching in the swaps collection and then for each finding the corresponding swaps

How to you obtain or compute the

from your previous post it looks like

Your last question

looks exactly the same as

And

confirms that

And my answer will be the same

and the code will be the same (once the corrections I also posted are done)

your_current_match = {
    organisationId: mongoose.Types.ObjectId(organisationId),
    matchId: null,
    matchStatus: 0,
    offers: { $elemMatch: {
        from: { $lte: new Date(from) },
        to: { $gte: new Date(to) },
        locations: { $elemMatch: { $eq: location } },
        types: { $elemMatch: { $eq: type } },
    } }
}
{ $match : your_current_match } ,
{ $unwind : "$offers" } ,
{ $lookup : {
    from : /* SwapModel collection */ ,
    let : {
        from : "$offers.from" ,
        to:  "$offers.to" ,
        location : "$offers.locations" ,
        type : "$offers.types" ,
    }
    pipeline : [
        { $match : { $expr : $and : [
             { $gte : [ "$from" , "$$from" } } ,
             { $lte : [ "$to" , "$$to" } } ,
             { $in : [ "$$locations" , "$location" } } ,
             { $in : [ "$$type" , "$type" } } ,
        ] } }
    ]
} }

Have you tried the above solution?

If it does not work please provide sample documents from both collections and the expected results. Please make sure your documents are usable. The previous documents you published are not usable. Some of the ObjectId are not correct. I get errors when I cut and paste into mongosh:

mongosh> userId = ObjectId("ddbb8f3c59cf13467cbd6a532")
TypeError: Argument passed in must be a string of 12 bytes or a string of 24 hex characters
mongosh> organisationId = ObjectId("246afaf417be1cfdcf55792be")
TypeError: Argument passed in must be a string of 12 bytes or a string of 24 hex characters

Sorry for my late reply I’ve been on shift

Currently this query is run knowing the offers. A match is done, then for each result the query in my inital answer is run to find the matching swaps. The goal here to to do this in one query.

Here’s an example of a mismatch

The returned document is for 2023-01-15, and the offers array starts on the 2023-01-17. But the returned match is on the 2023-01-14. This means the returned document can’t possibly swap with the matched document, because the from and to of the matched document aren’t in any offers of the returned document.

Does this diagram help explain it at all? I’m not doing a good job of it

This is my last reply in this thread.

Sorry but you keep mentioning

And I have repeated

and I have supplied code to try and wrote

I have asked

Screenshots that shows an image of documents is even less usable than documents with wrong ObjectId.

Despite writing that you need to do $lookup you still

and do multiple accesses to the database.

If your $gte/$lte with dates gives you wrong results then
1 - the order of the parameters are wrong
2 - the operator is wrong
3 - the type of data differs between the parameters

I really cannot help any further.

Thanks for your time, I have been able to get the solution as below, I strongly suspect there is a better way to do this as I’m clearly not very experienced but it seems to be working

working demo

db.swaps.aggregate([
    {
        $match: {/*find some documents*/},
    },
    {
        $unwind: "$offers",
    },
    {
        $lookup: {
            from: "swaps",
            as: "matches",
            let: {
                parentId: "$_id",
                parentOrganisationId: "$organisationId",
                parentUserId: "$userId",
                parentLocations: "$offers.locations",
                parentTypes: "$offers.types",
                parentOffersFrom: "$offers.from",
                parentFrom: "$from",
                parentTo: "$to",
                parentOffersTo: "$offers.to",
                parentLocation: "$location",
                parentType: "$type",
            },
            pipeline: [
                {
                    $match: {
                        matchStatus: 0,
                        matchId: null,
                        $expr: {
                            $and: [
                                {
                                    $ne: ["$_id", "$$parentId"],
                                },
                                {
                                    $ne: ["$userId", "$$parentUserId"],
                                },
                                {
                                    $eq: [
                                        "$organisationId",
                                        "$$parentOrganisationId",
                                    ],
                                },
                                {
                                    $in: ["$location", "$$parentLocations"],
                                },
                                {
                                    $in: ["$type", "$$parentTypes"],
                                },
                                {
                                    $lte: ["$$parentOffersFrom", "$from"],
                                },
                                {
                                    $gte: ["$$parentOffersTo", "$to"],
                                },
                                {
                                    $anyElementTrue: {
                                        $map: {
                                            input: "$offers",
                                            as: "offer",
                                            in: {
                                                $and: [
                                                    {
                                                        $in: [
                                                            "$$parentLocation",
                                                            "$$offer.locations",
                                                        ],
                                                    },
                                                    {
                                                        $in: [
                                                            "$$parentType",
                                                            "$$offer.types",
                                                        ],
                                                    },
                                                    {
                                                        $lte: [
                                                            "$$offer.from",
                                                            "$$parentFrom",
                                                        ],
                                                    },
                                                    {
                                                        $gte: [
                                                            "$$offer.to",
                                                            "$$parentTo",
                                                        ],
                                                    },
                                                ],
                                            },
                                        },
                                    },
                                },
                            ],
                        },
                    },
                },
                {
                    $lookup: {
                        from: "users",
                        localField: "userId",
                        foreignField: "_id",
                        as: "matchedUser",
                    },
                },
                {
                    $set: {
                        matchedUser: {
                            $ifNull: [
                                {
                                    $first: "$matchedUser",
                                },
                                null,
                            ],
                        },
                    },
                },
            ],
        },
    },
    {
        $group: {
            _id: "$_id",
            doc: {
                $first: "$$ROOT",
            },
            matches: {
                $push: "$matches",
            },
            offers: {
                $push: "$offers",
            },
        },
    },
    {
        $set: {
            matches: {
                $reduce: {
                    input: "$matches",
                    initialValue: [],
                    in: {
                        $concatArrays: ["$$value", "$$this"],
                    },
                },
            },
        },
    },
    {
        $replaceRoot: {
            newRoot: {
                $mergeObjects: [
                    "$doc",
                    {
                        matches: "$matches",
                        offers: "$offers",
                    },
                ],
            },
        },
    },
    {
        $lookup: {
            from: "users",
            localField: "userId",
            foreignField: "_id",
            as: "user",
        },
    },
    {
        $set: {
            user: {
                $ifNull: [
                    {
                        $first: "$user",
                    },
                    null,
                ],
            },
        },
    },
    {
        $sort: {
            _id: 1,
        },
    },
]);