Cast array from jsonb_array_elements_text() to array of integers

Question

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.

Answer 1

You can use jsonb_array_elements_text with ARRAY constructor and casting it into int[] like:

SELECT ARRAY(
  SELECT jsonb_array_elements_text('{"users": [1, 2, 3]}'::jsonb->'users'))::int[]