postgresql json 数组查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45743778/
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 json array query
提问by Armando Perea
I tried to query my json array using the example here: How do I query using fields inside the new PostgreSQL JSON datatype?
我尝试使用以下示例查询我的 json 数组：How do I query using fields inside the new PostgreSQL JSON datatype?
They use the example:
他们使用以下示例：
SELECT *
FROM json_array_elements(
'[{"name": "Toby", "occupation": "Software Engineer"},
{"name": "Zaphod", "occupation": "Galactic President"} ]'
) AS elem
WHERE elem->>'name' = 'Toby';
But my Json array looks more like this (if using the example):
但是我的 Json 数组看起来更像这样（如果使用示例）：
{
"people": [{
"name": "Toby",
"occupation": "Software Engineer"
},
{
"name": "Zaphod",
"occupation": "Galactic President"
}
]
}
But I get an error: ERROR: cannot call json_array_elements on a non-array
但我收到一个错误：错误：无法在非数组上调用 json_array_elements
Is my Json "array" not really an array? I have to use this Json string because it's contained in a database, so I would have to tell them to fix it if it's not an array. Or, is there another way to query it?
我的 Json“数组”不是真正的数组吗？我必须使用这个 Json 字符串，因为它包含在一个数据库中，所以如果它不是一个数组，我将不得不告诉他们修复它。或者，还有其他查询方式吗？
I read documentation but nothing worked, kept getting errors.
我阅读了文档，但没有任何效果，不断出现错误。
回答by klin
The json array has a key peopleso use my_json->'people'in the function:
json 数组有一个键，people因此my_json->'people'在函数中使用：
with my_table(my_json) as (
values(
'{
"people": [
{
"name": "Toby",
"occupation": "Software Engineer"
},
{
"name": "Zaphod",
"occupation": "Galactic President"
}
]
}'::json)
)
select t.*
from my_table t,
json_array_elements(my_json->'people') elem
where elem->>'name' = 'Toby';
The function json_array_elements()unnests the json array and generates all its elements as rows:
该函数json_array_elements()取消嵌套 json 数组并将其所有元素生成为行：
select elem->>'name' as name, elem->>'occupation' as occupation
from my_table t,
json_array_elements(my_json->'people') elem
name | occupation
--------+--------------------
Toby | Software Engineer
Zaphod | Galactic President
(2 rows)
If you are interested in Toby's occupation:
如果您对托比的职业感兴趣：
select elem->>'occupation' as occupation
from my_table t,
json_array_elements(my_json->'people') elem
where elem->>'name' = 'Toby'
occupation
-------------------
Software Engineer
(1 row)
