Build JSON object from one-to-many relationship data in a single query?

Question

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 (<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.

Answer 1

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 (<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 (<list of container ids>) -- repeat condition
      GROUP  BY 1
      ) ct USING (id)
   WHERE  c.id IN (<list of container ids>)
   ) 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 (<list of container ids>)
   ) 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:

1. "thingIds":[null]
2. "thingIds":null
3. "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 (<list of container ids>) -- repeat condition
   GROUP  BY 1
   ) ct USING (id)
WHERE  c.id IN (<list of container ids>);

Alternatively:

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

Answer 2

A join (on container_id) will work very well in this situation. Yes, you will get multiple rows with duplicated container data.

If your coding language supports an ORM (e.g. MyBatis or Hibernate for Java), the redundant duplication of container fields will be handled for you. The ORM will return a list of containers with each container having a list of IDs. Producing JSON from such an object graph should be trivial.

With modern databases, there should not be a measurable performance hit due to the data duplication.

Answer 3

Join + string_agg()

select '{"id": "' || cast (c.id as varchar(36)) ||', "thingIds": ["'
   || string_agg(cast(ct.thing_id as varchar(36)),'","') 
   || '"]}'
from container c
join container_thing ct on c.id=ct.container_id
group by c.id;

Answer 4

This looks like a good fit for Postgres' JSON functions:

select to_json(x)
from (
  select c.*, json_agg(ct.thing_id) as "thingIds"
  from container_thing ct
    join container c on ct.container_id = c.id
  group by c.id
) x

SQLFiddle example: http://sqlfiddle.com/#!15/cd9992/1

Answer 5

If someone needs Row to Whole table json encoded linked by column (id). This is the way to go:

SELECT main_table.*, subVirt.subrows_json
FROM   main_table
LEFT   JOIN (
  SELECT parent_id, json_agg(row_to_json(sub_table)) AS subrows_json
  FROM  sub_table
  GROUP  BY 1
  ) subVirt ON subVirt.parent_id = main_table.id

So the result is each row from main_table has a column (subrows_json) with json encoded row data from sub_table, joined by main_table.id->sub_table.parent_id Hope it helps others.