Retrieve documents having array containing another document attribute

Question

I am storing some kind of filesystem in Mongo, where directories are named categories.

The category JSON looks like:

{
   "name":"CategoryChildLevel2",
   "parentId":"2",
   "otherAttribute":"anyVal",
   "breadcrumb":[
      {
         "name":"RootCategory",
         "id":"1"
      },
      {
         "name":"CategoryChildLevel1",
         "id":"2"
      }
   ]
}

The FS categories are linked together with the parentId attribute.

I need to display the categories breadcrumbs. Through user navigation, we can know where we are on the FS, but categories can be accessed directly by their ID (bookmarked category, search engine...), without any FS navigation. To avoid recursive calls to the DB, to be able to get the breadcrumb, I have denormalized it.

The problem is that this breadcrumb is hard to keep up to date, because a top level category can be moved and thus all its childs breadcrumbs must be updated. There can be many child categories to update, and there are different ways to deal with this problem. Some of them are secure but expensive (recursion), and others are faster but could lead to some inconsistencies.

Here what I'd like to know is if it is possible to do a query to retrieve categories that have a bad breadcrumb. I need a query that permits to do:

Retrieve all the categories that do not have: last array element breadcrumb.id = parentId

I don't think the "last array element" part is possible, but it would also be nice to be able to do:

Retrieve all the categories that do not have: breadcrumb.id contains parentId

Any solution, available in Scala or Java driver? I'm using Salat/Casbah.

This question may help you to understand what I'm facing: Which DB would you use? MongoDB/Neo4j/SQL... all of them?

How to do trees always comes down to your querying needs. How do you need to search this tree? Are the categories currently separate from the file listings for the FS so that you have one collection of `categories` and then one of `files`? Unfortunately trees are no easy thing in most databases and do normally require recursion either in finding or updating, the most performant being the one that suites your most used queries. — Sammaye, Jan 02 '13 at 08:18
If you have time to read, the anwser is in the link I give at the end :) There are 2 separate collections for categories and files. But files must be aware of their breadcrumb so that when search results are printed (20 results/page for exemple), breadcrumb is available out of the box without needing 20 expensive recursions — Sebastien Lorber, Jan 02 '13 at 09:46
Hmm the link is a little tl;dr if I must be honest. Have you thought of the docs here: http://docs.mongodb.org/manual/tutorial/model-tree-structures/ especially the materialised paths one. It is update heavy but easier to query. Your breadcrumb is a little redundant because you have the problem that you have transmitted the name of the category to all of it's children creating a subset of objects. If you were to use paths instead you could actually satisfy quite a few of your queries, i.e. `find({path: /,06060607/})` for judging if that id is at the end. — Sammaye, Jan 02 '13 at 11:53
Maybe you can define these 20 queries as well? I see one query per child category that contains the parent atm. — Sammaye, Jan 02 '13 at 11:53

score 3 · Accepted Answer · answered Jan 03 '13 at 03:27

You can do the Retrieve all the categories that do not have: last array element breadcrumb.id = parentId query with a $where operator:

db.test.find({
    // Find docs were breadcrumb is empty or its last element's id != parentId
    $where: '!this.breadcrumb.length || this.breadcrumb[this.breadcrumb.length-1].id !== this.parentId' 
})

Retrieve documents having array containing another document attribute

1 Answers1