PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Typeorm queryBuilder select only a specific key value of a JSONB type column

Let’s say i’ve a simple queryBuilder like:

const workout = await this.workouts
            .createQueryBuilder('workout')
            .select(['workout.uuid', `workout.name ->> 'it'` as name])
            .where('workout.uuid = :workoutUuid', { workoutUuid })
            .getOne();
return workout;

By using getRawOne() i see that typeorm is correctly taking the value of the key ‘it’ inside workout.name jsonb.
But since i have to use .getOne() typeorm tries to match the results with the entity column names and so my workout.name.it is not returned by the queryBuilder.

Any idea how to do this using .getOne()?

I’ve tried a lot of things, different operators or giving different aliases to the selected json column but nothing to do.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found the solution for that, thanks for montan tip but a little different.

    We just need to use .addSelect() method but set as alias the same alias that getRawMany()/getRawOne() would set. So in my case i just needed to use this

    .addSelect(workout.name ->> 'it', 'workout_name')

    and give 'workout_name' alias instead of 'name'.

    For any other innested entity just use entityName_propertyName.

    So in my case if i would leftJoin BlockEntity just use

    .addSelect(block.title ->> 'it', 'block_title')


  2. 0

    If you still want to use getOne(), you can use addSelect() to define a custom alias for the extracted JSON key, but you’ll need to manually handle that alias in your return value because getOne() will not map it automatically

    const workout = await this.workouts
  .createQueryBuilder('workout')
  .select(['workout.uuid'])
  .addSelect(`workout.name ->> 'it'`, 'name')
  .where('workout.uuid = :workoutUuid', { workoutUuid })
  .getOne();

if (workout) {
  workout.name = (await this.workouts
    .createQueryBuilder('workout')
    .select(`workout.name ->> 'it'`, 'name')
    .where('workout.uuid = :workoutUuid', { workoutUuid })
    .getRawOne()).name;
}

return workout;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search