=JSONINDEX

The =JSONINDEX() function returns a specific element from a JSON array based on its index position. It supports both positive indices (counting from the beginning of the array) and negative indices (counting from the end), making it useful for retrieving first, last, or recent entries without knowing the array length.

Syntax

=JSONINDEX(IndexToRetrieve; ExistingJsonArray)

Parameters

• IndexToRetrieve

  • The position of the element to return.

  • Positive numbers count from the beginning (1, 2, 3, …).

  • Negative numbers count from the end (-1, -2, -3, …).

• ExistingJsonArray

  • The JSON array containing the elements to retrieve from.

How It Works

• Uses one-based indexing (the first element is index 1).

• Negative indices wrap from the end of the array.

• If the specified index does not exist, the function returns an empty value.

---

Examples

Basic Usage with Positive Indices

// Sample JSON array
CustomerArray = [
    {"name": "Alice Johnson", "id": 1001, "status": "active"},
    {"name": "Bob Smith", "id": 1002, "status": "inactive"},
    {"name": "Carol Davis", "id": 1003, "status": "active"},
    {"name": "David Wilson", "id": 1004, "status": "active"}
]

// Get the first customer (index 1)
=JSONINDEX(1; CustomerArray)
Result: {"name": "Alice Johnson", "id": 1001, "status": "active"}

Retrieving Elements with Positive Indices

// Get the second customer (index 2)
=JSONINDEX(2; CustomerArray)
// Returns: {"name": "Bob Smith", "id": 1002, "status": "inactive"}

// Get the fourth customer (index 4)
=JSONINDEX(4; CustomerArray)
// Returns: {"name": "David Wilson", "id": 1004, "status": "active"}

Using Negative Indices (Wrap-around from End)

// Get the last customer (index -1)
=JSONINDEX(-1; CustomerArray)
// Returns: {"name": "David Wilson", "id": 1004, "status": "active"}

// Get the second-to-last customer (index -2)
=JSONINDEX(-2; CustomerArray)
// Returns: {"name": "Carol Davis", "id": 1003, "status": "active"}

// Get the third-to-last customer (index -3)
=JSONINDEX(-3; CustomerArray)
// Returns: {"name": "Bob Smith", "id": 1002, "status": "inactive"}

Working with Simple Arrays

// Array of product names
ProductNames = ["Laptop", "Mouse", "Keyboard", "Monitor", "Webcam"]

// Get the first product
=JSONINDEX(1; ProductNames)
// Returns: "Laptop"

// Get the last product using negative index
=JSONINDEX(-1; ProductNames)
// Returns: "Webcam"

// Get the second-to-last product
=JSONINDEX(-2; ProductNames)
// Returns: "Monitor"

Practical Examples

Getting First and Last Elements

// Sales data array
SalesData = [
    {"month": "Jan", "sales": 15000},
    {"month": "Feb", "sales": 18000},
    {"month": "Mar", "sales": 22000},
    {"month": "Apr", "sales": 19000}
]

// Get first month's data
=JSONINDEX(1; SalesData)
// Returns: {"month": "Jan", "sales": 15000}

// Get latest month's data
=JSONINDEX(-1; SalesData)
// Returns: {"month": "Apr", "sales": 19000}

---

Important Notes

• One-based indexing: The first element is at index 1 (not 0)

• Negative indices: Use negative numbers to access elements from the end

• Index bounds: Attempting to access an index that doesn’t exist will return an empty value

• Formula function names are case sensitive and must be written in ALL CAPS.

Common Use Cases

• Retrieving the first element: =JSONINDEX(1; Array)

• Retrieving the last element: =JSONINDEX(-1; Array)

• Getting recent entries without knowing exact array length

---

Keywords

JSONINDEX, JSON array indexing, get JSON element, first item, last item, negative index, JSON parsing, workflow formulas