CODEWITHSUNDEEP
sqljsonpostgresql
Venkata Subbarao
Venkata Subbarao

Reputation: 420

How to get JSONB data in additional columns from Postgres

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?

Upvotes: 0

Views: 28

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions