how to parse json using json_populate_recordset in postgres
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 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
no need to create a new type for that.
select * from json_populate_recordset(null::record,'[{"id_item":1,"id_menu":"34"},{"id_item":2,"id_menu":"35"}]')
AS
(
id_item int
, id_menu int
)