Double nested array_agg(row_to_json())

Question

I have three tables: person, pet, pup.

A person can have many pets. A pet can have many pups.

I build my schema and insert data like this:

create table person (
  id serial primary key,
  name text
);

create table pet (
  id serial primary key,
  owner int,
  name text
);

create table pup (
  id serial primary key,
  parent int,
  name text
);

insert into person (name) values
('tom'), ('dick'), ('harry');

insert into pet (owner, name) values
(1, 'fluffy'),
(2, 'snuffles'),
(1, 'mr potato head');

insert into pup (parent, name) values
(1, 'fluffy jr'),
(1, 'fluffy II');

we see person "tom" has two pets "fluffy" and "mr potato head". we see person "dick" has one pet "snuffles".

we see pet "fluffy" has two pups "fluffy jr" and "fluffy II".

I am trying to get a doubly nested array, but I can only get one level of nesting. Here is my sql fiddle - http://sqlfiddle.com/#!17/03659/2 and they query i use:

select p.*,
       array_agg(row_to_json(
         pet
       )) filter (where pet.id is not null) as pets
from person p
left outer join pet pet
on pet.owner = p.id
group by p.id;

What I am hoping for is for entry for "tom" to be doubly nested for "pups":

{
  "id": 1,
  "name": "tom",
  {
    "id": 1,
    "owner": 1,
    "name": "fluffy",
    "pups": [
      {
        "id": 1,
        "parent": 1,
        "name": "fluffy jr"
      },
      {
        "id": 2,
        "parent": 1,
        "name": "fluffy II"
      }
    ]
  }
}

Anyone know how to get this double nesting?

user_10676716 · Answer

You can aggregate the "pups" by parent in a subquery, then aggregate by person in the outer query:

select pn.*,
    jsonb_agg(jsonb_build_object(
        'id',    pt.id,
        'owner', pt.owner,
        'name',  pt.name,
        'pups',  pp.pups
    )) filter(where pt.id is not null) pets
from person pn
left join pet pt 
    on pt.owner = pn.id
left join (select parent, jsonb_agg(pp) pups from pup pp group by parent) pp 
    on pp.parent = pt.id
group by pn.id;

Note that this uses JSON aggregation function json[b]_agg() to generate the JSON arrays rather than array_agg(). I also switched from json to jsonb - the latter should be preferred, as it offers more features that then former.

Double nested array_agg(row_to_json())

1 Answers