MongoDB aggregate and then flatten

Question

FULL DISCLOSURE: I'm a MongoDB noob

I'm dealing with a legacy DB structure. A part of my MongoDB looks like this currently:

• Events (_id, name (string), ...)
• Orders (_id, eventId (as string), products (array of {prodIdentifier (string), quantity (number)}), customer_ID (string), signee (string), sign_time (date), ...)
• Products (_id, prodIdentifier (string), price (number), sku (string), ...)

The relations are as follows:

• Event 1..N Orders (via eventId)
• Orders 1..N Products (via products array)

I need to query in a way that given an eventId, I return

Order ID
Customer Name (can be a cascade request / premeditated by frontend),
Product SKU,
Product Name,
Quantity,
Value (quantity * price),
Signee Name,
Sign time

Mind that, my interface requires filters and sorts on all of the above fields along with limit and offset for pagination, to reduce query time, fast UI, etc.

I could use populate on orders, but how am I supposed to honor the limit and offset via mongoose then. I'm wondering if I should make a view, in which case how should I flatten it to send/receive a list that honors the limit and offset.

Or will it have to be a very manual, step-by-step build of the resulting list?

UPDATE:

Sample data in the DB:

Event Object:

{ 
    "_id" : ObjectId("6218b9266487367ba1c20258"), 
    "name" : "XYZ", 
    "createdAt" : ISODate("2022-02-03T13:25:43.814+0000"), 
    "updatedAt" : ISODate("2022-02-14T09:34:47.819+0000"),
    ...
}

Order(s):

[
{ 
    "_id" : ObjectId("613ae653d0112f6b49fdd437"), 
    "orderItems" : [
        {
            "quantity" : NumberInt(2), 
            "productCode" : "VEO001", 
        }, 
        {
            "quantity" : NumberInt(2), 
            "productCode" : "VEO002", 
        }, 
        {
            "quantity" : NumberInt(1), 
            "productCode" : "VEO003", 
        }
    ], 
    "orderCode" : "1000", 
    "customerCode" : "Customer 1", 
    "createdAt" : ISODate("2021-09-10T05:00:03.496+0000"), 
    "updatedAt" : ISODate("2022-02-08T10:06:42.255+0000"), 
    "eventId" : "6218b9266487367ba1c20258"
}
]

Products:

[
{ 
    "_id" : ObjectId("604206685f25b8560a1cd48d"), 
    "Product name" : "ABC", 
    "createdAt" : ISODate("2021-03-05T10:22:32.085+0000"), 
    "tag" : "VEO001", 
    "updatedAt" : ISODate("2022-03-28T07:29:21.939+0000"), 
    "Product Price" : NumberInt(0), 
    "photo" : {
        "_id" : ObjectId("6042071a5f25b8560a1cd4a9"), 
        "key" : "e8c9a085-4e8d-4ac4-84e9-bb0a83a59145", 
        "name" : "Screenshot 2021-03-05 at 11.24.50.png"
    }, 
    "name" : "ABC", 
    "_costprice" : NumberInt(12), 
    "_sku" : "SKUVEO001",
},
{ 
    "_id" : ObjectId("604206685f25b8560a1cd48a"), 
    "Product name" : "DEF", 
    "createdAt" : ISODate("2021-03-05T10:22:32.085+0000"), 
    "tag" : "VEO002", 
    "updatedAt" : ISODate("2022-03-28T07:29:21.939+0000"), 
    "Product Price" : NumberInt(0), 
    "photo" : {
        "_id" : ObjectId("6042071a5f25b8560a1cd4a9"), 
        "key" : "e8c9a085-4e8d-4ac4-84e9-bb0a83a59145", 
        "name" : "Screenshot 2021-03-05 at 11.24.50.png"
    }, 
    "name" : "DEF", 
    "_costprice" : NumberInt(13), 
    "_sku" : "SKUVEO002",
},
{ 
    "_id" : ObjectId("604206685f25b8560a1cd48a"), 
    "Product name" : "GHI", 
    "createdAt" : ISODate("2021-03-05T10:22:32.085+0000"), 
    "tag" : "VEO003", 
    "updatedAt" : ISODate("2022-03-28T07:29:21.939+0000"), 
    "Product Price" : NumberInt(0), 
    "photo" : {
        "_id" : ObjectId("6042071a5f25b8560a1cd4a9"), 
        "key" : "e8c9a085-4e8d-4ac4-84e9-bb0a83a59145", 
        "name" : "Screenshot 2021-03-05 at 11.24.50.png"
    }, 
    "name" : "GHI", 
    "_costprice" : NumberInt(13), 
    "_sku" : "SKUVEO003",
},
]

Expected output:

Answer 1

You can do something like:

db.orders.aggregate([
  {$match: {eventId: "6218b9266487367ba1c20258"}},
  {
    $lookup: {
      from: "products",
      localField: "orderItems.productCode",
      foreignField: "tag",
      as: "orderItemsB"
    }
  },
  {
    "$addFields": {
      "orderItems": {
        "$map": {
          "input": "$orderItemsB",
          "in": {
            "$mergeObjects": [
              "$$this",
              {
                "$arrayElemAt": [
                  "$orderItems",
                  {"$indexOfArray": ["$orderItems.productCode", "$$this.tag"]}
                ]
              }
            ]
          }
        }
      },
      orderItemsB: 0
    }
  },
  {
    $unset: "orderItemsB"
  },
  {
    $lookup: {
      from: "events",
      let: {eventId: "$eventId"},
      pipeline: [
        {
          $match: {$expr: {$eq: [{$toString: "$_id"}, "$$eventId"]}}
        }
      ],
      as: "event"
    }
  },
  {
    $set: {event: {"$arrayElemAt": ["$event", 0]}}   
  },
  {$unwind: "$orderItems"}
])

As you can see on this playground example. This will give you a document for each product of the order with all the data.