jq JSON to CSV: @csv Errors, Headers and Nulls, Tested
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"
| Symptom | Cause | Fix |
|---|---|---|
Every line is wrapped in "…" with \" inside | Missing -r | Add -r |
object (…) cannot be csv-formatted, only array | @csv got an object, not an array | Build a row first: [.id, .name] |
array (…) is not valid in a csv row or object (…) is not valid in a csv row | A field inside the row is itself an array or object | join(";"), tostring, or pick one element |
string (…) cannot be csv-formatted, only array with @csv "…" | Interpolation applies @csv to each \(…) separately | Interpolate one array: @csv "\([.id, .name])" |
| A column is missing, no error | Header taken from the first object’s keys | Take keys from all objects |
Backslashes doubled, \n in text | You used @tsv, which escapes on purpose | Use @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
- Is
-rthere? - Is the thing going into
@csvan array, one per row? Put[ … ]around the fields. - Is any field itself an array or object?
join,tostring, or index into it. - Where does your header come from? If it’s
.[0] | keys, you may be losing columns. - 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.