PostgreSQL嵌套JSON查询 [英] PostgreSQL Nested JSON Querying
问题描述
在PostgreSQL 9.3.4上,我有一个名为"person"的JSON类型列,并且其中存储的数据的格式为{dogs: [{breed: <>, name: <>}, {breed: <>, name: <>}]}
.我想检索索引为0的那只狗.这是我运行的两个查询:
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:
不起作用
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支持有必要?
Why is the type casting necessary? Isn't it inefficient? Am I doing something wrong or is this necessary for postgres JSON support?
推荐答案
这是因为运算符->>
获取JSON数组元素作为文本.您需要强制转换以将其结果转换回JSON.
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;
这篇关于PostgreSQL嵌套JSON查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!