时间戳

首页 > 解决方案 > 从 jsonb 获取最后一个元素 - PostgreSQL

sql - 从 jsonb 获取最后一个元素 - PostgreSQL

问题描述

我需要为每个不为空的 id 找到最后一个列表名

表创建和插入（示例数据）

CREATE TABLE temp.transfers(id varchar(20), lists jsonb);

INSERT INTO temp.transfers(id, lists)
values ( 'id1', '[
    {"listinput": "input1", "listname": "name1"},
    {"listinput": "input2", "listname": "name2"}
]'::jsonb);

INSERT INTO temp.transfers(id, lists)
values ( 'id2', '[
    {"listinput": "input3", "listname": "name3"},
    {"listinput": "input4", "listname": "name4"},
    {"listinput": "NULL", "listname":"name5"}
]'::jsonb);

失败的尝试

在这里尝试使用 groupby 和 array_agg 函数，但无法弄清楚。

select id,
jsonb_array_elements_text(jsonb_path_query_array(lists, '$.listinput')) as listinput
from temp.transfers;

id      listinput
"id1"   "input1"
"id1"   "input2"
"id2"   "input3"
"id2"   "input4"
"id2"   "NULL"

select id,
jsonb_path_query_array(lists, '$.listinput') as listinput
from temp.transfers;


id      listinput
"id1"    ["input1","input2"]
"id2"    ["input3","input4", "NULL"]

期望的输出：

id      listinput
"id1"    input2
"id2"    input4

标签： sqlpostgresql

解决方案

您可以提取数组的元素及其位置。然后基本上使用distinct on：

select distinct on (t.id) t.id, j.list->>'listname'
from transfers t cross join lateral
     jsonb_array_elements(t.lists) with ordinality j(list, n)
where j.list->>'listinput' is not null
order by t.id, j.n desc;

这是一个 db<>fiddle。

推荐阅读