Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table perfiles as select  1252710 as id,
'[ { "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" : "" } ]'::json as info

SELECT 1


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


id
major
end
name
degree
start
desc


1252710
International business
2007
Annamalai University
Master Degree
2005


1252710
Mechanical Engineering
1990
Bharathidasan University
Bachelor Degree
1990




SELECT 2


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);


id
major
end
name
degree
start
desc


1252710
International business
2007
Annamalai University
Master Degree
2005


1252710
Mechanical Engineering
1990
Bharathidasan University
Bachelor Degree
1990




SELECT 2

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