How to transpose a json array of objects into a pivot table
- Step 1Open the JSON Transposer — Go to json-transposer. The tool runs entirely client-side; nothing is uploaded to a server.
- Step 2Drop your JSON file — Drag a
.jsonfile onto the dropzone (or click to browse). The input for this mode must be a top-level array of objects — for example a query result or an API list payload. A bare object or a CSV will not pivot here. - Step 3Choose Array → columnar — Select the Array → columnar radio (
pivot-to-columnar). This is the rows-to-columns direction. The hint reads 'Array of records → columns object'. - Step 4Click Transpose — Press Transpose. The tool walks every record, and for each key it appends the value to that key's column array, producing one object of equal-length arrays — provided every record carried the same keys.
- Step 5Verify column lengths — Scan the output: every column array should have the same length as your input array. If one is shorter, a record was missing that key — fix the source so all rows share a uniform key set, then re-run.
- Step 6Copy or download — Use Copy to grab the column arrays for a pivot config, or Download to save
<name>.transposed.json. Feed the columns into PivotTable.js, a DataFrame, or an Arrow table builder.
The three transpose modes
The JSON Transposer exposes exactly three radio-button modes. For pivot-table work you almost always want Array → columnar. There is no flatten, no CSV export, and no header-normalization step inside this tool.
| Mode (UI label) | Internal name | Expects | Produces | Use for |
|---|---|---|---|---|
| Array → columnar | pivot-to-columnar | Top-level array of objects | One object: {key: [values]} | Rotating rows to columns for pivots / columnar stores |
| Columnar → array | pivot-to-records | Object of {key: [values]} | Array of record objects | Rebuilding rows from a column layout (reverse of above) |
| Flip keys ↔ values | flip-kv | A plain (non-array) object | Object with keys and values swapped | Inverting a lookup map — not a pivot operation |
Row-to-column transformation, field by field
How a 3-record array maps to column arrays under Array → columnar. Note the literal append order: column[i] corresponds to record[i] only when every record has the key.
| Input record index | id | status | score | Resulting columns |
|---|---|---|---|---|
| [0] | 1 | "open" | 9.5 | id[0]=1, status[0]="open", score[0]=9.5 |
| [1] | 2 | "open" | 7.0 | id[1]=2, status[1]="open", score[1]=7.0 |
| [2] | 3 | "closed" | 8.2 | id[2]=3, status[2]="closed", score[2]=8.2 |
| Result | [1,2,3] | ["open","open","closed"] | [9.5,7.0,8.2] | Three equal-length column arrays |
Cookbook
Real before/after JSON for pivot-table prep. Each example shows the file dropped in and the exact transposed output, with 2-space indentation as the tool emits it.
Rotate a query result into columns
ExampleA typical SQL-to-JSON dump returns one object per row. Array → columnar gives you one array per field, which PivotTable.js and pandas both consume directly (pd.DataFrame(cols)).
Input (rows.json):
[
{ "region": "NA", "units": 120, "q": "Q1" },
{ "region": "EU", "units": 90, "q": "Q1" },
{ "region": "NA", "units": 150, "q": "Q2" }
]
Mode: Array → columnar
Output:
{
"region": ["NA", "EU", "NA"],
"units": [120, 90, 150],
"q": ["Q1", "Q1", "Q2"]
}Types are preserved per column
ExampleNumbers, booleans, and null pass through unchanged, so a pivot engine sees a numeric column it can SUM, not strings it must coerce.
Input:
[
{ "id": 1, "active": true, "churn": null },
{ "id": 2, "active": false, "churn": 0.12 }
]
Mode: Array → columnar
Output:
{
"id": [1, 2],
"active": [true, false],
"churn": [null, 0.12]
}Round-trip back to rows
ExampleAfter editing columns, Columnar → array rebuilds the original record array — useful when a pivot tool exports columns but the next step needs records.
Input (columns.json):
{
"region": ["NA", "EU"],
"units": [120, 90]
}
Mode: Columnar → array
Output:
[
{ "region": "NA", "units": 120 },
{ "region": "EU", "units": 90 }
]Ragged input misaligns columns
ExampleIf a record omits a key, that column ends up shorter and every value after the gap is off by one. This is the single biggest pivot trap — normalize keys first.
Input (note record [1] has no score):
[
{ "id": 1, "score": 9 },
{ "id": 2 },
{ "id": 3, "score": 7 }
]
Mode: Array → columnar
Output (score is length 2, id is length 3 — MISALIGNED):
{
"id": [1, 2, 3],
"score": [9, 7]
}Nested objects stay nested
ExampleThis tool transposes structure literally; it does not flatten nested objects into dot-notation columns. For that, run json-flattener on the array first, then transpose.
Input:
[
{ "id": 1, "geo": { "city": "NYC" } },
{ "id": 2, "geo": { "city": "LA" } }
]
Mode: Array → columnar
Output (geo column holds whole objects):
{
"id": [1, 2],
"geo": [ { "city": "NYC" }, { "city": "LA" } ]
}Errors and edge cases
Real errors and silent failures sourced from each platform's own documentation. Match the wording to the row, fix what the row says to fix.
Top-level input is an object, not an array
RejectedArray → columnar throws pivot-to-columnar requires an array of objects. The mode is strictly for a top-level JSON array. If your data is already columnar (an object of arrays) you want Columnar → array instead; if it is a single lookup map you want Flip keys ↔ values.
A record is missing a key
MisalignedThe tool only appends a value when the key is present, so a missing key produces a shorter column array and every later value in that column is off by one index versus the other columns. There is no padding. Ensure every record shares the same keys (run a key-normalizing pass first) before pivoting.
Records have extra/inconsistent keys
PreservedAny key seen in any record becomes a column. A key present in only some records yields a column shorter than the others — the same misalignment risk as a missing key. Uniform shapes give clean, aligned columns.
Nested objects or arrays as values
PreservedNested values are copied into the column array as-is — objects stay objects, arrays stay arrays. No dot-notation flattening happens. Pre-flatten with json-flattener if your pivot tool needs scalar columns.
Array of primitives (not objects)
Rejected[1, 2, 3] has no keys to pivot on. Object.entries over a number is empty, so the result is an empty object {}. Array → columnar expects an array of objects.
Empty array `[]`
ExpectedAn empty array produces an empty object {} — there are no records to read keys from. No error is thrown.
Duplicate keys within one object
By designJSON itself collapses duplicate keys (last value wins) at parse time, before the transpose runs. The column array therefore reflects only the surviving value per record.
Invalid JSON (trailing comma, single quotes)
Parse errorInput is parsed with JSON.parse after trimming whitespace; malformed JSON throws a syntax error and nothing transposes. Repair it with json-format-fixer or json-validator first.
File over the tier limit
BlockedFiles above 2 MB on Free are blocked before processing; Pro allows up to 100 MB. The whole file is parsed in memory, so extremely large arrays are bounded by browser memory regardless of tier.
Need a real Excel/CSV pivot, not column-JSON
Wrong toolThis tool only emits JSON. To pivot in Excel or Sheets, convert the array with json-to-csv and open the CSV in your spreadsheet, where the native PivotTable wizard takes over.
Frequently asked questions
Which mode rotates rows into columns?
Array → columnar (internal name pivot-to-columnar). It takes a top-level array of objects and returns one object where each key maps to an array of that field's values across all records.
Does it flatten nested objects into pivot columns?
No. Nested objects and arrays are copied into the column arrays unchanged. If your pivot tool needs scalar columns, run json-flattener on the array first to convert nested fields to dot-notation keys, then transpose.
Why is one of my columns shorter than the others?
A record was missing that key. The tool appends a value only when the key exists, so a missing key leaves the column short and misaligned. Normalize your records to a uniform key set before pivoting.
Can I pivot back from columns to rows?
Yes — use Columnar → array (pivot-to-records). It reads the longest column and emits one record per index, filling any missing slot with null, so a clean round-trip works when all columns are equal length.
Does it output a CSV or Excel pivot table?
No. The output is always JSON. For a spreadsheet pivot, convert with json-to-csv and use Excel or Google Sheets' built-in PivotTable on the CSV.
Are number and boolean types kept?
Yes. Values are moved into column arrays without coercion, so numbers stay numbers, booleans stay booleans, and null stays null — your pivot engine sees real numeric columns it can aggregate.
What happens to an empty array?
You get an empty object {} with no error, because there are no records from which to derive column keys.
Is there an indentation or minify option?
The UI exposes only the three mode radios. Output is pretty-printed with 2-space indentation. To minify the result afterwards use json-minifier.
How big a file can I pivot?
Free tier accepts JSON up to 2 MB; Pro raises it to 100 MB. The JSON Transposer is a Pro tool, so a Pro subscription is required to run it.
Does my data get uploaded?
No. Parsing and transposing happen entirely in your browser tab via JavaScript — the file is never sent to a server.
Can I pivot a CSV file here?
Not directly — the input must be JSON. Convert your CSV with csv-to-json first, then transpose the resulting array.
What if my JSON has a syntax error?
Parsing fails and nothing is produced. Fix the file with json-format-fixer or check it against json-validator, then retry the transpose.
Privacy first
Conversion runs locally in your browser. No file is uploaded — only metadata counters are saved for signed-in dashboard stats.