How to $lookup when foreign and local fields are ObjectId?

Working with Data
1

I am working through a small example of my own based on the M121 course.

I have a simple Appointments and Events collection and would like join and return all documents that are associated with a specific provider.

Appointment collection:

{
    _id: ObjectId('62fd3e631d732145ed86adff'),
    imported: false,
    provider: ObjectId('62b62342654ae12263478489'),
    status: "SCHEDULED",
    summary: "Test appointment with client",
    description: "This is a test description for the new booking type",
    location: "4211 N Winfield Scott Plaza, Scottsdale, AZ 85251, USA",
    startTime: 2022-08-17T04:30:00.000+00:00,
    endTime: 2022-08-17T04:45:00.000+00:00,
    createdAt: 2022-08-17T19:15:47.903+00:00,
    updatedAt: 2022-08-17T19:15:47.903+00:00,
    type:"external"
}

Event collection:

[
    {
        _id: ObjectId('62feaa0dca4ba048fa24dd45'),
        summary: "Shampoo with Sandra Rodriguez",
        description: "Please make sure to arrive at least 20 minutes before the...",
        location: "4211 N Winfield Scott Plaza, Scottsdale, AZ 85251, USA",
        creator: ObjectId('62b62342654ae12263478489'),
        startTime: 2022-08-18T17:00:00.000+00:00,
        endTime: 2022-08-18T17:15:00.000+00:00,
        type: "external"
    },
    {
        id: ObjectId('62fe983aca4ba048fa24dd43'),
        summary: "Haircut with Derrick Jones",
        description: "Meet with client 15 minutes after last scheduled appointment",
        location: "4211 N Winfield Scott Plaza, Scottsdale, AZ 85251, USA",
        creator: ObjectId('62b62342654ae12263478489'),
        startTime: 2022-08-18T15:00:00.000+00:00,
        endTime: 2022-08-18T15:30:00.000+00:00,
        type: "external"
    }
]

Below I have provided my aggregation but it’s only returning 1 document from Appointment collection and not 3 documents (1 appointment, and 2 event docs).

Is this the correct way to approach this problem? Thank you for any suggestions.

[{
 $match: {
  provider: ObjectId('62b62342654ae12263478489')
 }
}, {
 $lookup: {
  from: 'event',
  localField: 'provider',
  foreignField: 'creator',
  as: 'Bookings'
 }
}]
2

Your aggregation is correct.

You only shared 1 document from Appointment so I assume you only have one with the matched provider.

The 2 documents from the Event will not be 2 top level documents such as the one from Appointment but 2 sub-documents with the array Bookings from the Appointment document.

The one thing I notice that might be wrong is that you present your 2 collections with an uppercase first letter such as

and

but in your $lookup you refer to Event in lowercase as in

Collection names are case sensitive. The names Event and event do not refer to the same collection.

1 Like
3

@steevej Thanks for pointing out the case. sensitivity, I totally missed that. After fixing, I do see a bookings property on the Appointment collection however it is empty.

Now I realize that maybe the $lookup is not the solution for the problem I’m trying to solve. I simply want to just have the combined results from a simple find() query in both collections.

The reason I want to do this on the database layer instead of inside the application is because I want to properly apply .skip() and .limit() to the resulting list of documents.

Any thoughts on this? Thanks a lot for you time.

4

So as it turns out, what I really needed was to perform a UNION rather than a JOIN.

That being said MongoDB’s $unionWith operation solves my issue.

For any future readers with a situation similar to mine above, check out this link.

1 Like
5

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.