4 ms·
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 recu
by CGamesPlay 4y ago
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 https://github.com/PRQL/prql/issues/407
- sa46 4y agoI 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 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.