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

Jackie McDoniel · Accepted Answer · 2020-05-29 04:07:34Z

0

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

answered May 29, 2020 at 4:07

Jackie McDoniel

71.8k9 gold badges191 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to filter table rows in nodejs using db queries?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related