Request last element in array from json structure in postgres

Question

Say we have the following

{ "items" :
    [
        {"id": 1},
        {"id": 2},
        {"id": 3}
    ]
}

How can I get the last element from an array in a given json structure? Getting the first one seems not so difficult

SELECT t.column->'items'->0 AS elem
FROM   tbl t
WHERE  other_column = 20;

Thanks in advance!

Answer 1

Something like this should get the last element of your example:

SELECT t.col->'items'->(json_array_length(t.col->'items')-1)
FROM   tbl t

SQLFiddle showing this in action ...

Answer 2

In Postgres 9. 5+, you can now use negative subscriptions to achieve this.

In the above case, getting the last item can be achieved with:

SELECT t.column->'items'->-1 AS elem
FROM   tbl t
WHERE  other_column = 20;