How to retrieve array element from a specific position in CosmosDB document?

Question

Suppose I have a document with the following structure,

{
    "VehicleDetailId": 1,
    "VehicleDetail": [
        {
            "Id": 1,
            "Make": "BMW"
        },
        {
            "Id": 1,
            "Model": "ABDS"
        },
        {
            "Id": 1,
            "Trim": "5.6L/ASMD"
        },
        {
            "Id": 1,
            "Year": 2008
        }
    ]
}

Now I want to retrieve an array element located at a specific position from VehicleDetail array like I want to retrieve the second element, i.e.,

{
    "Id": 1,
    "Model": "ABDS"
}

or the third,

{
    "Id": 1,
    "Trim": "5.6L/ASMD"
}

How should I write the query to achieve this?

Answer 1

Use the built-in ARRAY_SLICE function. This allows you to select part of an array.

Pass the array, starting position, number of elements to select.

SELECT ARRAY_SLICE(c.VehicleDetail, 1, 1) As SecondElement
FROM c

Output:

{
    "SecondElement": [
        {
            "Id": 1,
            "Model": "ABDS"
        }
    ]
}