PostgreSQL dynamically SELECT last json array value

Question

How do I dynamically SELECT the last array value in a JSON type column in PostgreSQL?

id: 2927
material_size: [".016 x 1.156","0","1"]

I could statically retrieve the last value "1" using:

 SELECT material_size->>2 AS material_size FROM table_name;

However that would always get the second record instead of the last record (if the number of values isn't exactly three).

Answer 1

You can get the length of the JSON array and use that (minus one) as the index:

with t1 as (select 2927 id, '[".016 x 1.156","0","1"]'::json material_size)
select material_size->>(json_array_length(material_size)-1) from t1;

See http://www.postgresql.org/docs/9.4/static/functions-json.html.

Answer 2

You can use negative index to query elements in a JSON value from the end:

 SELECT material_size->>-1 AS material_size FROM table_name;