RethinkDB hierarchical data

Question

I'm trying to see if there is a way to transform a flat list into a hierarchical tree in rethinkdb.

Given this table:

nodes
------
-id
-name
-parent

I can query all with r.db('app').table('nodes') and get a flat list:

[
  {name: "one", id: "1"}
  {name: "two", id: "2", parent: "1"}
  {name: "three", id: "3", parent: "2"}
]

But I'd really like a query that returns the data in hierarchal structure:

[
  {
    name: "one", 
    id: "1",
    children: [
      {
        name: "two", 
        id: "2", 
        children: [
          {name: "three", id: "3"}
        ]
      }
    ]
  }
]

Is this possible in rethinkdb? Postgres has WITH RECURSIVE queries for this. Currently I'm doing the transformation in the application layer but it's getting complex -- for example to fetch a single node I also have to fetch ALL nodes, recursively add its descendants, and return just the requested node. Anyway, would love to find a way to do this if possible in rethinkdb. Thanks!

Answer 1

There's no easy way to do this in RethinkDB, unfortunately. How attached are you to that schema? (If the answer is "not very", what queries do you need to be fast on this table?)