Query last element in array from json structure in postgres

Question

Let's say that we have the following

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

How can I get the last element from the array in the given json structure? Getting the first one seems not that complicated

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+ one can now use negative subscripts to achieve this.

For your case above, getting the last element could be achieved by:

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