Question 115121 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Convert Jsonb simple array into column group by

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
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search