Length of array inside a jsonb with Postgres and Typeorm

Question

I'm developing an app with NodeJS, PostgreSQL and Typeorm. I have a column "data" in my database which is of type jsonb. Each element has the following structure:

{
  "a": [
    { "b": "some-text" },
    { "b": "some-text" },
    ...
  ]
}

I want to select all rows where the size of the "a" array is N AND where the "b" field of the first object inside the array is of value X.

So far I tried to get the size query working first by using jsonb_array_length as follows:

await this.createQueryBuilder('entity')
        .where(`jsonb_array_length("entity".data->>'a') = 5`)
        .getMany();

However, this throws an error:

No function matches the given name and argument types. You might need to add explicit type casts.

Any help would be appreciated.

use -> operator to get josnb instead of ->> which yields text. jsonb_array_length("entity".data->'a') = 5. BTW why don't you try native queries instead of the smudgy query builder? — Stefanov.sm
@Stefanov.sm - You should reply as the answer, you are right. — Edward

Stefanov.sm Stefanov.sm · Accepted Answer · 2021-03-18T17:25:42

Use the -> operator to get josnb instead of ->> which yields text.
Your where expression should be this:

jsonb_array_length("entity".data -> 'a') = 5

Length of array inside a jsonb with Postgres and Typeorm

1 Answers