Query cosmosDB: get last element in array

Question

i have document like this:

    { "id": ....,
    "Title": ""title,
    "ZipCodes": [
            {
                "Code": "code01",
                "Name": "Name01"
            },
            {
                "Code": "code02",
                "Name": "Name02"
            },
            {
                "Code": "code03",
                "Name": "Name03"
            } ],
"_rid": .......,
"_self": .......,
"_etag": ......,
"_attachments": "attachments/",
"_ts": ......

i was used to command

select c.id, c.ZipCodes[ARRAY_LENGTH (c.ZipCodes) -1] as ZipCodes from c

But i got error, how can i query last element ZipCodes in cosmos DB.

Answer 1

You can use ARRAY_SLICE for this. When passed -1 it returns an array containing the last element of the original array. Then index into that with [0] to get the single element contained (i.e. the zip code itself.)

SELECT c.id, 
       ARRAY_SLICE(c.ZipCodes,-1)[0] AS LastZipCode
FROM c