JSON_EXTRACT

Function Overview

JSON_EXTRACT is a powerful tool in Sheet Workflows that extracts specific data from JSON strings in Google Sheets. It simplifies parsing complex JSON data without requiring technical expertise in JSON or JavaScript.

Uses

1. Extract specific fields from API responses stored in your spreadsheet.
2. Parse webhook data received from external services.
3. Extract values from nested JSON structures without complicated formulas.
4. Transform JSON-formatted data into spreadsheet-friendly formats.
5. Extract multiple occurrences of the same field throughout a JSON structure.
6. Navigate through complex nested JSON objects to find specific data.

Syntax and Parameters

=JSON_EXTRACT(jsonString, [field])

Advanced Options

Single Key Extraction

When you pass a single key, JSON_EXTRACT finds all occurrences of that key anywhere in the JSON structure.

=JSON_EXTRACT(A1, "name")

Chained Key Expressions

Use chained key expressions with

<

=JSON_EXTRACT(A1, "city<address<person")

Handling Nested Objects

When a field contains a nested object or array, the function returns the object's keys as separate values if it's a nested object, the array's elements as separate values if it's a nested array, or the primitive value for non-nested content.

Examples

Example 1: Basic Extraction

This example demonstrates extracting a simple field from a JSON object.

=JSON_EXTRACT(A1, "name")

Expected output:
John Doe

Example 2: Finding Multiple Occurrences

This example shows how the function extracts multiple occurrences of a field from a JSON structure containing several objects.

=JSON_EXTRACT(A1, "name")

Expected output (in multiple cells):
Alice
Bob
Charlie
Diana

Example 3: Using Chained Expression

This example demonstrates navigating nested JSON structures using a chained key expression.

=JSON_EXTRACT(A1, "city<address<person")

Expected output (in multiple cells):
New York
Boston

Best Practices

1. Verify your JSON format first by using =JSON_EXTRACT(A1) without a field parameter.
2. Extract specific fields to reduce data processing and simplify results.
3. Handle large datasets carefully by extracting only the necessary fields.
4. Use chained expressions efficiently by starting with the most specific path.
5. Format your cells appropriately after extraction for dates, numbers, or other data types.
6. Combine with ARRAYFORMULA when processing multiple JSON strings in a column.

Troubleshooting

Invalid JSON String

This error occurs when the input string is not valid JSON due to missing commas, brackets, quotes, unescaped special characters, or trailing commas.
Solution: Validate your JSON using an online JSON validator and fix any syntax errors.

Field Not Found

This error occurs when the specified field does not exist in the JSON.
Solutions: Check for typos in the field name, verify the JSON structure, and ensure correct capitalization for case-sensitive fields.

Unexpected Format

If the results appear as [object Object] or contain escaped quotes, it means that the extracted value is an object or array.
Solutions: Use a more specific field path to extract primitive values or combine with additional functions like SPLIT() or REGEXEXTRACT() to further process the results.

FAQ

Can I Extract Data From a JSON Array?

Yes, JSON_EXTRACT will search through arrays and extract fields from any objects they contain.

How Can I Extract Multiple Fields at Once?

You need separate JSON_EXTRACT calls for each field. Consider using multiple columns or a custom formula to combine the results.

Is This Function Case-Sensitive?

Yes, field names in JSON are case-sensitive. For example, "Name" and "name" are treated as different fields.

Can I Use This Function With Data From an API?

Yes, the function works well with API responses. Use Sheet Workflows' HTTP functions to fetch the data, then JSON_EXTRACT to parse it.

What Happens If a Field Contains Null or Undefined Values?

Null values are returned as empty strings, and undefined fields will result in a "Field not found" error.

How Many Levels Deep Can the Function Search?

There is no practical limit to the depth of JSON the function can search through.

Can I Use This Function With More Complex JSON Path Expressions Like Wildcards?

No, the function supports simple key names and chained paths but does not support wildcards or array indexing. For more complex cases, extract a broader section and refine with additional functions.