Reputation: 2477
I'm required to find the last listname for each id that is not null
Table creation and insertion (Sample data)
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);
Failed attempts
Tried using groupby and array_agg functions here, but couldn't figure it out.
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"]
Desired output :
id listinput
"id1" input2
"id2" input4
Upvotes: 1
Views: 256
Reputation: 1271231
You can extract the elements of the array with their position. Then basically use 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;
Here is a db<>fiddle.
Upvotes: 1