如何在postgres中使用json_populate_recordset解析json [英] how to parse json using json_populate_recordset in postgres
问题描述
我在数据库行之一中存储了一个以文本形式存储的json. json数据如下
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"}]
要对此进行解析,我想使用PostgreSQL方法
to parse this i want to use postgresql method
json_populate_recordset()
json_populate_recordset()
当我发布
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;
它给了我以下错误 json_populate_recordset的第一个参数必须为行类型
it gives me following error first argument of json_populate_recordset must be a row type
注意:from子句"anoop"中的是表名.
note : in the from clause "anoop" is the table name.
有人可以建议我如何使用json_populate_recordset方法从此json字符串中提取数据.
can anyone suggest me how to use the json_populate_recordset method to extract data from this json string.
我从中获得方法的参考 http://www.postgresql.org/docs/9.3/static/functions-json.html
I got method's reference from http://www.postgresql.org/docs/9.3/static/functions-json.html
推荐答案
传递给pgsql函数json_populate_recordset
的第一个参数应该是行类型.如果要使用json数组填充现有表anoop
,则只需将表anoop
作为行类型传递即可:
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"}]');
null
是要插入未在传递的json中设置的表列中的默认值.
Here the null
is the default value to insert into table columns not set in the json passed.
如果没有现有表,则需要创建行输入以保存您的json数据(即列
名称及其类型),并将其作为第一个参数传递,例如anoop_type
:
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
这篇关于如何在postgres中使用json_populate_recordset解析json的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!