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!

Do you always have a simple uniform JSON structure? Because a simple, inelegant trick would be to find a substring. — David Manheim
– David Manheim, Commented Aug 6, 2014 at 21:49
The json structure will need more information than the one I provided. It was more illustrative than anything else, hoping to have an easy answer. I am interested on how you could handle it using substring. Thanks! — Nano Documet
– Nano Documet, Commented Aug 6, 2014 at 22:01
You can use Regex's in your SQL on strings, and treat your JSON as a string: postgresql.org/docs/9.1/static/functions-matching.html - if you know that the JSON ends with }]}, you can find the element that precedes that - it's messy, and PostgreSQL has better ways of handling it. — David Manheim
– David Manheim, Commented Aug 6, 2014 at 22:10

ig0774 · Accepted Answer · 2014-08-06 22:07:28Z

22

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...

answered Aug 6, 2014 at 22:07

ig0774

41.6k3 gold badges61 silver badges58 bronze badges

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

1 Comment

For me, the function name had to be caps for it to work (windows, perhaps?) JSONB_ARRAY_LENGTH/JSON_ARRAY_LENGTH

Martin · Accepted Answer · 2019-10-11 08:09:51Z

4

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;

answered Oct 11, 2019 at 8:09

Martin

413 bronze badges