嵌套 JSON 查询

在 PostgreSQL 9.3.4上，我有一个名为“ person”的 JSON 类型列，其中存储的数据格式为 {dogs: [{breed: <>, name: <>}, {breed: <>, name: <>}]}。我要检索索引为0的狗的品种。下面是我运行的两个查询:

没用的

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.

工程

select (person->'dogs'->>0)::json->'breed' from es_config_app_solutiondraft where id = 77;
?column?
-----------
"westie"
(1 row)

为什么需要类型铸造？效率不是很低吗？是我做错了什么，还是这对 postgres JSON 支持是必要的？

82297 次浏览

小开

最佳答案

This is because operator ->> gets JSON array element as text. You need a cast to convert its result back to JSON.

You can eliminate this redundant cast by using operator ->:

select person->'dogs'->0->'breed' from people where id = 77;