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

Question

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.

Answer 1

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