Sample Header Ad - 728x90

How to store and query a tree structure in Cosmos DB?

2 votes
1 answer
1215 views
I'm migrating a relational DB over to Cosmos DB and am unsure about a tree structure. There are several thousand rows of data ("Offerings") where each offering can have child items arranged in a tree hierarchy. In the source DB it's using the traditional ParentID tactic:
| ID | Content         | ParentID
|0   | "Root"          | -1
|1   | "Child 1"       | 0
|2   | "Child 2"       | 0
|3   | "Child's child" | 2
In my actual DB, "Content" is not just one field but multiple columns, some of them being JSON blobs already. When moving over to Cosmos DB, I see three options: **Option 1: Should I store the actual hierarchy?**
{
  id : 0,
  content : "Root",
  children : [
    {
      id : 1,
      content : "Child 1",
      children : []
    },
    {
      id : 2,
      content : "Child 2",
      children : [
        {
          id : 3,
          content : "Child's child",
          children : []
        }
      ]
    }
  ]
}
This represents the actual structure and allows easy traversal of the hierarchy when reading it. However: what if I need (for example) "all children aged 10"? Is there a way to query for the property age, no matter how deep down in the hierachy and have it return a list of child object? **Option 2: Should I store a list and an extra property representing the hierarchy?**
{

  id : 0,
  content : "Root",
  descendants : [
    {
      id : 1,
      content : "Child 1",
    },
    {
      id : 2,
      content : "Child 2"
    },
    {
      id : 3,
      content : "Child's child",
    }
  ],
  hierarchy : {
    id : 0,
    children : [
      {
        id : 1,
        children : []
      },
      {
        id : 2,
        children : [
          {
            id : ,
            children : []
          }
        ]
      }

    ]

  }
}
This allows me to easily get all descendants without tree traversals. Is there a major pro/con for either one? Is either one bad design? Or does it entirely depend on what queries I mostly expect?
Asked by Krumelur (121 rep)
Dec 6, 2021, 11:54 AM
Last activity: Dec 6, 2022, 10:32 PM