How to get JSONB data in additional columns from Postgres

Question

I have a table from which I want to fetch two fields of which one is JSONB with JSONB values in to new columns.

SELECT ID, ATTRIBS from EMPLOYEE;

This returns me the output as follows.

   id  |                attribs
------------------------------------------------------------------------------------
 EM001 | {"Education": "C.A.", "Contact No": "6655448822", "Relative Name": "Ganga"}
 EM002 | {"Education": "M.E.", "Contact No": "6542349992", "Relative Name": "Yamuna"}

I would like to have the output as follows

   id  | Education | Contact No | Relative Name
-----------------------------------------------
 EM001 | C.A.      | 6655448822 | Ganga
 EM002 | M.E.      | 6542349992 | Yamuna

Any suggestions how I can do this?

user330315 · Accepted Answer · 2018-10-06 09:09:41Z

1

Use the ->> operator to extract values based on a key:

select id, 
       attribs ->> 'Education' as education,
       attribs ->> 'Contact No' as "Contact No",
       attribs ->> 'Relative Name' as "Relative Name"
from the_table

answered Oct 6, 2018 at 9:09

user330315

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

Collectives™ on Stack Overflow

How to get JSONB data in additional columns from Postgres

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related