Find an element's highest level ancestor without having bi-directional reference

Question

I'm using MongoDB to store subcategories in categories, and items in the subcategories. I'd like to retrieve the main category by an item. How can I do that in Spring Data the easiest way without having a bi-directional reference?

class Category {    
    private String id;

    //some other attributes

    @DbRef
    List<Category> subCategories = new ArrayList<>();

    @DbRef
    List<Item> items = new ArrayList<>();
}

In the DB the Categories collection looks something like:

{
    id: 1,
    subcategories: [
        2, 3   
    ]
},
{
    id: 2,
    items: [
        001
    ]
}

I'd like to find the category with ID 1 by providing the itemID 001 (which is an item in the Items collection), through the subcategory 2, without assuming the depth of connections.

I'd prefer the lazy way with Spring Data Repository's smart method naming, something like Category findBySubCategoriesItems(Item item) but @Query is also much appreciated!

Edit: I can find the subCategory from MongoDB console by itemId but I don't know how to recursively step up to the root category. This is my query:

db.category.find({ items: { id: ObjectId("someItemId") } })

I tried to go the other way around, getting the top level categories and filtering by the item like this: category.*.items.id : someItemId but unfortunately the wildcard "any depth" query is not supported, as it's stated in https://jira.mongodb.org/browse/SERVER-267

Edit 2: I've been reading about GraphLookup but as far as I understand, it could only find the root category if the parent relation was set, and can't operate with it when only the childs are set.

@Visrozar I've extended the question a little bit to clarify. — OCPi, Feb 07 '19 at 06:32
When you say 'indefinite depth of connections', which collection are you talking about (categories or items) ? Assuming you're talking about categories collection, will the subcategories array for each category be unique (as in, if a category has 2 as the subcategory, can any other category have 2 as subcategory)? — Elvis, Feb 07 '19 at 07:43
@Visrozar yes, I'm talking about the categories, and yes, they are unique, one subcategory has only one parent but this connection is only stored on the parent side, the sub doesn't know about it's parent. — OCPi, Feb 07 '19 at 07:49

score 3 · Accepted Answer · answered Feb 07 '19 at 08:56

Graphlookup is definitely the way to go, Assuming the two collections names are 'items' and 'categories',

  db.items.aggregate([
  {
    // find the item in items collection
    $match: {
      items: '001'
    },

  },
  // recursively find the categories starting from matched item
  {
    $graphLookup: {
      from: "categories",
      startWith: "$id",
      connectFromField: "id",
      connectToField: "subcategories",
      as: "categories",
      depthField: "level"
    }
  },
  // get only the root node (this is optional, basically if you skip the below stage, you'll get the entire recursive category list along with the matched item)
  {
    $project: {
      root_category: {
        $filter: {
          input: "$categories",
          as: "category",
          cond: {
            $eq: [
              "$$category.level",
              {
                $max: "$categories.level"
              }
            ]
          }
        }
      }
    }
  }
])

Find an element's highest level ancestor without having bi-directional reference

1 Answers1