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, or the result of another JSON function. If a cell contains an escaped JSON string it is unescaped first.

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 joins because join is the default. 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" will not silently collide with a nested user object. Ambiguous collisions are rejected with a message naming both paths.

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. Ask for a key/value pair layout explicitly if you prefer it vertical.

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: not valid JSON at character 214.

Usually a truncated paste or a cell that hit the 50,000-character limit. Wrap the input in JSON_VALID() to find the offending rows.

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.