Postgres jsonb.异构 json 字段

Question

如果我有一个带有单个jsonb 列的表，并且该表包含如下数据：

[{"body": {"project-id": "111"}},
{"body": {"my-org.project-id": "222"}},
{"body": {"other-org.project-id": "333"}}]

基本上，它为不同的行存储不同的项目ID。 现在我需要一个查询，其中来自不同行的data->'body'->'etc'.将合并为一个字段'project-id'，我该怎么做？

例如：如果我做这样的事情：

select data->'body'->'project-id' projectid from mytable

它将返回类似以下内容：

| projectid |
|       111 |

但是我也希望在其他行中使用project-id，但我不希望结果中有其他列。 即，我想要这个：

| projectid |
|       111 |
|       222 |
|       333 |

Answer 1

我知道您的每一行都包含一个 json 对象，其中包含一个嵌套对象，其键因行而异，并且您想要获取其值。

假设'body'始终只有一个键，您可以执行以下操作：

select jsonb_extract_path_text(t.js -> 'body', x.k) projectid
from t
cross join lateral jsonb_object_keys(t.js -> 'body') as x(k)

jsonb_object_keys()上的横向联接将对象中的所有键提取为行。然后我们使用jsonb_extract_path_text()来获取相应的值。

DB小提琴上的演示：

with t as ( 
select '{"body": {"project-id": "111"}}'::jsonb js
union all select '{"body": {"my-org.project-id": "222"}}'::jsonb
union all select '{"body": {"other-org.project-id": "333"}}'::jsonb
)
select jsonb_extract_path_text(t.js -> 'body', x.k) projectid
from t
cross join lateral jsonb_object_keys(t.js -> 'body') as x(k)

|

投影ID | |:--------- | |111 | |222 | |333 |