Toolarity

jq JSON to CSV: @csv Errors, Headers and Nulls, Tested

Fix jq Published November 8, 2026

Turning a JSON array into CSV with jq is one line — once you know which line. Until then you get cannot be csv-formatted, only array, or is not valid in a csv row, or a file where every row is wrapped in quotes and backslashes. And the most expensive mistake doesn’t produce an error at all: a header built the obvious way can drop a column without telling you.

I ran every command below on three jq builds: the official 1.8.2 and 1.7.1 release binaries (checksums matched the release’s sha256sum.txt), and the jq in /usr/bin on macOS 15.3.1, which reports jq-1.6-159-apple-gcff5336-dirty. All output is copied from the terminal. The three builds printed identical results everywhere except in how error messages quote the offending value, which I point out where it matters.

Short answer (tested 2026-09-29):

$ jq -r '["id","name"], (.[] | [.id, .name]) | @csv' users.json
"id","name"
1,"Ann"
2,"Bob ""Bobby"" Smith, Jr."
3,"Cy
Line2"
SymptomCauseFix
Every line is wrapped in "…" with \" insideMissing -rAdd -r
object (…) cannot be csv-formatted, only array@csv got an object, not an arrayBuild a row first: [.id, .name]
array (…) is not valid in a csv row or object (…) is not valid in a csv rowA field inside the row is itself an array or objectjoin(";"), tostring, or pick one element
string (…) cannot be csv-formatted, only array with @csv "…"Interpolation applies @csv to each \(…) separatelyInterpolate one array: @csv "\([.id, .name])"
A column is missing, no errorHeader taken from the first object’s keysTake keys from all objects
Backslashes doubled, \n in textYou used @tsv, which escapes on purposeUse @csv, or unescape when reading

The test file

users.json has the things that break naive CSV: a quote and a comma in a name, a newline in another, a null, a missing field, a boolean, an array, and a decimal with a trailing zero.

[
  {"id": 1, "name": "Ann", "email": "ann@example.com", "active": true, "tags": ["admin", "ops"]},
  {"id": 2, "name": "Bob \"Bobby\" Smith, Jr.", "email": null, "active": false, "tags": []},
  {"id": 3, "name": "Cy\nLine2", "active": true, "tags": ["dev"], "score": 1.10}
]

Forgetting -r

Without -r, jq prints each CSV line as a JSON string, so you get outer quotes and every inner quote escaped a second time:

$ jq '.[] | [.id, .name] | @csv' users.json
"1,\"Ann\""
"2,\"Bob \"\"Bobby\"\" Smith, Jr.\""
"3,\"Cy\nLine2\""

With -r the same filter prints real CSV. Quotes inside a value are doubled, which is the standard CSV escape, and the newline stays inside the quoted field:

$ jq -r '.[] | [.id, .name] | @csv' users.json
1,"Ann"
2,"Bob ""Bobby"" Smith, Jr."
3,"Cy
Line2"

That last row is valid CSV, but it’s two physical lines. Anything that reads the file line by line (wc -l, grep, a quick while read loop) will count it wrong. If you don’t need the newline, replace it before formatting:

$ jq -r '.[] | [.id, (.name | gsub("\n"; " "))] | @csv' users.json
1,"Ann"
2,"Bob ""Bobby"" Smith, Jr."
3,"Cy Line2"

“cannot be csv-formatted, only array”

@csv wants an array: one array per row. Handing it the objects directly fails:

$ jq -r '.[] | @csv' users.json
jq: error (at users.json:5): object ({"id":1,"name":"Ann","ema...}) cannot be csv-formatted, only array

(1.7.1 and the macOS build quote less of the object: ({"id":1,"na...).)

The fix is to list the fields you want, in the order you want them: .[] | [.id, .name] | @csv, as in the short answer.

The same error shows up when you use @csv with string interpolation, which looks like it should work:

$ jq -r '.[] | @csv "\(.id),\(.name)"' users.json
jq: error (at users.json:5): string ("Ann") cannot be csv-formatted, only array

The manual explains why: with @foo "…", the literal text is left alone and each interpolation is escaped separately. So @csv ran on .id (a number, which was fine) and then on .name (a string, which isn’t an array). Interpolate one array instead:

$ jq -r '.[] | @csv "\([.id, .name])"' users.json
1,"Ann"
2,"Bob ""Bobby"" Smith, Jr."
3,"Cy
Line2"

“is not valid in a csv row”

This one means the row is an array, but one of its fields is an array or an object. tags is an array:

$ jq -r '.[] | [.id, .tags] | @csv' users.json
jq: error (at users.json:5): array (["admin","ops"]) is not valid in a csv row

(Again, 1.7.1 and macOS cut the value shorter: (["admin","o...).)

You’ll also hit it when the JSON has a wrapper object and .[] goes one level too shallow. With {"users":[{"id":1,"name":"Ann"},{"id":2,"name":"Bob"}]} in wrapped.json, .[] yields the whole users array as a single “row” of objects:

$ jq -r '.[] | @csv' wrapped.json
jq: error (at wrapped.json:1): object ({"id":1,"name":"Ann"}) is not valid in a csv row
$ jq -r '.users[] | [.id, .name] | @csv' wrapped.json
1,"Ann"
2,"Bob"

(That error is from 1.8.2; the other two builds shorten the object to ({"id":1,"na...).)

For a nested array, decide what the cell should hold. Joined into one field:

$ jq -r '.[] | [.id, (.tags | join(";"))] | @csv' users.json
1,"admin;ops"
2,""
3,"dev"

As JSON text in one field (the quotes get doubled, which is correct CSV):

$ jq -r '.[] | [.id, (.tags | tostring)] | @csv' users.json
1,"[""admin"",""ops""]"
2,"[]"
3,"[""dev""]"

Or only the first element:

$ jq -r '.[] | [.id, .tags[0]] | @csv' users.json
1,"admin"
2,
3,"dev"

Avoid [.id, .tags[]]. It doesn’t error, but it spreads the tags over as many columns as each record happens to have, so rows end up with different lengths:

$ jq -r '.[] | [.id, .tags[]] | @csv' users.json
1,"admin","ops"
2
3,"dev"

Adding a header row

Put the header array before the rows, separated by a comma, and let the one @csv at the end format both:

$ jq -r '["id","name"], (.[] | [.id, .name]) | @csv' users.json
"id","name"
1,"Ann"
2,"Bob ""Bobby"" Smith, Jr."
3,"Cy
Line2"

The parentheses matter. | binds looser than ,, so without them jq sends the header array down the same pipe as the records, and .id fails on it:

$ jq -r '["id","name"], .[] | [.id, .name] | @csv' users.json
jq: error (at users.json:5): Cannot index array with string ("id")

(1.7.1 and macOS print with string "id", without the parentheses.)

The silent one: a header from the first object drops columns

A common way to avoid typing column names is to read them from the first record. Here’s a file where the second record has a field the first one doesn’t, with the keys in a different order:

[
  {"id": 1, "name": "Ann"},
  {"name": "Bob", "id": 2, "email": "bob@example.com"}
]
$ jq -r '(.[0] | keys_unsorted) as $k | $k, (.[] | [.[$k[]]]) | @csv' mixed_order.json
"id","name"
1,"Ann"
2,"Bob"

The rows line up, but bob@example.com is gone, and nothing warns you. Build the header from every record’s keys instead:

$ jq -r '(map(keys) | add | unique) as $k | $k, (.[] | [.[$k[]]]) | @csv' mixed_order.json
"email","id","name"
,1,"Ann"
"bob@example.com",2,"Bob"

unique sorts the column names alphabetically. If you need a specific order, write the list out by hand.

And don’t shortcut with [.[]] (every value of each object): it follows each object’s own key order, so columns shift from row to row:

$ jq -r '.[] | [.[]] | @csv' mixed_order.json
1,"Ann"
"Bob",2,"bob@example.com"

Nulls, missing fields, booleans and numbers

@csv leaves null as an empty field, quotes an empty string, and writes numbers and booleans without quotes:

$ jq -rn '[null, "", 0, false] | @csv'
,"",0,false

A missing field is null in jq, so it comes out empty too; you can’t tell “missing” from null in the CSV. From the test file, email is null for Bob and absent for Cy, and score only exists for Cy:

$ jq -r '.[] | [.id, .email, .active, .score] | @csv' users.json
1,"ann@example.com",true,
2,,false,
3,,true,1.10

Notice 1.10 kept its trailing zero. Numbers you don’t touch are printed as they were written in the input, on all three builds, even a 21-digit integer. The one change is that exponent notation gets rewritten:

[{"a": 1.10, "b": 100000000000000000001, "c": 1e3, "d": 0.1}]
$ jq -r '.[] | [.a, .b, .c, .d] | @csv' nums.json
1.10,100000000000000000001,1E+3,0.1

Do any arithmetic on them and you get ordinary floating-point numbers instead. The 21-digit integer loses its last digit:

$ jq -r '.[] | [.a + 0, .c + 0, .b + 0] | @csv' nums.json
1.1,1000,1e+20

The 1.7 release notes describe this: number literals are kept to preserve precision, but arithmetic might truncate. So if IDs or amounts must survive exactly, don’t compute on them inside the same filter that writes the CSV. (The macOS build reports 1.6, but it behaved like 1.7 here; its version string says it was built 159 commits after the 1.6 tag. I didn’t test upstream 1.6.)

To turn a flat object into two-column CSV, use to_entries. With {"apples": 3, "pears": 0, "plums": null} in counts.json:

$ jq -r 'to_entries[] | [.key, .value] | @csv' counts.json
"apples",3
"pears",0
"plums",

join(",") is not CSV

It looks similar on clean data, but join doesn’t quote anything, so a comma or quote inside a value breaks the row:

$ jq -r '.[] | [.id, .name] | join(",")' users.json
1,Ann
2,Bob "Bobby" Smith, Jr.
3,Cy
Line2

Bob’s row now has three fields. Use join only when you control the data completely.

@tsv escapes on purpose

@tsv is the better choice when the next step is cut -f or awk -F'\t', because a record is always one line. It gets there by escaping: newline, carriage return, tab and backslash become \n, \r, \t and \\ (this part is in the manual). Quotes and commas are left alone:

$ jq -r '.[] | [.id, .name] | @tsv' users.json
1	Ann
2	Bob "Bobby" Smith, Jr.
3	Cy\nLine2

That also means a Windows path comes out with two backslashes. Here path is C:\temp and note holds a real tab character:

$ jq -r '.[] | [.path, .note] | @tsv' tsv.json
C:\\temp	tab\there
$ jq -r '.[] | [.path, .note] | @csv' tsv.json
"C:\temp","tab	here"

If you open the TSV in a spreadsheet, you’ll see C:\\temp literally. @csv keeps the value as it is.

CSV back to JSON: jq isn’t a CSV parser

Searches for “jq csv to json” usually end with -R and split(","), which reads each line as a string and cuts at every comma. It breaks on the first quoted comma:

$ cat in.csv
id,name
1,Ann
2,"Smith, Jr."
$ jq -c -R 'split(",")' in.csv
["id","name"]
["1","Ann"]
["2","\"Smith"," Jr.\""]

jq has no built-in that reads CSV quoting. If the file might contain quoted fields, convert it with a real CSV parser first (for example Python’s csv module, or a tool like Miller), then use jq on the JSON.

What I’d check, in order

  1. Is -r there?
  2. Is the thing going into @csv an array, one per row? Put [ … ] around the fields.
  3. Is any field itself an array or object? join, tostring, or index into it.
  4. Where does your header come from? If it’s .[0] | keys, you may be losing columns.
  5. Did you compute on numbers you need exactly?

Test setup

jq 1.8.2 and 1.7.1 (official jq-macos-arm64 release binaries, SHA-256 checked against each release’s sha256sum.txt) and macOS’s /usr/bin/jq (jq-1.6-159-apple-gcff5336-dirty), on macOS 15.3.1, Apple Silicon, tested 2026-09-29. Not tested: Linux and Windows builds, and upstream jq 1.6 or older.

@csv and @tsv are described under “Format strings and escaping” in the official manual, https://jqlang.org/manual/ (checked 2026-09-29). The manual doesn’t cover how @csv prints null and booleans or which values it rejects; those parts of this article come from the tests above.

To combine two JSON files before exporting them, see merging two JSON files by key.