Local · unmetered · no sign-in

JSON_TABLE

Turns a JSON array of objects into a header row plus data rows, ready to spill into the sheet. Columns are the union of every key discovered, in a deterministic order, so the same payload always lands the same way.

=JSON_TABLE(json, [path], [options])

Arguments

Argument Required Accepts

json Yes

A cell holding JSON text, a JSON string literal, or compact JSON text returned by another JSON function.

path No

Where the array lives. A dot path (data.orders), an index (orders[0]), a wildcard (orders[*]), a JSONPath-subset expression starting with $, or a JMESPath expression in advanced mode. Omit it and the root is used.

options No

A semicolon-separated string. Unknown keys are rejected. Full option list →

Worked examples

All three use the same payload in A1.

{
  "data": {
    "orders": [
      { "id": 8842, "customer": { "name": "Ana Ruiz" }, "tags": ["rush", "gift"], "note": null },
      { "id": 8843, "customer": { "name": "Ben Okafor" }, "tags": [], "refunded": true }
    ]
  }
}

01 · default behaviour

=JSON_TABLE(A1, "data.orders")

Nested objects flatten to dot notation. tags stays as compact JSON because json is the default array mode. refunded only exists on the second record, so it becomes a column with a blank above it. null is written as a blank cell, not the text "null".

id customer.name tags note refunded 8842 Ana Ruiz ["rush","gift"] 8843 Ben Okafor TRUE

02 · one row per tag

=JSON_TABLE(A1, "$.data.orders[*]", "array=rows")

Order 8842 becomes two rows, one per tag. Order 8843 has an empty array, so it still produces one row with a blank — records are never dropped. In the sidebar this is where the preview warns you that row count will exceed record count.

id customer.name tags note refunded 8842 Ana Ruiz rush 8842 Ana Ruiz gift 8843 Ben Okafor TRUE

03 · filtered with JMESPath, no header row

=JSON_TABLE(A1, "data.orders[?refunded]", "headers=false;array=json")

Only refunded orders. With headers=false the output starts at the data, so it can sit under headers you have already styled. array=json keeps tags intact for a later JSON_VALUE().

8843 Ben Okafor [] TRUE

Rules it follows

Column order is stable

Keys appear in the order first encountered while scanning records. A key that only shows up in record 900 is appended at the end, not inserted alphabetically.

Keys containing dots are escaped

A source key like "user.id" is encoded with bracket notation, so it remains distinct from a nested user object.

Arrays of primitives get one column

Point at ["a","b","c"] and you get a single value column rather than an error.

A single object becomes one row

Point at an object instead of an array and you get a header row plus one data row, with nested object paths expanded into columns.

Big integers stay text

Anything outside JavaScript's safe integer range is written as text so a 19-digit ID does not quietly lose its last digits.

Limits fail loudly

Exceeding your row or column cap raises an error rather than writing a truncated table you might mistake for the whole dataset.

Errors you might see

JSON for Sheets: The JSON could not be parsed.

Usually the source is truncated or malformed. Test the same cell with JSON_VALID(); it returns FALSE when that value is not valid JSON.

JSON for Sheets: path "data.order" matched nothing.

The path is valid but absent. JSON_KEYS(A1, "data") will show you what is actually there.

JSON for Sheets: unknown option "flaten".

Options are parsed strictly on purpose — a typo that silently did nothing would be worse.