Split a JSON column in postgres sql

Question

I have a table with a column that has JSON value. This column is so clustered and unable to get detailed information from the column.

   Company     User
    A   [{"Name":"Mark","email":"mark.com","password":"password"},{"Name":"Keith","email":"Keith.com","password":"password"}]
    B   [{"Name":"Anna","email":"anna.com","password":"password"},{"Name":"Lewis","email":"Lewis.com","password":"password"}]
    C   [{"Name":"Bill","email":"bill.com","password":"password"},{"Name":"Stuart","email":"stuart.com","password":"password"}]

Is there any way we can break the column as below in a select statement

Company User_1_name User_1_email User_1_password User_2_name User_2_email User_2_password
A         Mark      mark.com       password        Keith      Keith.com     password
B         Anna      anna.com       password        Lewis      Lewis.com     password
C         Bill      bill.com       password        Stuart     stuart.com    password

What is the datatype of the column? Is it text or is it jsonb? — Mike Organek
– Mike Organek, Commented Aug 19, 2020 at 11:43

Mike Organek · Accepted Answer · 2020-08-19 11:54:46Z

4

Since the column is not defined as jsonb or json per your comment, you will need to cast to make this work:

select company, 
       (user::jsonb)->0->>'Name' as user_1_name,
       (user::jsonb)->0->>'email' as user_1_email,
       (user::jsonb)->0->>'password' as user_1_password,       
       (user::jsonb)->1->>'Name' as user_2_name,
       (user::jsonb)->1->>'email' as user_2_email,
       (user::jsonb)->1->>'password' as user_2_password
  from your_table;

If the number of users per row varies, then you will need to add as many columns as necessary to capture the maximum size.

If any of the rows has invalid json in the user column, then this will not work.

edited Aug 19, 2020 at 11:54

answered Aug 19, 2020 at 11:47

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

Split a JSON column in postgres sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related