CODEWITHSUNDEEP
sqljsonpostgresql
Saul
Saul

Reputation: 3

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

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

Upvotes: 0

Views: 173

Answers (3)

Kaushik Nayak
Kaushik Nayak

Reputation: 31736

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

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

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

Upvotes: 0

Ivan Pivkin
Ivan Pivkin

Reputation: 101

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

Upvotes: 0

Related Questions