如何获得PostgreSQL中从JSON数组元素

问题描述：

我搜索过此颇多，仍然无法回答。我使用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.

我的专栏看起来像这样在数据库：

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"}]
 }]

这是JSON数组，我想获得attrkey3在我的结果。从Json的获取特定的键，我可以使用 json_extract_path_text（json_column，'json_property'）这是工作完全正常。但我不知道如何从JSON某些属性[]。

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[].

如果我讲上面的例子，我想获得的财产attrkey2在我的结果中显示的值。我知道这是一个数组，因此可能会有所改变比平时，例如我的阵列将作为不同行的所有值，所以我可能写子查询，但不知道该怎么做。

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.

另外，我不能写静态指标和一些特定的索引得到JSON元素的属性。我的查询将被动态生成的，所以我永远不会知道有多少元素都在里面JSON阵列。

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.

我看到一些静态的例子，但不知道如何实现它在我的情况。谁能告诉我如何在查询中这样做吗？

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?

答

我不知道你有一个 json的[] （PostgreSQL的数组 JSON 值）类型的列或 JSON 键入列，这似乎是一个JSON阵列（比如在你的例子）。

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的[] ，你需要使用的 UNNEST（anyarray的） ;在一个 JSON 键入列JSON数组的情况下，你需要使用的 json_array_elements（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):

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.

如何获得PostgreSQL中从JSON数组元素

相关推荐