如何从PostgreSQL中的Json数组中获取元素

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。在下面的示例中，列名称为“sections”，列类型为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?

我看到了一些静态示例，但在我的情况下不知道如何实现它。有人能告诉我如何在查询中执行此操作吗？

2 个解决方案

#1

I'm not sure you have a json[] (PostgreSQL array of json values) typed column, or a json typed column, which appears to be a JSON array (like in your example).

我不确定你有一个json []（json值的PostgreSQL数组）类型列，或者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 json typed column, you need to use json_array_elements(json) (and LATERAL joins -- they are implicit in my examples):

无论哪种情况，您都需要在查询之前扩展阵列。在json []的情况下，你需要使用unexst（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;

SQLFiddle

Unfortunately, you cannot use any index with your data. You need to fix your schema first, in order to do that.

不幸的是，您不能对数据使用任何索引。您需要先修复架构，才能执行此操作。

#2

And also, if there was a key value map data in 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 jsonb type attribute.

我提到这一点是因为这个好的答案也为我的案子提供了一个完美的解决方案。顺便说一下，还要注意jsonb_array ..方法的jsonb类型属性。

#1