CODEWITHSUNDEEP
sqlpostgresql
Suraj
Suraj

Reputation: 2477

Getting last element from jsonb - PostgreSQL

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions