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.

saaj · Accepted Answer · 2018-07-20 09:12:13Z

3

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

answered Jul 20, 2018 at 9:12

saaj

25.5k6 gold badges116 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vao Tsun · Accepted Answer · 2018-01-15 08:14:38Z

1

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)

answered Jan 15, 2018 at 8:14

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Cast array from jsonb_array_elements_text() to array of integers

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related