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
Best Answer
The first argument passed to pgsql function
json_populate_recordset
should be a row type. If you want to use the json array to populate the existing tableanoop
you can simply pass the tableanoop
as the row type like this: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
: