Find last element of array in JSON column type

Question

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)

Simeon Visser · Accepted Answer · 2016-11-27 02:22:07Z

2

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;

answered Nov 27, 2016 at 2:22

Simeon Visser

123k19 gold badges192 silver badges185 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:30:48Z

1

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

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Nov 27, 2016 at 3:39

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

2 Comments

JZ. Over a year ago

too late I upgraded postgres versions!

Erwin Brandstetter Over a year ago

@JZ.: That's the right move. Doesn't change the question, though.

Collectives™ on Stack Overflow

Find last element of array in JSON column type

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related