=JSONQUERY

The =JSONQUERY() function allows you to query JSON-formatted content and extract specific property values. It supports nested properties using dot-path syntax and array indexing, making it easy to retrieve deeply nested values from complex JSON structures.

Syntax

=JSONQUERY(JSONPropertyName; JSONContainingTheProperty)

Parameters

• JSONPropertyName

  • The name or path of the JSON property to retrieve.

  • Supports dot notation and array indexing (e.g., Manufacturers[0].Products[1].Price).

• JSONContainingTheProperty

  • The JSON text containing the property you want to extract.

How It Works

• The function parses the provided JSON content.

• The property path is evaluated from left to right.

• If the property exists, its value is returned.

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

Usage Notes

• Array indices are zero-based ([0] is the first element).

• Dot notation can be combined with array indexing for deep queries.

• Works with JSON objects and arrays.

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

---

Example JSON Content

{
  "Store": "Willis Street",
  "Manufacturers": [
    {
      "Name": "Acme Co",
      "Products": [
        {
          "Name": "Anvil",
          "Price": 50
        }
      ]
    },
    {
      "Name": "Contoso",
      "Products": [
        {
          "Name": "Elbow Grease",
          "Price": 99.95
        },
        {
          "Name": "Headlight Fluid",
          "Price": 4
        }
      ]
    }
  ]
}

Examples

Retrieve a Top-Level Property

=JSONQUERY(Store; JSONAbove)

Returns:

Willis Street

Retrieve a Nested Property

=JSONQUERY(Manufacturers[0].Products[0].Price; JSONAbove)

Returns:

50

---

Keywords

JSONQUERY, query JSON, extract JSON values, JSON path, nested JSON, JSON parsing, workflow formulas, formula function