MongoDB - findOne with nested subdocuments and projection

Question

I am currently using the code below in node.js to find and return data on various nesting levels from a mongo database. I'd like to add another layer of nesting (as mentioned in #3).

Collection:

[
  {
    "title": "Category A",
    "link": "a",
    "items": [
      {
        "title": "Item C",
        "link": "a-c",
        "series": [
          {
            "title": "Item C X",
            "link": "a-c-x"
          },
          {
            "title": "Item C Y",
            "link": "a-c-y"
          },

        ]
      },
      {
        "title": "Item D",
        "link": "a-d"
      }
    ]
  },
  {
    "title": "Category B",
    "link": "b"
  }
]

The query:

const doc = await ... .findOne(
    {
        $or: [
            { link: id },
            { "items.link": id },
            { "items.series.link": id }
        ],
    },
    {
        projection: {
            _id: 0,
            title: 1,
            link: 1,
            items: { $elemMatch: { link: id } },
        },
    }
);

Intended results:

1. (works) if link of the document is matched,
   (works) there should only be an object with the title and link returned
   e.g.
   value of id variable: "a"
   expected query result: { title: "Category A", link: "a"}

2. (works) if items.link of subdocument is matched,
   (works) it should be the same as above + an additional element in the items array returned.
   e.g.
   value of id variable: "a-c"
   expected query result: { title: "Category A", link: "a", items: [{ title: "Item C", link: "a-c" }]}

3. (works) if items.series.link of sub-subdocument is matched
   (struggling with this) it should return the same as in 2. + an additional element inside the matched items.series
   e.g.
   value of id variable: "a-c-y"
   expected query result: { title: "Category A", link: "a", items: [{ title: "Item C", link: "a-c", series: [{ title: "Item C Y", link: "a-c-y" }]}]}
   current query result: The whole Category A document with all sub-documents

Questions:

a.) How do I modify the projection to return the expected output in #3 as well?

b.) Is the approach above sound in terms of reading speed from a denormalized structure? I figured there'd probably need to be indexes on link, items.link and items.series.link as they are all completely unique in the document, but maybe there is a way to achieve the above goal with a completely different approach?

Answer 1

Ended up with going half-way via mongodb and get the full item for both - when the item link is matched and the series link is matched:

projection: {
  _id: 0,
  title: 1,
  link: 1,
  items: { $elemMatch: { $or: [
    { link: id },
    {"series.link": id }
  ]}},
}

After that javascript filters the series array to see if the series is matched:

doc?.items?.[0]?.series?.find(item => item.link === id)

if the js is truthy (returns an object) we matched a series, if there is a doc, but the js is falsy we matched an item result.

Although not a full mongodb solution and there is definitely room for improvement the above seems to achieve the end goal to be able to distinguish between category, item and series results.