PSQL-修改JSONB元素

Question

我有一个jsonb对象，如下所示：

{
"members": [
[
"1966-07-31",
null,
{
"last_name": "ss",
"first_name": "ss"
}
],
[
"1968-12-17",
"spouse",
{
"last_name": "kk",
"first_name": "kk"
}
]
]
}

我想把它转换成

{
"applicants": [
{
"last_name": "ss",
"first_name": "ss"
}
{
"last_name": "kk",
"first_name": "kk"
}
]
}

从本质上讲，取每个成员的第三个元素，并将一个对象放入一个新数组"申请人"中。我不需要对象外部的成员数据。

我可以运行一个PHP脚本来遍历所有行并更新它。但我想知道我是否可以用普通的sql查询来写这个？

谢谢。

Answer 1

您应该使用jsonb_array_elements和两个CROSS JOIN来提取JSON数组数据，然后聚合它们

动态阵列：

演示

select
jsonb_build_object('applicants', jsonb_agg(e2.value))
from
test t
cross join jsonb_array_elements(t.obj -> 'members') as e1
cross join jsonb_array_elements(e1.value) as e2
where 
e2.value ? 'first_name'
and e2.value ? 'last_name'

---

静态阵列：

如果你的结构是特定的，你不需要在数组上循环，可以使用下面的查询：

演示

select
jsonb_build_object('applicants', jsonb_agg(e1.value -> 2))
from
test t
cross join jsonb_array_elements(t.obj -> 'members') as e1