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?

Hi Tristan. That's not what SQL is made for. Joining A, B and C tables will result in a Cartesian product between them. With that in hands, you'd have to assemble your JSON in your JS code from the query result. — nicolasl, Oct 17 '18 at 17:11
Possible duplicate of [Create nested json from sql query postgres 9.4](https://stackoverflow.com/questions/42222968/create-nested-json-from-sql-query-postgres-9-4) — Mikael Lepistö, Apr 09 '19 at 08:24

score 5 · Answer 1 · answered Dec 11 '20 at 05:04

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`))

score 1 · Answer 2 · answered Nov 20 '22 at 04:58

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":...}]

score 0 · Answer 3 · answered Jan 31 '23 at 13:17

Used json_agg

Session table with two foreign keys at users and devices

exports.up = async function (knex) {
  await knex.raw('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"');
  return knex.schema.createTable('sessions', function (table) {
    table.uuid('id').defaultTo(knex.raw('uuid_generate_v4()'));
    table
      .integer('user')
      .notNullable()
      .references('id')
      .inTable('users')
      .onDelete('cascade');
    table
      .integer('device')
      .notNullable()
      .references('id')
      .inTable('devices')
      .onDelete('cascade');
    table.timestamp('session_start').defaultTo(knex.raw('CURRENT_TIMESTAMP'));
    table.timestamp('session_pre_end').nullable();
  });
};

Knex : get nested data using join

 await this.knex
        .from('sessions')
        .join('users', 'sessions.user', 'users.id')
        .groupBy(['sessions.id'])
        .join('devices', 'sessions.device', 'devices.id')
        .select(
          this.knex.raw(
            `sessions.id, json_agg(users) as users, json_agg(devices) as devices`,
          ),
        );

Output JSON

{
    "sessions": [
        {
            "id": "881e4a2c-4e55-4045-b80d-3232ab3b616a",
            "users": [
                {
                    "id": 2,
                    "access_type": "user",
                    "firstname": "Two",
                    "lastname": "Karki",
                    "phone": "9845225544",
                    "email": "example2@nest.it",
                    "password": "123456"
                }
            ],
            "devices": [
                {
                    "id": 1,
                    "short_name": "ps1",
                    "full_name": "Play Station 5 - 1",
                    "model_number": "ps5001",
                    "type": "console",
                    "isActive": true,
                    "created_at": "2023-01-31T00:17:16.974896+05:45",
                    "updated_at": "2023-01-31T00:17:16.974896+05:45"
                }
            ]
        }
    ]
}

score -2 · Accepted Answer · answered Oct 19 '18 at 10:25

-2

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]');

answered Oct 19 '18 at 10:25

Mikael Lepistö

18,909
3
68
70

Well, doesn't an orm do the same under the hood? I wanted to know how to solve it, not just `npm install something` – tristantzara Nov 01 '18 at 09:10
Maybe you should have asked SQL syntax for achieving it instead of general easy solution :) IIRC that question has been answered somewhere in stackoverflow. It did contain joins + some json operations, which were used to modify flat result from join to object format. – Mikael Lepistö Nov 02 '18 at 11:07
yeah, I know that I can get a cartesian product of tables and parse that json myself, was wondering if there was some feature/pattern/shorthand for that in knex – tristantzara Nov 02 '18 at 11:08
Also orm’s usually do data modifying from flat result array to nested objects in application side in this case by postprocessing it with javascript. Generating objects in database side is really inconvenient. – Mikael Lepistö Nov 02 '18 at 11:12
1

"...was wondering if there was some feature/pattern/shorthand for that in knex" no. Knex is lower level tool, which doesn't care about models / nested objects. That is why I recommended using an ORM like objection.js which is based on knex and adds lots of useful features on top of it. – Mikael Lepistö Nov 02 '18 at 11:14
Answer is set to be the correct one, but also with 2 minus votes and with no competing answers. It seems that for some people the truth hurts – Mikael Lepistö Aug 04 '20 at 15:10

knex postgres join as nested JSON

4 Answers4