How can I use MongoDB aggregation $lookup and $addFields to compare fields and return a value if the-CodePudding

I have 2 MongoDB collections, here are 2 model examples:

Users collection:

{
  _id: ObjectId,
  available: Boolean,
  province: String,
  city: String,
  [...]
}

Distances collection:

{
  _id: ObjectId,
  start_province: String,
  start_city: String,
  end_province: String,
  end_city: String,
  distance: Number
}

What I need is to find Users using a query like {available: true}, then use some aggregations to add to each User Document a distance field that will contain a value if there is a match between province and city from Users and end_province / end_city from Distances (start_province and start_city are fixed values defined in JS before this stack).

If there's a match distance should contain the distance value, otherwise 0 (or undefined).

I think I should use $lookup and/or $addFields, but I'm not yet confident with aggregation operators enough to solve this.

Users Results Example:

[
  {
    _id: ObjectId,
    available: Boolean,
    province: String,
    city: String,
    [...],
    distance: Number
  },
  {...}
]

Thanks for your help!

EDIT: Adding Example Data as requested:

Users
[
  {name: 'John', available: true, province: 'Rome', city: 'Tivoli'},
  {name: 'Difool', available: true, province: 'Rome', city: 'Ostia'},
  {name: 'Paul', available: true, province: 'Rome', city: 'Rome'},
  {name: 'Andrew', available: false, province: 'Rome', city: 'Grottaferrata'}
]

Distances
[
  {start_province: 'Rome', start_city: 'Rome', end_province: 'Rome', end_city: 'Ostia', distance: 5},
  {start_province: 'Rome', start_city: 'Rome', end_province: 'Rome', end_city: 'Tivoli', distance: 8}
]

Expected results from Users

[
  {name: 'John', available: true, province: 'Rome', city: 'Tivoli', distance: 8},
  {name: 'Difool', available: true, province: 'Rome', city: 'Ostia', distance: 5},
  {name: 'Paul', available: true, province: 'Rome', city: 'Rome', distance: 0}
]

Filter query: { available: true }

User.province needs to match with Distance.end_province

User.city needs to match with Distance.end_city.

Otherwise, distance should return 0 or undefined. Thanks.

CodePudding user response：

Query

filter to keep the availiable
join if
- User.province needs to match with Distance.end_province
- User.city needs to match with Distance.end_city.
if not joined => distance=0 else get the distance from the first joined document
unset the distances(the join result)

*if you have mongodb <5 and indexes if you can make the second match with the $eq query operator, not the aggregation $eq that i used (its the same just remove the $expr and use the query $eq)

Test code here

db.users.aggregate([
  {
    "$match": {
      "available": {
        "$eq": true
      }
    }
  },
  {
    "$lookup": {
      "from": "distances",
      "let": {
        "uprovince": "$province",
        "ucity": "$city"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$and": [
                {
                  "$eq": [
                    "$$uprovince",
                    "$end_province"
                  ]
                },
                {
                  "$eq": [
                    "$$ucity",
                    "$end_city"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "distances"
    }
  },
  {
    "$set": {
      "distance": {
        "$cond": [
          {
            "$eq": [
              "$distances",
              []
            ]
          },
          0,
          {
            "$arrayElemAt": [
              "$distances.distance",
              0
            ]
          }
        ]
      }
    }
  },
  {
    "$unset": [
      "distances"
    ]
  }
])