jq · lesson 21 of 32 · Tables & lookups
CSV & TSV
@csv and @tsv write an array as one row of text. Build the row with [ … ], one row per result. When the last step of a query is one of them, the playground shows the results as rows, labelled CSV or TSV, not as quoted JSON strings.
Examples
Example 1One CSV row per member
Document
{
"team": "Platform",
"office": { "city": "Berlin", "floor": 3 },
"members": [
{ "name": "Ada", "age": 36, "role": "lead", "active": true,
"email": "[email protected]", "skills": ["rust", "sql"] },
{ "name": "Linus", "age": 28, "role": "dev", "active": true,
"email": null, "skills": ["c", "shell"] },
{ "name": "Grace", "age": 45, "role": "dev", "active": false,
"skills": ["cobol"] },
{ "name": "Alan", "age": 41, "role": "qa", "active": true,
"email": "[email protected]", "skills": [] }
]
}Query
.members[] | [.name, .age, .role] | @csv
.members[]Each member in turn.[.name, .age, .role]Build an array of the fields you want, in column order: one array is one row.@csvWrite the array as a line of CSV: strings in quotes, numbers bare, commas between the fields.
Result
"Ada",36,"lead" "Linus",28,"dev" "Grace",45,"dev" "Alan",41,"qa"
Example 2A header row first, tab-separated
Document
{
"team": "Platform",
"office": { "city": "Berlin", "floor": 3 },
"members": [
{ "name": "Ada", "age": 36, "role": "lead", "active": true,
"email": "[email protected]", "skills": ["rust", "sql"] },
{ "name": "Linus", "age": 28, "role": "dev", "active": true,
"email": null, "skills": ["c", "shell"] },
{ "name": "Grace", "age": 45, "role": "dev", "active": false,
"skills": ["cobol"] },
{ "name": "Alan", "age": 41, "role": "qa", "active": true,
"email": "[email protected]", "skills": [] }
]
}Query
["name", "age"], (.members[] | [.name, .age]) | @tsv
["name", "age"]The header: an array of column titles.,The comma puts the header first and the member rows after it.(.members[] | [.name, .age])One row array per member.@tsvApplies to the header and every row: tabs between the fields, and any tab, newline or backslash inside a value written as\t,\nor\\.
Result
name age Ada 36 Linus 28 Grace 45 Alan 41
Example 3Missing values are empty fields
Document
{
"team": "Platform",
"office": { "city": "Berlin", "floor": 3 },
"members": [
{ "name": "Ada", "age": 36, "role": "lead", "active": true,
"email": "[email protected]", "skills": ["rust", "sql"] },
{ "name": "Linus", "age": 28, "role": "dev", "active": true,
"email": null, "skills": ["c", "shell"] },
{ "name": "Grace", "age": 45, "role": "dev", "active": false,
"skills": ["cobol"] },
{ "name": "Alan", "age": 41, "role": "qa", "active": true,
"email": "[email protected]", "skills": [] }
]
}Query
.members[] | [.name, .email] | @csv
[.name, .email]Linus's e-mail isnulland Grace has none at all: both come out asnull…@csv…which is an empty field, not the wordnull.
Result
"Ada","[email protected]" "Linus", "Grace", "Alan","[email protected]"
Good to know
- Only strings, numbers, booleans and
nullfit in a row. An array or object among the fields is an error; flatten it first, for example.skills | join(";"). @csvand@tsvare jq's own names for these formats, so a query written here also works in thejqcommand line (with-rto print the rows without quotes).