I can not extract data from a JSON type column in PostgreSQL

Question

I want to extract data from a json type column to insert them into a table in order to normalize a database.

The JSON type column is called "info" and an example of a record is the following:

[ { "major" : "International business", 
    "end" : "2007", 
    "name" : "Annamalai University",
    "degree" : "Master Degree", 
    "start" : "2005", "desc" : "" 
  }, 
  { "major" : "Mechanical Engineering", 
    "end" : "1990", 
    "name" : "Bharathidasan University", 
    "degree" : "Bachelor Degree", 
    "start" : "1990", "desc" : "" 
  }
]

This is my code:

SELECT id,
       (json_array_elements(info)->>'education')::json ->> 'key' AS key1 
FROM perfiles
WHERE id = 1252710;

This is the result I want to obtain: table result example

How should I do the query?

Thanks in advance

Kaushik Nayak · Accepted Answer · 2019-02-28 17:01:09Z

1

You may use cross join lateral with json_array_elements and list the elements in the select

SELECT  p.id, 
           j->>'major'::text AS major, 
           (j->>'end')::int  AS "end", 
           j->>'name'        AS NAME, 
           j->>'degree'      AS degree, 
           j->>'start'       AS start, 
           j->>'desc'        AS "desc" 
FROM  perfiles p 
CROSS JOIN LATERAL json_array_elements(info) AS j

Or use json_to_recordset by specifying column list in the FROM clause

select p.id,
j.* FROM   perfiles p
  cross join lateral json_to_recordset(info) 
as j(major text, "end" int, name text, degree text, start int, "desc" text);

Demo

answered Feb 28, 2019 at 17:01

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

1 Comment

Saul Over a year ago

Thanks the 3 answers were very Useful! Thank you!

Zaynul Abadin Tuhin · Accepted Answer · 2019-02-28 16:56:47Z

0

use json_to_recordset

    SELECT  x.*
FROM pjson_table
   , json_to_recordset(myjson::json) x 
        (  major text
         , "end" text
         , name text
         , degree text
         , start text
         ,"desc" text
        )

demo link

 major                  end      name                   degree         start
International business  2007    Annamalai University    Master Degree   2005
Mechanical Engineering  1990    Bharathidasan University    Bachelor Degree 1990

edited Feb 28, 2019 at 16:56

answered Feb 28, 2019 at 16:49

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Comments

Ivan Pivkin · Accepted Answer · 2019-02-28 16:16:57Z

0

Try something like this

select *
from (
  select
  json_to_recordset(info) as ed(major text, end int, name text, degree text, start int, desc text)
  from perfiles 
  where id = 1252710
)

Ref: https://www.postgresql.org/docs/9.5/functions-json.html

answered Feb 28, 2019 at 16:16

Ivan Pivkin

1012 bronze badges

Collectives™ on Stack Overflow

I can not extract data from a JSON type column in PostgreSQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related