Join MongoDB subdocuments in same parent document

Question

Given this Orders collection:

// Order documents
[
  {
    _id: "order_123",
    items: [
      { _id: "item_123", type: "T-Shirt" },
      { _id: "item_234", type: "Hoodie" },
      { _id: "item_345", type: "Hat" },
    ],
    refunds: [
      {
        _id: "refund_123",
        items: ["item_123", "item_234"],
      },
      {
        _id: "refund_234",
        items: ["item_345"],
      },
    ],
  },
]

Is it possible to map refunds.items -> items._id, allowing us to filter by type?

This is how we currently get the refund sub-documents:

db.orders.aggregate([
  {
    $replaceRoot: {
      newRoot: {
        order: "$$ROOT",
        refunds: "$$ROOT.refunds",
      },
    },
  },
  {
    $unwind: "$refunds",
  },
  {
    $project: {
      order: "$order",
      refund: "$refunds",
    },
  },
]);

Which gives us:

// Refund documents
[
  {
    refund: {
      _id: "refund_123",
      items: ["item_123", "item_234"],
    },
    order: { ... }, // The original order document
  },
  {
    refund: {
      _id: "refund_234",
      items: ["item_345"],
    },
    order: { ... }, // The original order document
  },
]

From here, we want to map up refund.items -> order.items._id to produce the following output:

[
  {
    _id: "refund_123",
    items: [
      { _id: "item_123", type: "T-Shirt" },
      { _id: "item_234", type: "Hoodie" },
    ],
  },
  {
    _id: "refund_234",
    items: [
      { _id: "item_345", type: "Hat" }
    ],
  },
]

Allowing us to filter refund documents by type.

Answer 1

You can do this using $unwind and $filter,

• $unwind deconstruct array refunds
• $project to show refund id in _id, and filter items that are in refunds.items array using $filter

db.orders.aggregate([
  { $unwind: "$refunds" },
  {
    $project: {
      _id: "$refunds._id",
      items: {
        $filter: {
          input: "$items",
          cond: { $in: ["$$this._id", "$refunds.items"] }
        }
      }
    }
  }
])

Playground