knex postgres join as nested JSON

Question

I'm using knex with postgresql db, I have a row in table A which has one-to-one relation to rows in table B and one-to-many relation to rows in table C.

I wanna join a row from A with B and C and get a json like

{
  aCol1: ...,
  b: {
    bCol1: ...,
    bCol2: ...,
  },
  c: [
    {
      cCol1: ...
    },
    {
      cCol1: ...
    }
  ]
}

Where stuff like aCol means column from table A, bCol - column from table B, which was joint.

How can I achieve that with one query and joins?

Answer 1

Use JsonAgg for this

SQL :

select tA.id, tA.name, json_agg(tB) as tB from "tA" left join "tB" on "tA"."tB_id" = "tB"."id" group by "tA"."id", "tA"."name"

Knex :

db('tA')
.leftJoin('tA', 'tA.tB_id', 'tB.id')
.groupBy(['tA.id', 'tA.name'])
.select(db.raw(`tA.id,tA.name,json_agg(tB) as tB`))

Answer 2

You can get what you want with JSON_AGG(). You can also use Postgres's JSON operators to get only one record back instead of many. And if you want to avoid a large GROUP BY clause, use JSON_AGG() on the primary table you're selecting from instead of selecting every column. Using your example this would look like:

select
  a.id,
  json_agg(a.*)->0 as a_obj,
  json_agg(b.*)->0 as b_obj,
  json_agg(c.*) as c_objs
from a
left join b on b.a_id = a.id
left join c on c.a_id = a.id
group by a.id

This will give you a nice clean result like:

id       | 123
a_obj    | {"id":123,...}
b_obj    | {"bCol1":...,"bCol2":...}
c_objs   | [{"cCol1":...},{"cCol1":...}]