CODEWITHSUNDEEP
sqlpostgresqljsonb
user4528432
user4528432

Reputation:

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.

Upvotes: 3

Views: 5942

Answers (2)

saaj
saaj

Reputation: 25184

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[]

Upvotes: 3

Vao Tsun
Vao Tsun

Reputation: 51446

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)

Upvotes: 1

Related Questions