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("id1"),
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("id2"),
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!
Edit: expected output is simply an array of matching documents, so passing document id1 to the function would return an array with id2 in, because each document is compatible with the other
///expected output, from and to are between an offer in id1's from and to, similarly types/locations are compatible
{
_id: ObjectId("id2"),
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"],
}]
Based on the accepted answer I was able to create a self-lookup, the missing componenet was the reciprocal match using $anyElementTrue to search the offers field
I suspect there is a more optimised approach
Mongo playground working example
db.swaps.aggregate([
{
$match: {},
},
{
$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,
},
},
]);
You can perform self-lookup with your criteria set in the sub-pipeline.
db.collection.aggregate([
{
$match: {
organisationId: "organisationId1",
matchId: null,
matchStatus: 0
}
},
{
$unwind: "$offers"
},
{
"$lookup": {
"from": "collection",
"let": {
offersFrom: "$offers.from",
offersTo: "$offers.to",
offersLocation: "$offers.locations",
offersType: "$offers.types"
},
"pipeline": [
{
$match: {
$expr: {
$and: [
{
$gte: [
"$from",
"$$offersFrom"
]
},
{
$lte: [
"$to",
"$$offersTo"
]
},
{
"$in": [
"$location",
"$$offersLocation"
]
},
{
"$in": [
"$type",
"$$offersType"
]
},
]
}
}
}
],
"as": "selfLookup"
}
},
{
"$unwind": "$selfLookup"
},
{
"$replaceRoot": {
"newRoot": "$selfLookup"
}
}
])