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

Question

我已经对此进行了很多搜索，但仍然无法回答。我正在使用 PostgreSQL。在下面的示例中，列名称是“sections”，列类型是 json[]。

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

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 获取特定 key ，我可以使用 json_extract_path_text(json_column, 'json_property') ，它工作得很好。但我不知道如何从 json[] 获取一些属性。

如果我谈论上面的例子，我想获得属性“attrkey2”的值以显示在我的结果中。我知道它是一个数组，所以它的工作方式可能与平常不同，例如我的数组的所有值都将充当不同的行，因此我可能不得不编写子查询但不知道该怎么做。

此外，我无法静态编写索引并从某个特定索引获取 json 元素的属性。我的查询将动态生成，因此我永远不知道 json 数组中有多少元素。

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

Answer 1

我不确定您是否有 json[](json 值的 PostgreSQL 数组)类型列，或 json 类型列，这似乎是一个 JSON 数组(就像在您的示例中一样)。

无论哪种情况，您都需要在查询之前扩展您的数组。对于 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;

SQLFiddle

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