I have a PostgreSQL 9.5.3 DB with tables like this:
container
id: uuid (pk)
... other data
thing
id: uuid (pk)
... other data
container_thing
container_id: uuid (fk)
thing_id: uuid (fk)
primary key (container_id, thing_id)
A container
can point to any number of thing
s (without duplicates), and a thing
can be pointed to by any number of container
s.
There may be a large number of containers and things (depends on how many customers we have). Each container would likely only have 1 to 10 things in it. We'll only query for around 20 containers at once, max. A container can be empty, and I need to get an empty array back.
I need to build json objects that represent containers, something like this:
{
"id": "d7e1bc6b-b659-432d-b346-29f3a530bfa9",
... other data
"thingIds": [
"4e3ad81b-f2b5-4220-8e0e-e9d53c80a214",
"f26f49e5-76b4-4363-9ffe-9654ba0b0f0d"
]
}
This is working fine, but I'm doing it by using two queries:
select * from "container" where "id" in (<list of container ids>)
select * from "container_thing" where "container_id" in (<list of container ids>)
I then procedurally build the "thingIds" array for each container.
Later I found a solution with correlated subqueries that works nicely for me at the moment.
select *, array(select thing_id from container_thing where container_id = c.id) as "thingIds"
from container c;
Is there a better way to do this, perhaps using a join somehow?
It seems like they always generate a single set of rows, which would mean duplicating the container
data for every thing
that's being pointed at.