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.

up vote 6 down vote accepted

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;

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   ... 
  • Thank you for this, very thorough and extremely helpful! – antsyawn Jul 18 '16 at 21:20

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.

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;

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

  • Thanks, this works nicely, and led me down the path of learning more about Postgres built-in functions etc. The json stuff is great, and I'm sure will come in very useful. I modified what you had above to be a left join since I want 'containers' that have no 'things' as well. But this left a 'null' in the array. That led me to find the 'array' function, which also works nicely when used as a sub-query. Would you mind taking a look at this alternative and seeing if you spot any problems with that approach? – antsyawn Jul 18 '16 at 0:28

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.