Q:

Cast array from jsonb_array_elements_text() to array of integers

I have a JSON as following:
{
    "users": [1, 2, 3]
}

I am converting the users JSON array to a PostgreSQL array:
select user.identifier
from   jsonb_array_elements_text(('{"users": [1, 2, 3]}'::jsonb)->'users') as user(identifier);

This returns an array of text values. I want an array of integer values. With a subquery, it could look like the following, but this is rather clumsy:
select user.identifier
from   (select user.identifier::integer from jsonb_array_elements_text(('{"users": [1, 2, 3]}'::jsonb)->'users') as user(identifier)) user

Is this doable without an additional subquery? I can't find such syntax or utility function in the documentation.

A:

you can aggregate straight away:
t=# select array_agg(identifier)::int[], pg_typeof(array_agg(identifier)::int[])
from   jsonb_array_elements_text(('{"users": [1, 2, 3]}'::jsonb)->'users') as u(identifier);
 array_agg | pg_typeof
-----------+-----------
 {1,2,3}   | integer[]
(1 row)

