0

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.

Meglio
  • 1,646
  • 2
  • 17
  • 33

1 Answers1

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)
Ftisiot
  • 1,808
  • 1
  • 7
  • 13
  • 1
    An alternative approach I just found is to use `strict` mode, so that the sub-arrays are not expanded automatically: `strict $[*] ? (...)`. – Meglio Jul 01 '21 at 12:57