How To Turn A Simple Json(b) Int Array Into An Integer[] In Postgresql 9.4+

I have this array from a json object: [1, 9, 12] Since it uses the square bracket notation because it is fetched directly from a json object I cannot cast it to ::integer[] and whe

Solution 1:

Obviously you have a JSON array nested inside an outer JSON array:

SELECT n.*, array_agg(p)::int[] AS group_node_permissions
FROM   my_user_group u
     , jsonb_array_elements(u.node_permissions) elem
JOIN   node n ON n.id = (elem->>'id')::int
     , jsonb_array_elements_text(elem->'permissions') p
GROUPBY n.id;  -- id being the PK

• This is assuming there are no empty arras in permissions. Else you need LEFT JOIN LATERAL ... ON TRUE:

  • Call a set-returning function with an array argument multiple times

• This should preserve the original order of the JSON array, but there are no guarantees. If you need to make sure, use WITH ORDINALITY.

  • PostgreSQL unnest() with element number

• LEFT [OUTER] JOIN would be pointless, since the later predicate on a column of the left table forces [INNER] JOIN behavior anyways.

Related answer on dba.SE with more details and explanation:

• How to turn json array into Postgres array?

Depending on details of the use case, it might be a good idea to support the query with a GIN index:

• Index for finding an element in a JSON array

As for your P.S., it depends on the complete picture. All other considerations aside a Postgres array is typically a bit smaller and faster than jsonb holding a JSON array. Testing for existence of an element can be very fast with with a GIN index either way:

jsonarray @> '12'
intarray @> '{12}'

Note in particular, that the variant 12 = ANY(intarray) is not supported by a GIN index. Details in the manual.