JSON_EXTRACT

Function Overview

JSON_EXTRACT is a powerful custom function that extracts specific values from JSON strings in your spreadsheet. It can find both single values and nested data using simple key paths, making it easy to pull out the exact information you need from complex JSON structures.

Uses

1. Extract API response data into separate cells for analysis
2. Pull specific fields from JSON configuration files
3. Process webhook payloads and extract relevant information
4. Retrieve nested values from complex JSON objects without writing code
5. Extract multiple occurrences of the same field from different parts of a JSON structure
6. Transform unstructured JSON data into structured spreadsheet data
7. Extract values from arrays within JSON data
8. Follow nested paths to deeply buried data points

Syntax and Parameters

=JSON_EXTRACT(jsonString, [field])

Advanced Options

Chained Key Extraction

To extract deeply nested values, use the '<' delimiter to specify a path:

• Simple format: "city<address<person"
• Reading direction: Right-to-left ("find person, then address within it, then city")
• Example: =JSON_EXTRACT(A1, "city<address<person") extracts the city from inside the address inside the person object

Extracting Multiple Values

When a key appears multiple times in your JSON, JSON_EXTRACT finds all occurrences:

• All values are returned as an array
• Values are converted to strings for consistency
• Objects are stringified (returned as JSON strings)

Handling Empty Fields

• If no field is provided, the function returns the entire JSON string
• If a field is not found, the function returns "Field not found"

Examples

Example 1: Extract a Simple Field

Extract a person's name from a JSON object:

=JSON_EXTRACT("{""person"":{""name"":""John"",""age"":30}}", "name")

Example 2: Extract a Nested Field

Extract the city from a nested address structure:

=JSON_EXTRACT("{""data"":{""address"":{""city"":""New York""}}}", "city<address<data")

Example 3: Extract Multiple Occurrences

Extract all project names from a JSON array:

=JSON_EXTRACT("{""projects"":[{""name"":""Project A""},{""name"":""Project B""}]}", "name")

Example 4: Extract the Entire JSON

Get the entire JSON structure (useful for debugging):

=JSON_EXTRACT("{""status"":""success"",""data"":{""count"":5}}")

Example 5: Extract from API Responses

Extract all prices from a complex API response:

=JSON_EXTRACT(URL_READER("https://api.example.com/data"), "price")

Best Practices

• Always wrap your JSON in proper double quotes in the formula (escaped as "")
• For large JSON objects, extract only the specific fields you need
• Use chained key extraction (key1<key2<key3) for deeply nested values
• Check for "Field not found" responses and handle them appropriately
• Test your extraction paths on a small sample before using on large datasets
• Be aware that all values are returned as strings, even numbers and booleans
• When extracting multiple occurrences of a key, verify you're getting all expected values
• For complex JSON structures, consider extracting into multiple cells and then referencing those cells
• Use JSON_EXTRACT in combination with other functions like SPLIT to further process extracted data
• Remember that object values return their keys, not the full object

Troubleshooting

Error: Invalid JSON string

Cause: The JSON string is not properly formatted or contains syntax errors.
Solution: Verify your JSON with a JSON validator. Common issues include missing quotes, unescaped special characters, or trailing commas.

Field not found

Cause: The specified key does not exist in the JSON structure.
Solution: Check that your key name matches exactly (case-sensitive) and that the path is correct if using chained keys.

Function returns array elements in unexpected order

Cause: JSON_EXTRACT searches the entire JSON structure recursively.
Solution: For predictable ordering, structure your JSON appropriately or sort the results after extraction.

Getting string representations instead of actual values

Cause: JSON_EXTRACT converts all values to strings for consistent output.
Solution: Use functions like VALUE() to convert numeric strings back to numbers if needed.

Missing nested values with chained keys

Cause: Key path order is reversed (right-to-left) in the chain expression.
Solution: For path "a.b.c", use "c<b<a" in the field parameter (reading from inner to outer).

Getting keys instead of object values

Cause: When a key points to an object, the function returns the object's keys rather than the object itself.
Solution: Use a more specific path to target the exact value you need within the object.

Unexpected array behavior

Cause: Arrays are handled differently than objects.
Solution: Extract specific array elements by first extracting the array, then accessing elements with another function.

FAQ

How does JSON_EXTRACT handle arrays?

JSON_EXTRACT will search through arrays and return all matching keys it finds at any level. If the key directly references an array, the function will return the array contents as strings.

Can I extract multiple fields at once?

No, each JSON_EXTRACT call extracts a single field. To extract multiple fields, use multiple JSON_EXTRACT calls or consider using EXTRACT_JSON for tabular data.

How do I handle numeric values from JSON_EXTRACT?

All values are returned as strings. Use the VALUE() function to convert to numbers: =VALUE(JSON_EXTRACT(A1,"price")).

Why does my formula return values in unexpected cells?

JSON_EXTRACT returns an array. If your formula is in a cell adjacent to empty cells, Google Sheets may spill the results across multiple cells. Ensure you have enough empty cells to display all results.

How do I extract just one value when multiple matches are found?

Use array indexing with INDEX: =INDEX(JSON_EXTRACT(A1,"name"),1) returns only the first match.

How do I extract values from a nested array of objects?

Use JSON_EXTRACT to find the key across all levels. For specific array indexing, you may need to use a combination of functions or extract the array first.

What happens if the field contains special characters?

The field parameter is case-sensitive and matches exactly. Special characters in key names are supported but must match exactly.

Is there a limit to how deeply nested I can extract?

There's no specific depth limit for chained extraction, but very complex paths may impact performance. For extremely deep nesting, consider breaking extraction into multiple steps.

Can I use wildcard characters in the field parameter?

No, wildcard characters are not supported. Each key must match exactly.

Will JSON_EXTRACT work with compressed or minified JSON?

Yes, the function works with any valid JSON string regardless of formatting.