Bonnie Scott Bonnie Scott - 2 months ago 24
SQL Question

Array field in postgres, need to do self-join with results

I have a table that looks like this:

stuff
id integer
content text
score double
children[] (an array of id's from this same table)


I'd like to run a query that selects all the children for a given id, and then right away gets the full row for all these children, sorted by score.

Any suggestions on the best way to do this? I've looked into WITH RECURSIVE but I'm not sure that's workable. Tried posting at postgresql SE with no luck.

Answer

The following query will find all rows corresponding to the children of the object with id 14:

SELECT *
FROM unnest((SELECT children FROM stuff WHERE id=14)) t(id)
     JOIN stuff USING (id)
ORDER BY score;

This works by finding the children of 14 as array first, then we convert it into a table using the unnest function, and then we join with stuff to find all rows with the given ids.