Is it possible to filter arrays without filtering inside jsonb in postgres?

Question

I was wondering if it is possible to filter array inside jsonb without filtering the whole table or unnesting it?

Say I have a table:

id	jsnob
1	{"names":["anna", "peter", "armin"]}
2	{"names":["anna"]}
3	{"names":["peter"]}

I want to have the same table, but so that names are filtered, to only names starting from "a":

id	jsnob
1	{"names":["anna", "armin"]}
2	{"names":["anna"]}
3	{"names":null}

I implemented it with unnest ( jsonb_array_elements), and then group by back to json, but it is quite slow. I was wondering if there is a faster way?

Answer 1

You can do it record by record using a scalar subquery w/o need to flatten the whole table and then re-group. Here is an update example.

update the_table 
set "jsonb" = jsonb_set("jsonb", '{names}',
  (select jsonb_agg(e) from jsonb_array_elements_text("jsonb"->'names') e where e ~* '^a')
);

DB Fiddle

Note The above does not guarantee that the order of the array elements is retained. To be strict use the scalar subquery below.

(
 select jsonb_agg(e ORDER BY i) 
 from jsonb_array_elements_text("jsonb"->'names') WITH ORDINALITY t(e, i)
 where e ~* '^a'
)