Convert Jsonb simple array into column group by-CodePudding

I have a simple table with a Jsonb array of phone numbers:

CREATE TEMP TABLE IF NOT EXISTS user_info (
    id serial,
    phone_numbers jsonb
);
INSERT INTO user_info values (1, '["123456"]'),(2, '["564789"]'), (3, '["564747", "545884"]');

SQLFiddle: SQLFiddle Link

now, I want to group the array of numbers into columns. something like:

phone_numbers	id
123456	1
564789	2
564747	3
545884	3

I have tried the below query but it is not working:

select s.phone_numbers
from (
        select id,phone_numbers from sales_order_details,
         lateral jsonb_array_elements(phone_numbers) e
     ) s
group by s.phone_numbers

CodePudding user response：

No need to nest queries:

select pn.phone_number,
       ui.id
from user_info ui 
  cross join jsonb_array_elements_text(ui.phone_numbers) as pn(phone_number)
order by ui.id

Online example

CodePudding user response：

Not sure what you're trying to achieve with the outer group by, but the following gives you the raw data

select id,e.value phone_number from user_info,
lateral jsonb_array_elements(phone_numbers) e