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 pjson_table (myjson json);
INSERT INTO pjson_table  VALUES('[ { "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" : "" } ]')
1 rows affected

select * from pjson_table


myjson


[ { "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" : "" } ]


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


major
end
name
degree
start


International business
2007
Annamalai University
Master Degree
2005

Mechanical Engineering
1990
Bharathidasan University
Bachelor Degree
1990

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