I already said I don't have time to rewrite the query right now, but I'd definitely get rid of the correlated subqueries, which should be obvious from my comments. I may or may not use an intermediate representation, like a CTE, and I'd probably make use of JSONB_OBJECT_AGG.
I kind of thought it was a red flag that you were accusing me of not understanding the post, which is about how SQL is bad at hierarchical data, while defending the bad SQL he used to defend his argument. But I didn't make a big deal out of it.
I know. I answered that in my second paragraph. They also asked how I'd approach writing the query after I already mentioned the issues and sent them the postgres doc page.
You would restate his main argument as "I kind of thought it was a red flag that you were accusing me of not understanding the post, which is about how SQL is bad at hierarchical data, while defending the bad SQL he used to defend his argument. But I didn't make a big deal out of it."? That doesn't make any sense.
I think you don't understand what the post is about, you don't understand what the query is trying to achieve, you don't know a better way to do it, and you're just engaging in ego defense instead of contributing anything.
Seems to me, from the bit you quoted, that their answer would be something along the lines of: "I would restate the point of the post as talking about how SQL is bad at hierarchical data, while using bad SQL to defend this argument." What they actually wrote is so close to this that your claim not to be able to see that feels a tad disingenious.
I kind of thought it was a red flag that you were accusing me of not understanding the post, which is about how SQL is bad at hierarchical data, while defending the bad SQL he used to defend his argument. But I didn't make a big deal out of it.