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

Nate Anderson · Accepted Answer · 2019-02-03 20:15:54Z

57

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

edited Feb 3, 2019 at 20:15

Nate Anderson

21.7k22 gold badges113 silver badges155 bronze badges

answered Nov 4, 2014 at 18:28

jediKnight

7966 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wellspring Over a year ago

This is the first answer I've found that unpacks null::anoop in plain English. Thanks! That said, I don't understand the value of the null, however, as most records have many data types. Aside from null, what other defaults could we provide for a record expecting strings, uuid and ints?

Inder · Accepted Answer · 2019-03-15 17:57:21Z

23

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
 )

edited Mar 15, 2019 at 17:57

Inder

3,8369 gold badges30 silver badges43 bronze badges

answered Mar 15, 2019 at 17:28

Yarex

2492 silver badges5 bronze badges

3 Comments

Souvik Over a year ago

If I query from Table what would be the syntax for that without using create type

Nishant Ghodke Over a year ago

ERROR: record type has not been registered

RoK Over a year ago

Can even be easier: select * from jsonb_to_recordset('[{"id_item":1,"id_menu":"34"},{"id_item":2,"id_menu":"35"}]') AS ( id_item int , id_menu int )

Collectives™ on Stack Overflow

how to parse json using json_populate_recordset in postgres

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related