MongoDB join two collection

Question

This is the order entity:

public class Order 
{
    @Id
    private String id;
    private String internalId;
    private String externalId;
    private String status;
}

public class OrderAssigned 
{
    @Id
    private String id;
    private String internalId;
    private String externalId;
    private String stationId;
    private String rejectStatus;
}

I want to join these two collections.

• First I want to find stationId
• then join Orders.internalId and OrderAssigned.internalId and status = "OrderCreated"

Like this SQL query:

select orders.* 
from PARTNER_RESTAURANT_ORDER orders 
inner join PARTNER_RESTAURANT_ORDER_ASSIGNED_STATION assigned_station on orders.internalId = assigned_station.internalId 
where assigned_station.stationId = "5e2968e2763e750001c8ba5f" 
  and orders.status = "OrderCreated"

Answer 1

Well MongoDB is not the best for joins but it is possible via $lookup.

Assume you have following set of data:

ORDERS:
{
    _id: 'id-1',
    internalId: 'internal-id-1',
    externalId: 'external-id-1',
    status: 'OrderCreated'
}
{
    _id: 'id-2',
    internalId: 'internal-id-2',
    externalId: 'external-id-2',
    status: 'INACTIVE'
)

ORDERS ASSIGNED:
{
    _id: 'id-1',
    internalId: 'internal-id-1',
    externalId: 'external-id-1',
    stationId: 'station-id-1',
    rejectStatus: 'NONE'
}
{
    _id: 'id-2',
    internalId: 'internal-id-2',
    externalId: 'external-id-2',
    stationId: 'station-id-2',
    rejectStatus: 'NONE'
}

Then:

db.orderAssigned.aggregate([
 {$match: {stationId: {$in: ['station-id-1', 'station-id-2']}}},
 {$lookup: {from: 'order', localField: 'internalId', foreignField: 'internalId', as: 'orders'}}
])

will give you:

{
    "_id" : "id-1",
    "internalId" : "internal-id-1",
    "externalId" : "external-id-1",
    "stationId" : "station-id-1",
    "rejectStatus" : "NONE",
    "orders" : [
        {
            "_id" : "id-1",
            "internalId" : "internal-id-1",
            "externalId" : "external-id-1",
            "status" : "OrderCreated"
        }
    ]
}
{
    "_id" : "id-2",
    "internalId" : "internal-id-2",
    "externalId" : "external-id-2",
    "stationId" : "station-id-2",
    "rejectStatus" : "NONE",
    "orders" : [
        {
            "_id" : "id-2",
            "internalId" : "internal-id-2",
            "externalId" : "external-id-2",
            "status" : "INACTIVE"
        }
    ]
}

But if you need some extra join conditions like status = "OrderCreated" then more appropriate for you is 2nd version of $lookup with pipeline:

db.orderAssigned.aggregate([
 {$match: {stationId: {$in: ['station-id-1', 'station-id-2']}}},
 {$lookup: 
    {
        from: 'order', 
        let: {joinValue: '$internalId'}, 
        pipeline: [
            { $match:
                 { $expr:
                    { $and:
                       [
                         { $eq: [ "$internalId",  "$$joinValue" ] },
                         { $eq: [ "$status", "OrderCreated" ] }
                       ]
                    }
                 }
                  }     
        ], 
        as: 'orders'}}
])

will give you:

{
    "_id" : "id-1",
    "internalId" : "internal-id-1",
    "externalId" : "external-id-1",
    "stationId" : "station-id-1",
    "rejectStatus" : "NONE",
    "orders" : [
        {
            "_id" : "id-1",
            "internalId" : "internal-id-1",
            "externalId" : "external-id-1",
            "status" : "OrderCreated"
        }
    ]
}
{
    "_id" : "id-2",
    "internalId" : "internal-id-2",
    "externalId" : "external-id-2",
    "stationId" : "station-id-2",
    "rejectStatus" : "NONE",
    "orders" : [ ]
}