PostgreSQL 嵌套 JSON 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24944347/
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
PostgreSQL Nested JSON Querying
提问by ravishi
On PostgreSQL 9.3.4, I have a JSON type column called "person" and the data stored in it is in the format {dogs: [{breed: <>, name: <>}, {breed: <>, name: <>}]}. I want to retrieve the breed of dog at index 0. Here are the two queries I ran:
在 PostgreSQL 9.3.4 上，我有一个名为“person”的 JSON 类型列，其中存储的数据格式为{dogs: [{breed: <>, name: <>}, {breed: <>, name: <>}]}. 我想在索引 0 处检索狗的品种。这是我运行的两个查询：
Doesn't work
不起作用
db=> select person->'dogs'->>0->'breed' from people where id = 77;
ERROR: operator does not exist: text -> unknown
LINE 1: select person->'dogs'->>0->'bree...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Works
作品
select (person->'dogs'->>0)::json->'breed' from es_config_app_solutiondraft where id = 77;
?column?
-----------
"westie"
(1 row)
Why is the type casting necessary? Isn't it inefficient? Am I doing something wrong or is this necessary for postgres JSON support?
为什么需要类型转换？是不是效率低下？我做错了什么，或者这对于 postgres JSON 支持是必要的吗？
回答by max taldykin
This is because operator ->>gets JSON array element as text. You need a cast to convert its result back to JSON.
这是因为 operator->>将 JSON 数组元素作为文本获取。您需要进行强制转换才能将其结果转换回 JSON。
You can eliminate this redundant cast by using operator ->:
您可以使用 operator 消除这种多余的演员->：
select person->'dogs'->0->'breed' from people where id = 77;
