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:

CodePudding user response：

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.