knex postgres join as nested JSON

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

You can achieve it with one query and join, but it gets overly complicated. You should use an ORM instead.

Objection.js is based on knex and allows you to do this kind of queries in a trivial and performant way (you can choose to use joins or multiple queries, usually multiple queries are more performant).

With Objection.js syntax it would look like this:

const res = await ATableModel.query().where('id',  a.id).eager('[b,c]');