current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 2 down vote favorite

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

share|improve this question

1 Answer 1

up vote 4 down vote

The first argument passed to pgsql function json_populate_recordsetshould be a row type. Either 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, or, 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 null is the default value to insert into table columns not set in the json passed.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.