Postgres jsonb_path_query to filter sub-arrays by their length

Question

In Postgres 13.3, I'd like to extract sub-arrays of a particular length from a jsonb value:

select jsonb_path_query('[[0,1], [0,1,2]]'::jsonb, '$[*] ? (@.size() >= 3)');

However, this returns nothing (0 rows).

What am I doing wrong? Note that I'd like to keep using jsonpath - while this example is simplified, the real case is much more involved and relies on the flexibility of jsonpath expressions.

Ftisiot · Accepted Answer · 2021-07-01 12:40:31Z

2

The $[*] will execute each element within your array's array at the time, so evaluates separately 0,1,0,1,2

The following does the trick

select jsonb_path_query('[[0,1], [0,1,2]]'::jsonb, '$ ? (@.size() >= 3)');

result

 jsonb_path_query 
------------------
 [0, 1, 2]
(1 row)

answered Jul 1, 2021 at 12:40

Ftisiot

1,8981 gold badge9 silver badges14 bronze badges

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

1 Comment

Meglio Over a year ago

An alternative approach I just found is to use strict mode, so that the sub-arrays are not expanded automatically: strict $[*] ? (...).

Collectives™ on Stack Overflow

Postgres jsonb_path_query to filter sub-arrays by their length

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related