CODEWITHSUNDEEP
sqlpostgresqlknex.js
tristantzara
tristantzara

Reputation: 5897

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?

Upvotes: 3

Views: 3289

Answers (4)

Kushagra Acharya
Kushagra Acharya

Reputation: 176

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": "[email protected]",
                    "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"
                }
            ]
        }
    ]
}

Upvotes: 1

a paid nerd
a paid nerd

Reputation: 31502

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

Upvotes: 2

SHIKHIL S
SHIKHIL S

Reputation: 91

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

Upvotes: 6

Mikael Lepistö
Mikael Lepistö

Reputation: 19718

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

Upvotes: -2

Related Questions