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, Commented Mar 18, 2021 at 15:13
@Stefanov.sm - You should reply as the answer, you are right. — Edward
– Edward, Commented Mar 18, 2021 at 16:13

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

1

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

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

answered Mar 18, 2021 at 17:25

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

Length of array inside a jsonb with Postgres and Typeorm

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related