具有数组值的Postgres 12 JSONB键选择
具有数组值的Postgres 12 JSONB键选择
提问于 2021-06-29 02:46:19
我有一个类型为JSONB的数据库表的列，我希望从该列中获得一些数据。在大多数情况下，该列是键\值对的平面列表。
例如：
{ s_key: 'value', s_key1: 'value', s_key2: 'value' ...etc }但是，我想要的键包含一个JSON数据数组(也可以是null/nil)。所以桌子应该是这样的：
id: ,
data: {
s_key: 'value',
s_key1: 'value',
board_members: [
{first_name: 'Hugo', last_name: 'Grant', ind: true },
{first_name: 'Larry', last_name: 'Larson', ind: false },
{first_name: 'Rick', last_name: 'Flair', ind: 'true' } ]
}
created_at: 现在，我想要做的是有一个子选择，它给我基于first_name(不管它是否为真/‘真’)的串接的名称字符串(last_name+ ind )。所以，我想要一个输出：
[ 'Hugo Grant', 'Rick Flair' ]我已经在一定程度上通过这个PSQL片段实现了这个目标：
select t.id, array_agg(t._name) as _board
from (
select
d.id,
jsonb_extract_path_text(jsonb_array_elements(
case jsonb_extract_path(d.data, 'board_members')
when 'null' then '[{}]'::jsonb
else jsonb_extract_path(d.data, 'board_members')
end
), 'first_name') || ' ' || jsonb_extract_path_text(jsonb_array_elements(
case jsonb_extract_path(d.data, 'board_members')
when 'null' then '[{}]'::jsonb
else jsonb_extract_path(d.data, 'board_members')
end
), 'last_name') as _name
from my_table d
group by d.id
) t
group by t.id有办法简化SQL语句吗？
回答 1
Database Administration用户
回答已采纳
发布于 2021-06-29 13:48:21
可以使用jsonb_path_query_array获取所有匹配的数组元素：
jsonb_path_query_array(data, '$.board_members[*] ? (@.ind == true)')上述回报
[
{"ind": true, "last_name": "Grant", "first_name": "Hugo"},
{"ind": true, "last_name": "Flair", "first_name": "Rick"}
]你的样本数据。
要获得连接的第一个/最后一个名称，您需要取消数组并将名称聚合回来。
select id,
(select jsonb_agg(concat_ws(' ', p.item ->> 'first_name', p.item ->> 'last_name'))
from jsonb_array_elements(jsonb_path_query_array(data, '$.board_members[*] ? (@.ind == true)')) as p(item)) as names
from my_table上面的内容在["Hugo Grant", "Rick Flair"]列中返回names
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/294966
复制相关文章
相似问题
腾讯云开发者
