PostgreSQL: How to Get Last Item in JSON Array

Often in PostgreSQL you may want to select the last item in a JSON array.

You can use the following syntax to do so:

SELECT *,
       player_info->'role'->>-1 AS last_role
FROM athletes;

This particular example selects the last item from the role array of the player_info column in the athletes table.

Note that we use -1 to select the item with an index position of -1 in the array, which represents the last item in the array.

The following example shows how to use this syntax in practice.

Example: How to Get Last Item in JSON Array in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball teams:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER,
  player_info jsonb
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, '{"role":["Center", "Guard", "Center"]}');
INSERT INTO athletes VALUES (0002, '{"role":["Guard", "Forward", "Guard"]}');
INSERT INTO athletes VALUES (0003, '{"role":["Guard", "Guard", "Center"]}');
INSERT INTO athletes VALUES (0004, '{"role":["Guard", "Guard", "Forward"]}');

-- view all rows in table
SELECT * FROM athletes;

 id |               player_info               
----+-----------------------------------------
  1 | {"role": ["Center", "Guard", "Center"]}
  2 | {"role": ["Guard", "Forward", "Guard"]}
  3 | {"role": ["Guard", "Guard", "Center"]}
  4 | {"role": ["Guard", "Guard", "Forward"]}

The player_info column contains information in a JSON format that shows the role of various athletes on each basketball team.

Suppose that we would like to select the last value in each array.

We can use the following syntax to do so:

SELECT *,
       player_info->'role'->>-1 AS last_role
FROM athletes;

 id |               player_info               | last_role 
----+-----------------------------------------+-----------
  1 | {"role": ["Center", "Guard", "Center"]} | Center
  2 | {"role": ["Guard", "Forward", "Guard"]} | Guard
  3 | {"role": ["Guard", "Guard", "Center"]}  | Center
  4 | {"role": ["Guard", "Guard", "Forward"]} | Forward

Notice that the new column named last_role contains the last item from each array in the player_info column.

You can use similar syntax to get the last element from a JSON array in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Filter Table by JSONB Field
PostgreSQL: How to Find the Size of an Array
PostgreSQL: How to Check if Array is Empty