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:

(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"}
(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" }]}
(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?

Can you edit this question with sample document / inputs & required o/p.. — whoami - fakeFaceTrueSoul, Jun 17 '20 at 19:06
@whoami - edited the description with a sample collection added, was this what you had in mind? — Lukas, Jun 18 '20 at 09:02
Are you passing in same `id` value either `a` or `a-c` or `a-c-y` to all the fields (`link`, `items.link`, `items.series.link`) in match/filter stage ? or is that different `id` different based on field ? I can see same !! So if you've `id = a-c-y` then you'll never be able to get `main doc + items array + series array with a-c-y object` cause first we need to filter `items` array then `series` array (when we filter items array then no object will match with `a-c-y` leaves empty `items` array and thereafter no series array to operate on) — whoami - fakeFaceTrueSoul, Jun 18 '20 at 18:05
Got to the point where the series array is shown for both - the items, and series. Guess the rest of the work will be done via js for now. — Lukas, Jun 21 '20 at 09:45
@whoami - Thank you for the support :) The result seems good enough for now. — Lukas, Jun 21 '20 at 10:09

Lukas · Accepted Answer · 2020-06-21T10:14:44.107

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.

MongoDB - findOne with nested subdocuments and projection

1 Answers1