如何在 postgresql 中解析 JSON
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32626261/
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 in postgresql
提问by Ivan Ursul
I have a table in my database, which contains character varying column and this column has json. I need to write a query, which will somehow parse this json into separate columns.
我的数据库中有一个表，其中包含字符变化列，该列包含 json。我需要编写一个查询，它将以某种方式将此 json 解析为单独的列。
I found json_eachfunction herebut I can't understand how to work with it.
回答by Ivan Ursul
I figured it out, guys
我想通了，伙计们
if I have a table books
如果我有桌书
I can easily write a query
我可以轻松编写查询
SELECT
id,
data::json->'name' as name
FROM books;
And it will result in
它会导致
I can also try to get non-existent column
我也可以尝试获取不存在的列
SELECT
id,
data::json->'non_existant' as non_existant
FROM books;
And it this case I will get empty result
在这种情况下，我会得到空结果
回答by hit3k
Awesome, thanks for sharing. I found that you can go deeper like:
厉害了，谢谢分享。我发现你可以更深入：
SELECT
id,
data::json->'name' as name,
data::json->'author' ->> 'last_name' as author
FROM books;
