=JSONREMOVE

The =JSONREMOVE() function removes a specified element from a JSON array based on its index position and returns a new, modified array. The function supports both positive indices, which count from the beginning of the array, and negative indices, which count from the end.

Syntax

=JSONREMOVE(IndexToRemove; ExistingJsonArray)

Parameters

• IndexToRemove

  • The index position of the element to remove.

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

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

• ExistingJsonArray

  • The JSON array containing the element to be removed.

How It Works

The function evaluates the provided JSON array and identifies the element located at the specified index. That element is removed, and a new JSON array is returned with the remaining elements in their original relative order.

Usage Notes

• Indexing is one-based, meaning the first element is at index 1, not 0.

• Negative indices allow access to elements starting from the end of the array.

• If the specified index does not exist, the original array is returned unchanged.

• The function is non-destructive and does not modify the original array.

• After removal, the remaining elements are automatically reindexed.

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

---

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"}
]

// Remove the first customer (index 1)
=JSONREMOVE(1; CustomerArray)
Result: [
    {"name": "Bob Smith", "id": 1002, "status": "inactive"},
    {"name": "Carol Davis", "id": 1003, "status": "active"},
    {"name": "David Wilson", "id": 1004, "status": "active"}
]

Removing Elements with Positive Indices

// Remove the second customer (index 2)
=JSONREMOVE(2; CustomerArray)
// Returns: [
//     {"name": "Alice Johnson", "id": 1001, "status": "active"},
//     {"name": "Carol Davis", "id": 1003, "status": "active"},
//     {"name": "David Wilson", "id": 1004, "status": "active"}
// ]

// Remove the fourth customer (index 4)
=JSONREMOVE(4; CustomerArray)
// Returns: [
//     {"name": "Alice Johnson", "id": 1001, "status": "active"},
//     {"name": "Bob Smith", "id": 1002, "status": "inactive"},
//     {"name": "Carol Davis", "id": 1003, "status": "active"}
// ]

Using Negative Indices (Wrap-around from End)

// Remove the last customer (index -1)
=JSONREMOVE(-1; CustomerArray)
// Returns: [
//     {"name": "Alice Johnson", "id": 1001, "status": "active"},
//     {"name": "Bob Smith", "id": 1002, "status": "inactive"},
//     {"name": "Carol Davis", "id": 1003, "status": "active"}
// ]

// Remove the second-to-last customer (index -2)
=JSONREMOVE(-2; CustomerArray)
// Returns: [
//     {"name": "Alice Johnson", "id": 1001, "status": "active"},
//     {"name": "Bob Smith", "id": 1002, "status": "inactive"},
//     {"name": "David Wilson", "id": 1004, "status": "active"}
// ]

// Remove the third-to-last customer (index -3)
=JSONREMOVE(-3; CustomerArray)
// Returns: [
//     {"name": "Alice Johnson", "id": 1001, "status": "active"},
//     {"name": "Carol Davis", "id": 1003, "status": "active"},
//     {"name": "David Wilson", "id": 1004, "status": "active"}
// ]

Working with Simple Arrays

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

// Remove the first product
=JSONREMOVE(1; ProductNames)
// Returns: ["Mouse", "Keyboard", "Monitor", "Webcam"]

// Remove the last product using negative index
=JSONREMOVE(-1; ProductNames)
// Returns: ["Laptop", "Mouse", "Keyboard", "Monitor"]

// Remove the second-to-last product
=JSONREMOVE(-2; ProductNames)
// Returns: ["Laptop", "Mouse", "Keyboard", "Webcam"]

Practical Examples

Cleaning Data Arrays

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

// Remove first month's data (outdated)
=JSONREMOVE(1; SalesData)
// Returns: [
//     {"month": "Feb", "sales": 18000},
//     {"month": "Mar", "sales": 22000},
//     {"month": "Apr", "sales": 19000}
// ]

// Remove latest month's data (incomplete)
=JSONREMOVE(-1; SalesData)
// Returns: [
//     {"month": "Jan", "sales": 15000},
//     {"month": "Feb", "sales": 18000},
//     {"month": "Mar", "sales": 22000}
// ]

Filtering Out Specific Entries

// Task list array
TaskList = [
    {"task": "Review documents", "priority": "high", "completed": true},
    {"task": "Send emails", "priority": "medium", "completed": false},
    {"task": "Update website", "priority": "low", "completed": true},
    {"task": "Call client", "priority": "high", "completed": false}
]

// Remove completed task at index 3
=JSONREMOVE(3; TaskList)
// Returns: [
//     {"task": "Review documents", "priority": "high", "completed": true},
//     {"task": "Send emails", "priority": "medium", "completed": false},
//     {"task": "Call client", "priority": "high", "completed": false}
// ]

Common Use Cases

• Removing the first element: =JSONREMOVE(1; Array)

• Removing the last element: =JSONREMOVE(-1; Array)

• Cleaning up arrays by removing outdated or invalid entries

• Data filtering workflows where specific positioned elements need removal

---

Keywords

JSONREMOVE, remove JSON element, JSON array manipulation, JSON indexing, negative index JSON, workflow formulas, formula function