How to convert JSON to CSV (jq, Python, pandas, Miller)

To convert JSON to CSV, find the array whose items should become rows, flatten each item into one level of key-value pairs, and write the result with a proper CSV writer. With jq that's a one-liner; in Python it's pandas.json_normalize plus to_csv, or the standard csv module if you want no dependencies. The hard part is never the syntax. It's deciding what a row is.

What you need before you start

Make three decisions first:

Which array is the table. API responses rarely start with a bare list. The records usually sit under a key such as data, results or items, next to paging fields you don't want as rows.
Nested objects. Flatten them into dotted columns (address.city), or drop them.
Nested arrays. Either one row per array item (repeating the parent fields), or join the array into a single cell.

If you can't tell where the records live in a big response, paste it into a JSON viewer with a collapsible tree and fold everything until the shape shows. That gives you the path for the tools below.

Here's the sample used in every example:

{
  "page": 1,
  "results": [
    {"id": 1, "name": "Ada", "address": {"city": "London"}, "tags": ["admin", "ops"]},
    {"id": 2, "name": "Lin, Mei", "address": {"city": "Taipei"}, "tags": []}
  ]
}

Note the comma in "Lin, Mei". That one field is what breaks most hand-rolled converters.

Option 1: jq on the command line

jq's @csv filter takes an array and, in the words of the manual, renders it "as CSV with double quotes for strings, and quotes escaped by repetition." You build one array per row and pipe it through:

jq -r '["id","name","city"], (.results[] | [.id, .name, .address.city]) | @csv' users.json

Output:

"id","name","city"
1,"Ada","London"
2,"Lin, Mei","Taipei"

Two things trip people up. The first is the -r flag. Without it, jq prints each line as a JSON string, wrapped in quotes and with the inner quotes escaped. With -r, the manual says, the string goes "directly to standard output". The second: @csv only accepts an array, so you list the fields yourself. That's a feature. You pick the columns and their order, and an object with an extra key can't shift them.

If the consumer prefers tabs, swap @csv for @tsv.

Option 2: Python with pandas

pandas.json_normalize exists for exactly this: "Normalize semi-structured JSON data into a flat table." Nested objects become columns joined by sep, which defaults to a dot, so {"foo": {"bar": 0}} turns into a foo.bar column.

import json
import pandas as pd

with open("users.json") as f:
    doc = json.load(f)

df = pd.json_normalize(doc["results"])
df.to_csv("users.csv", index=False)

That gives you id, name, tags and address.city columns. The tags column holds the Python list as text, which is rarely what you want.

To get one row per tag instead, use record_path (the path to the list of records) and meta (fields copied onto each of those rows):

df = pd.json_normalize(doc["results"], record_path="tags", meta=["id", "name"])

Now Ada gets two rows, one per tag, and Lin Mei gets none, because her tags array is empty. If you need parents with empty lists, join the list into a string column first.

max_level caps how deep the flattening goes, which helps when one field holds a deep blob you'd rather keep as text.

Option 3: Python without dependencies

The standard library's csv module handles quoting for you. DictWriter maps dictionaries onto rows, and its fieldnames fix the column order:

import csv, json

with open("users.json") as f:
    rows = json.load(f)["results"]

with open("users.csv", "w", newline="") as out:
    w = csv.DictWriter(out, fieldnames=["id", "name", "city"], extrasaction="ignore")
    w.writeheader()
    for r in rows:
        w.writerow({**r, "city": r["address"]["city"]})

Keep newline="". The Python docs warn that without it, newlines inside quoted fields won't be read correctly, and on platforms that use \r\n an extra \r gets added, which shows up as blank lines between rows on Windows. extrasaction="ignore" matters too: the default is "raise", which throws a ValueError as soon as a record has a key you didn't list. A missing key is filled with restval, an empty string unless you say otherwise.

Option 4: Miller

Miller converts formats as a side effect of reading one and writing another. mlr --ijson --ocsv cat file.json flattens nested maps into dotted keys (the separator is set by flatsep). It expects the records at the top level, so pull them out of the wrapper first, for example with jq '.results'. It's my pick for quick conversions of logs and exports where I don't want to name every column.

How to check it worked

Open the CSV in a plain text editor, not a spreadsheet, and look at a row you know contains a comma or a quote. Under RFC 4180, fields with commas, double quotes or line breaks should be wrapped in double quotes, and a quote inside a field is written twice ("She said ""hi""").
Count the rows. With a header line, the file should have one more line than you have records, unless a field contains a line break.
Check that the header row matches the columns you expect. Records with different keys are where columns go missing.

What goes wrong

Joining values with commas by hand. Object.values(row).join(",") works on the first test file and fails on the first real name with a comma in it. Always use a CSV library or @csv.

Leading zeros disappear in Excel. This isn't a conversion bug. As an answer on Microsoft Q&A puts it, CSV values get converted to numbers when you open the file, but the zeros "are still there if you open them in notepad." ZIP codes and account numbers starting with 0 are the usual victims. Import with Data > From Text/CSV, choose Transform Data, set the column type to Text and pick Replace Current.

Long numeric IDs change. Spreadsheets and some parsers store numbers as doubles, so a big ID can come back rounded. I covered why in why JSON.parse loses precision on large numbers. Keep such IDs as strings in the JSON if you control it.

The input isn't valid JSON. Every tool above parses first, so a stray trailing comma stops the whole run. See why JSON doesn't allow trailing commas, and if the file came back empty from an API call, what "Unexpected end of JSON input" means.

My take

Use jq with an explicit column list for anything that runs more than once. Naming the columns is extra typing, and it's the only option here where a new field in the API response can't silently reshape your spreadsheet. pandas is better when the data is messy and you're exploring it.

OnlineViewer Dev doesn't have a JSON to CSV converter. Its JSON tools stop at viewing, formatting, validating and converting to YAML, so for the conversion itself you need one of the tools above.

FAQ

Can every JSON file be converted to CSV?

Only the part that is a list of similar records. CSV is a flat table, so you have to choose which array becomes the rows and decide what to do with nested objects and arrays inside each record.

How do nested objects become CSV columns?

They are flattened into dotted column names. pandas.json_normalize and Miller both turn {"address": {"city": ...}} into a column called address.city by default.

Why does my CSV break when a value contains a comma?

The value was written without quotes. RFC 4180 says fields that contain commas, double quotes or line breaks should be wrapped in double quotes, with inner quotes doubled. Use a CSV library instead of joining strings with commas.

Why did Excel remove the leading zeros from my CSV?

Excel converts digit-only text to numbers when it opens a CSV. The zeros are still in the file. Import it through Data > From Text/CSV and set that column to Text.

How do I convert JSON to CSV?