How to filter table rows in nodejs using db queries?

Question

I am using nest js framework for my demo application. In my demo application I have an entity Category which has two columns (id, description). description is jsonb type.

export class Category extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'jsonb' })
  description;
}

I inserted some data in my table.

Now I want to filter that data and only show those rows which have name = test.

Currently I'm using the code shown here, which returns all rows. But I need only the rows which have name equals to test. Currently there is only one row

@Get('/app')
  async getApp(): Promise<Category[]> {
    return this.categoryRepository.find({});
  }

Here is my full code: https://codesandbox.io/s/nest-nhqyb?file=/src/app.controller.ts:568-670

You can see filter result like

https://nhqyb-5000.sse.codesandbox.io/app

score 0 · Answer 1 · answered May 29 '20 at 04:07

According to this issue, it's not possible unless you are writing raw SQL and using database specific syntax.

In Postgres, it would look something like

SELECT * 
FROM Category cat
WHERE EXISTS (
  SELECT FROM jsonb_each(cat.description) desc
   WHERE  desc.value->>'name' ILIKE 'test'
   );

Lots more discussion and in depth answers over here

How to filter table rows in nodejs using db queries?

1 Answers1