JZ. JZ. - 12 days ago 5
JSON Question

Find last element of array in JSON column type

The following is an example of data stored as data type

json
in Postgres 9.4.4:

[
1479772800000,
70.12
],
[
1479859200000,
70.83
],
[
1480032000000,
71.23
]


How can I select the last element of this array:
[1480032000000,71.23]


\d stocks
Table "public.stocks"
Column | Type | Modifiers
------------------+----------------------+-----------------------------------------------------
id | integer | not null default nextval('stocks_id_seq'::regclass)
ticker | character varying(6) |
price_history | json |
Indexes:
"stocks_pkey" PRIMARY KEY, btree (id)
"ix_stocks_ticker" UNIQUE, btree (ticker)

Answer

You can obtain the last element of a Postgres JSON array by doing (known to work in Postgres 9.5+):

select '...'::json->-1;

This uses the -> operator. Negative integers count from the end.

For example:

select '[[1479772800000, 70.12], [1479859200000, 70.83], [1480032000000, 71.23]]'::json->-1;