CODEWITHSUNDEEP
arraysjsonpostgresql
JZ.
JZ.

Reputation: 21907

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)

Upvotes: 1

Views: 893

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659357

Postgres 9.5 would be preferable, like @Simeon provided.

You could use json_array_length() in Postgres 9.4 since negative indexes are not allowed for the -> operator.

SELECT j -> -1                       AS pg95
     , j -> json_array_length(j) - 1 AS pg94
FROM (SELECT '[[1479772800000, 70.12]
             , [1479859200000, 70.83]
             , [1480032000000, 71.23]]'::json) t(j);

-1 because JSON array indexes are 0-based (unlike 1-based Postgres arrays).

Upvotes: 1

Simeon Visser
Simeon Visser

Reputation: 122536

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;

Upvotes: 2

Related Questions