如何从 PostgreSQL 中的 Json 数组中获取元素
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27834482/
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
How to get elements from Json array in PostgreSQL
提问by Mital Pritmani
I have searched quite much on this and still unanswerable. I'm using PostgreSQL. Column name is "sections" and column type is json[] in below example.
我已经对此进行了很多搜索，但仍然无法回答。我正在使用 PostgreSQL。在下面的示例中，列名称是“部分”，列类型是 json[]。
My column looks like this in database:
我的专栏在数据库中看起来像这样：
sections
[{"name" : "section1",
"attributes": [{"attrkey1": "value1",
"attrkey2": "value2"},
{"attrkey3": "value3",
"attrkey4": "value4"}]
},
{"name" : "section2",
"attributes": [{"attrkey3": "value5",
"attrkey6": "value6"},
{"attrkey1": "value7",
"attrkey8": "value8"}]
}]
It's json array and I want to get "attrkey3" in my result. For getting particular key from Json, I can use json_extract_path_text(json_column, 'json_property')which is working perfectly fine. But I have no idea how to get some property from json[].
它是 json 数组，我想在我的结果中得到“attrkey3”。为了从 Json 获取特定的密钥，我可以使用json_extract_path_text(json_column, 'json_property')它工作得很好。但我不知道如何从 json[] 获取一些属性。
If I talk about above example, I want to get value of property "attrkey2" to be shown in my result. I know it's an array so it might work differently than usual, e.g. all the values of my array would act as a different row so I might have to write subquery but no idea how to do it.
如果我谈论上面的例子，我想在我的结果中显示属性“attrkey2”的值。我知道它是一个数组，所以它的工作方式可能与平时不同，例如，我的数组的所有值都将充当不同的行，因此我可能必须编写子查询，但不知道该怎么做。
Also, I can't write index statically and get property of the json element from some particular index. My query will be generated dynamically so I would never know how many elements are inside json array.
此外，我无法静态写入索引并从某个特定索引获取 json 元素的属性。我的查询将动态生成，所以我永远不会知道 json 数组中有多少元素。
I saw some static examples but don't know how to implement it in my case. Can someone tell me how to do this in query?
我看到了一些静态示例，但不知道如何在我的情况下实现它。有人可以告诉我如何在查询中做到这一点吗？
回答by pozs
I'm not sure you have a json[](PostgreSQL array of jsonvalues) typed column, or a jsontyped column, which appears to be a JSON array (like in your example).
我不确定你有一个json[]（PostgreSQLjson值数组）类型的列，或者一个json类型的列，它似乎是一个 JSON 数组（就像你的例子一样）。
Either case, you need to expand your array before querying. In case of json[], you need to use unnest(anyarray); in case of JSON arrays in a jsontyped column, you need to use json_array_elements(json)(and LATERALjoins -- they are implicit in my examples):
无论哪种情况，您都需要在查询之前扩展您的数组。在这种情况下json[]，您需要使用unnest(anyarray); 对于json类型列中的 JSON 数组，您需要使用json_array_elements(json)（和LATERAL连接——它们在我的示例中是隐式的）：
select t.id,
each_section ->> 'name' section_name,
each_attribute ->> 'attrkey3' attrkey3
from t
cross join unnest(array_of_json) each_section
cross join json_array_elements(each_section -> 'attributes') each_attribute
where (each_attribute -> 'attrkey3') is not null;
-- use "where each_attribute ? 'attrkey3'" in case of jsonb
select t.id,
each_section ->> 'name' section_name,
each_attribute ->> 'attrkey3' attrkey3
from t
cross join json_array_elements(json_array) each_section
cross join json_array_elements(each_section -> 'attributes') each_attribute
where (each_attribute -> 'attrkey3') is not null;
Unfortunately, you cannot use any index with your data. You need to fix your schema first, in order to do that.
不幸的是，您不能对您的数据使用任何索引。您需要先修复您的架构，才能做到这一点。
回答by memomaster
And also, if there was a key value map data in array:
而且，如果有一个键值映射数据array：
select each_data -> 'value' as value3
from t cross join jsonb_array_elements(t.sections -> 'attributes') each_attribute
where each_attribute -> 'key' = '"attrkey3"'
I am mentioning this because the great answer also provided a perfect solution for my case. By the way, also be aware of jsonb_array.. method for jsonbtype attribute.
我之所以提到这一点，是因为出色的答案也为我的案例提供了完美的解决方案。顺便说一下，还要注意类型属性的jsonb_array.. 方法jsonb。
