Getting last element from jsonb - PostgreSQL

Question

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

Gordon Linoff · Accepted Answer · 2021-08-28 15:40:05Z

1

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.

edited Aug 28, 2021 at 15:40

answered Aug 28, 2021 at 12:10

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Suraj Over a year ago

Thanks, I was able to easily modify your solution for the original table.

Collectives™ on Stack Overflow

Getting last element from jsonb - PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related