Hacker News Reader

I was dealing with a tricky SQL problem yesterday: take a table of file paths and return a JSON tree with `name` and `children` keys. Definitely requires a recursive CTE, but I ultimately gave up an implemented the transformation in the host language.

It was the first thing that I tried in the playground, since it was top of mind, but unfortunately it doesn't seem like the language supports recursive queries (yet). https://github.com/PRQL/prql/issues/407

View on HN

I wrestled with a graph traversal problem and Postgres docs for a hot minute before realizing that the "recursive" working set can only access the results of the previous iteration. With that in mind, I took a crack at your problem with the following approach:

1. Build the JSON from the bottom up starting at the deepest path.

2. At each step, handle two cases: a) build the parent of the working set, and b) collect any sibling paths at the same depth.

3. The last step aggregates all children together since all paths descend from '/'.

Solution: https://www.db-fiddle.com/f/qTcG5BUjM8GzuzBfHamnVJ/4

There was a fair bit of trickiness. I used a CTE in the recursive step to reference the recursive table multiple times and to enable aggregation in the recursive step. I used a few helper functions for my own sanity.

Reply on news.ycombinator.com