postgresql 如何在 postgres 中使用 json_populate_recordset 解析 json
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25785575/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to parse json using json_populate_recordset in postgres
提问by kenthewala
I have a json stored as text in one of my database row. the json data is as following
我有一个 json 作为文本存储在我的数据库行之一中。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"}]
to parse this i want to use postgresql method
解析这个我想使用 postgresql 方法
json_populate_recordset()
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
它给了我以下错误 json_populate_recordset 的第一个参数必须是行类型
note : in the from clause "anoop" is the table name.
注意：在 from 子句中，“anoop”是表名。
can anyone suggest me how to use the json_populate_recordset method to extract data from this json string.
谁能建议我如何使用 json_populate_recordset 方法从这个 json 字符串中提取数据。
I got method's reference from http://www.postgresql.org/docs/9.3/static/functions-json.html
我从http://www.postgresql.org/docs/9.3/static/functions-json.html得到了方法的参考
回答by jediKnight
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 anoopyou can simply pass the table anoopas the row type like this:
传递给 pgsql 函数的第一个参数json_populate_recordset应该是行类型。如果您想使用 json 数组来填充现有表，anoop您可以简单地将表anoop作为行类型传递，如下所示：
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 nullis the default value to insert into table columns not set in the json passed.
这null是插入到未在传递的 json 中设置的表列中的默认值。
If you don't have an existing table, you need to create a row typeto hold your json data (ie. column
names and their types) and pass it as the first parameter, like this anoop_type:
如果您没有现有表，则需要创建一个行类型来保存您的 json 数据（即列名及其类型）并将其作为第一个参数传递，如下所示anoop_type：
create TYPE anoop_type AS (id int, name varchar(100));
select * from json_populate_recordset(null :: anoop_type,
'[...]') --same as above
回答by Yarex
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
)
