how can i get the last item in the array-CodePudding

I have jsonb array like this,

"[{""year"": 2020, ""month"": 8, ""visitor"": 1}, {""year"": 2020, ""month"": 12, ""visitor"": 1}, {""year"": 2021, ""month"": 9, ""visitor"": 1}, {""year"": 2021, ""month"": 11, ""visitor"": 1}, {""year"": 2022, ""month"": 1, ""visitor"": 2}]"

Thats my query

SELECT to_json(t."MonthlyVisitors")->>-1 as visitor FROM "Table" t Where "Url"='asd'

Query Result "{""year"": 2022, ""month"": 1, ""visitor"": 2}"

but the answer i want just the last item;2

CodePudding user response：

var newArray = [1,2,3,4,5]
var lastValue = newArray[newArray.length - 1]; //5

CodePudding user response：

You can use the function jsonb_array_length:

WITH jsontable(j) AS (
   SELECT JSONB '[{"year": 2020, "month": 8, "visitor": 1},
                  {"year": 2020, "month": 12, "visitor": 1},
                  {"year": 2021, "month": 9, "visitor": 1},
                  {"year": 2021, "month": 11, "visitor": 1},
                  {"year": 2022, "month": 1, "visitor": 2}]'
)
SELECT j -> (jsonb_array_length(j) - 1)
FROM jsontable;

                 ?column?                 
══════════════════════════════════════════
 {"year": 2022, "month": 1, "visitor": 2}
(1 row)

Even more efficient is to use the array index -1 (hats off to a_horse_with_no_name):

WITH jsontable(j) AS (...)
SELECT j -> -1
FROM jsontable;