PostgreSQL嵌套JSON查询

Question

在PostgreSQL 9.3.4上，我有一个称为“ person”的JSON类型列，并且其中存储的数据为format {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支持有必要？

Answer 1

这是因为运算符->>将JSON数组元素获取为文本。您需要强制转换以将其结果转换回JSON。

您可以使用operator消除此多余的转换->：

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