如何在 postgresql 9.3 中循环遍历 JSON 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20272650/
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 loop over JSON Arrays in postgresql 9.3
提问by Valerio
I'm writing function for a new postgreSQL db and i'm trying to loop over a nested structure.
我正在为一个新的 postgreSQL 数据库编写函数，我正在尝试遍历嵌套结构。
Is that even possible with the new JSON functions? What i'm trying to do is here below:
使用新的 JSON 函数甚至可能吗？我想要做的是下面：
DO
$BODY$
DECLARE
omgjson json := '[{ "type": false }, { "type": "photo" }, {"type": "comment" }]';
i record;
BEGIN
FOR i IN SELECT * FROM json_array_elements(omgjson)
LOOP
RAISE NOTICE 'output from space %', i;
END LOOP;
END;
$BODY$ language plpgsql
This returns a set of records (text!), that is not JSON! so i cannot query it like i->>'type', but that's exactly what i want to accomplish...
这将返回一组记录（文本！），这不是 JSON！所以我不能像这样查询它i->>'type'，但这正是我想要完成的......
回答by Valerio
I was a little dumb, but the documentation on this json feature on postgresql website is actually minimal
我有点笨，但是 postgresql 网站上关于这个 json 功能的文档实际上很少
to solve the problem all i did was
为了解决这个问题，我所做的就是
DO
$BODY$
DECLARE
omgjson json := '[{ "type": false }, { "type": "photo" }, {"type": "comment" }]';
i json;
BEGIN
FOR i IN SELECT * FROM json_array_elements(omgjson)
LOOP
RAISE NOTICE 'output from space %', i->>'type';
END LOOP;
END;
$BODY$ language plpgsql
