That’s left as an exercize to the reader though! Conclusion Ta-dah! Job done! It’s now possible to pass the result over to the very niceĭata set. JSON array to group them into a single valid document: Those are the Warrior, Wizard, Priest, and Rogue classes. Then has to deal with as many rows as we have top-level classes, in our case Walks up the tree aggregating classes as JSON documents. In the previous section already, and the dndclasses_from_children then In this query, the dndclasses_from_parents is the RECURSIVE query we saw the top-level classes all into the same JSON document, Finally, the traversal being done, we can aggregate Join dndclasses c on c.id = tree.parent_id || jsonb_build_object('Sub Classes', js) as js sub-classes as JSON document parts that we glue together the bottom-up traversal makes it possible to accumulate as we're traversing our graph from the leaf nodes, build our JSON document, one piece at a time Where level > 0 and not id = any(parents) Json_agg(jsonb_build_object('Name', c.name))::jsonb as js pointing to them as their parents, directly or indirectly Leaf nodes are not parents (level > 0) and have no other row Now start from the leaf nodes and recurse to the top-level Select c.id, c.name, parents || c.parent_id, level+1 Recursively find sub-classes and append them to the result-set With recursive dndclasses_from_parents as ( select id, name, ''::int as parents, 0 as level Have a parent class, our top-level classes: The first step begins at the top of the tree, with those classes that don’t Single step, obviously: you can’t have seen the bottom of the tree already That’s more complex and can’t be done in a Top-level classes and still see from there all the sub-classes that are In this case though, we want to walk through our class graph from the Given class entry up the chain to its top-level ancestor, because we know Now, it’s easy enough in SQL to run either from the set of top-levelĬlasses, because we know they have NULL as their parent_id, or from a That, we also need to accumulate the sub-classes into a single entity. To recurse over it from the parents classes to their sub-classes.
To be able to export our whole data set as a single JSON document, we need The result we need now should look something like this: Hand-over to the browser so that the client rendering can now happen. Would just run a single SQL query and the result would be a piece of JSON to Hand over to the d3js JavaScript library. Given this data set, our goal is to obtain a single JSON file that we can
I’ve been trying to make sense of the Wikipedia pages for the characterĬlasses and I had to pick a specific edition and extend it with a prestigeĬlass (the Assassin), so I hope fans of the game that are reading thisĪrticle will accept this classification for its pedagogic interest… Exporting a Hierarchy in JSON
Of a very simple TABLE dndclasses SQL command: id │ parent_id │ name This data set looks like in the following query result, which is the ouput Begin create table dndclasses ( id int generated by default as identity primary key, parent_id int references dndclasses(id), name text ) - INSERT commands
0 kommentar(er)
