This is to list out all of the ProductCategories in relation to a ProductStore. However, the problem is that every category can have sub categories that have sub categories meaning that recursion comes into play.
What I would like to do is have a Data Document that would display a ProductCategory's children indefinitely. For example a DataDocument that would look like this:
For what I think you’re describing you’d want ProductCategory to be the primary entity and go the other way to get to the store. The reason for that is to search for a ProductCategory you would want one document in Open/ElasticSearch for each ProductCategory, rather than a single document per product that would have a full tree of categories related to the ProductStore (which for some stores could be a bit large, especially to manually search through in memory on the app server).
With one DataDocument instance per ProductCategory, the main thing you need is a productStoreId field (or list of productStoreId fields that the category is related to through one or more categories).
As you mentioned there is a problem with this sort of thing when arbitrary tree depth (or actually graph width, ProductCategoryRollup has the parent/child notion but because it is a many-to-many relationship via this join entity a child can have an arbitrary number of parents, there can be loops, etc).
The main way I can think of for to handle this would be to write a service that does multiple queries and iteratively or recursively looks for category paths to product stores to get a List/Array of productStoreId. This service would be configured on the data document to augment the document data. The main OOTB example of this is in the MantleWikiPage DataDocument with:
Tangential to the topic but is this better suited for a database query rather than data docs? It would not change the fact that you need to handle multiple parent / child relationships so maybe it doesn’t matter, a database query would just be changing the source of the data. You just have to know how to retrieve and manipulate data from data docs.