Build JSON object from one-to-many relationship data in a single query? 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 things (without duplicates), and a thing can be pointed to by any number of containers. 

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 (&lt;list of container ids&gt;)
select * from "container_thing" where "container_id" in (&lt;list of container ids&gt;)


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.
If containers can be empty, the currently accepted solution does not work for you. It has to be an outer join to preserve rows without match - to get equivalent results to the correlated subqueries you are using in your fiddle:


select *, array(select thing_id from container_thing where container_id = container.id) as "thingIds"
from container



1.

SELECT to_json(sub) AS container_with_things
FROM  (
   SELECT c.*, json_agg(thing_id) AS "thingIds"
   FROM   container c
   LEFT   JOIN container_thing ct ON  ct.container_id = c.id
   WHERE  c.id IN (&lt;list of container ids>)
   GROUP  BY c.id
   ) sub;

2.

With more than a few rows per container (you mentioned 20) it's typically faster to aggregate before you join:

SELECT to_json(sub) AS container_with_things
FROM  (
   SELECT c.*, ct."thingIds"
   FROM   container c
   LEFT   JOIN (
      SELECT container_id AS id, json_agg(thing_id) AS "thingIds"
      FROM   container_thing
      WHERE  container_id IN (&lt;list of container ids&gt;) -- repeat condition
      GROUP  BY 1
      ) ct USING (id)
   WHERE  c.id IN (&lt;list of container ids&gt;)
   ) sub;


3.

Or you can combine the ARRAY constructor you found with LEFT JOIN LATERAL:

SELECT to_json(sub) AS container_with_things
FROM  (
   SELECT c.*, ct."thingIds"
   FROM   container c
   LEFT   JOIN LATERAL (
      SELECT ARRAY (
         SELECT thing_id
         FROM   container_thing
         WHERE  container_id = c.id
         -- ORDER  BY thing_id  -- optional order for deterministic results
         ) AS "thingIds"
      ) ct ON true
   WHERE  c.id IN (&lt;list of container ids&gt;)
   ) sub;



What is the difference between LATERAL and a subquery in PostgreSQL?


Might be faster, yet.

SQL Fiddle. (Extending @a_horse's fiddle.)

Note that the result for empty containers is subtly different in the three queries above:


"thingIds":[null]
"thingIds":null
"thingIds":[]


4.

In Postgres 9.5 (since you are using it) you could also work with  jsonb and its functionality and one less subquery:

SELECT jsonb_set(to_jsonb(c), '{thingIds}', "thingIds") AS container_with_things
FROM   container c
LEFT   JOIN (
   SELECT container_id AS id, jsonb_agg(thing_id) AS "thingIds"
   FROM   container_thing
   WHERE  container_id IN (&lt;list of container ids&gt;) -- repeat condition
   GROUP  BY 1
   ) ct USING (id)
WHERE  c.id IN (&lt;list of container ids&gt;);


Alternatively:

SELECT to_jsonb(c) || jsonb_build_object('thingIds', "thingIds") AS container_with_things
FROM   ...