The lastIndexOf() method returns the last index at which a given element can be found in the array, or -1 if it is not present.
PostgreSQL UNNEST() function This function is used to expand an array to a set of rows.
To get the second to last element in an array, call the at() method on the array, passing it -2 as a parameter, e.g. arr.at(-2) . The at method returns the array element at the specified index.
For any array "arr", to fetch the last element of array arr use
SELECT arr[array_upper(arr, 1)];
I think you're misinterpreting the example. PostgreSQL arrays don't have to be indexed from 1 to n
, that's just the default:
By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with
array[1]
and ends witharray[n]
.
The example you're looking at is this:
SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
But those negative numbers aren't indexing from the end of the arrays as in languages such as Perl. In the FROM (SELECT ...
part, they're specifying the starting and ending indexes so the -1 in f1[1][-1][5]
is just a plain old index. Consider this array_dims
result:
=> SELECT array_dims('[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[]);
array_dims
-------------------
[1:1][-2:-1][3:5]
If you're using the default 1-based arrays then you can get the last element with a simple arr[array_length(arr, 1)]
. If you're not using the default [1:n]
arrays then you'll have to mess around with array_lower
and array_upper
to get the first and last elements; or, depending on the circumstances, you might be able to use unnest
to unpack the array then work with the array as a rowset.
If someone is using Postgre 9.5, the documentation says:
-> int
Get JSON array element (indexed from zero, negative integers count from the end)
So this works for me:
to_json(arr)->-1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With