How to make sql table column from jsonb int array

Question

I have a table with jsonb array column like:

 name   |  arr
---------------
 n1     [1,2,3]
 n2     [4,5,6]

I want to get a table from it like:

name   |  element
---------------
  n1        1
  n1        2
  n1        3
  n2        4
  n2        5
  n2        6

Kaushik Nayak · Accepted Answer · 2019-05-24 09:10:55Z

2

Use jsonb_array_elements_text

select name,j.el::int as element
   from t cross join jsonb_array_elements_text(arr) as j(el)

answered May 24, 2019 at 9:10

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

1 Comment

Or without a join, simply: SELECT name, jsonb_array_elements_text(arr) AS element FROM t