how to parse json using json_populate_recordset in postgres

Question

I have a json stored as text in one of my database row. the json data is as following

[{"id":67272,"name":"EE_Quick_Changes_J_UTP.xlsx"},{"id":67273,"name":"16167.txt"},{"id":67274,"name":"EE_12_09_2013_Bcum_Searchall.png"}] 

to parse this i want to use postgresql method

json_populate_recordset()

when I post a command like

select json_populate_recordset(null::json,'[{"id":67272,"name":"EE_Quick_Changes_J_UTP.xlsx"},{"id":67273,"name":"16167.txt"},{"id":67274,"name":"EE_12_09_2013_Bcum_Searchall.png"}]') from anoop; 

it gives me following error first argument of json_populate_recordset must be a row type

note : in the from clause "anoop" is the table name.

can anyone suggest me how to use the json_populate_recordset method to extract data from this json string.

I got method's reference from http://www.postgresql.org/docs/9.3/static/functions-json.html

Answer 1

The first argument passed to pgsql function json_populate_recordsetshould be a row type. If you want to use the json array to populate the existing table anoop you can simply pass the table anoop as the row type like this:

insert into anoop select * from json_populate_recordset(null::anoop,          '[{"id":67272,"name":"EE_Quick_Changes_J_UTP.xlsx"},           {"id":67273,"name":"16167.txt"},           {"id":67274,"name":"EE_12_09_2013_Bcum_Searchall.png"}]'); 

Here the null is the default value to insert into table columns not set in the json passed.

If you don't have an existing table, you need to create a row type to hold your json data (ie. column names and their types) and pass it as the first parameter, like this anoop_type:

create TYPE anoop_type AS (id int, name varchar(100)); select * from json_populate_recordset(null :: anoop_type,          '[...]') --same as above