jq · lesson 22 of 32 · Tables & lookups

Records to a table

Most data is an array of objects. List the columns once, in a variable, and use that one list for the header and for every row: .[$cols[]] reads the listed fields in order.

Example 1Header and rows from one list of columns

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", "role", "age"] as $cols | $cols, (.members[] | [.[$cols[]]]) | @csv

["name", "role", "age"] as $cols The columns you want, in the order you want them, saved as $cols.
$cols The header row: the column names themselves.
(.members[] | [.[$cols[]]]) One row per member. .[$cols[]] looks up each listed field in turn, and [ … ] collects the values into an array.
@csv Writes the header and every row as CSV.

Result

"name","role","age"
"Ada","lead",36
"Linus","dev",28
"Grace","dev",45
"Alan","qa",41

Try it in the playground

Example 2A list in one cell

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, (.skills | join(";"))] | @csv

.members[] Each member in turn.
(.skills | join(";")) A list can't sit in a cell as it is: join(";") glues its items into one string. An empty list becomes an empty string.
@csv Writes the row.

Result

"Ada","rust;sql"
"Linus","c;shell"
"Grace","cobol"
"Alan",""

Try it in the playground

Example 3An object as two columns

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

.office | to_entries[] | [.key, .value] | @csv

.office An object with two fields, city and floor.
to_entries[] One {key, value} entry per field.
[.key, .value] A two-cell row: the field's name, then its value.
@csv Writes each row.

Result

"city","Berlin"
"floor",3

Try it in the playground

Good to know

keys_unsorted lists a record's keys in the order of the file, so (.members[0] | keys_unsorted) as $cols | … takes the columns from the first record. Every field must then be a string, number, boolean or null.
A field a record doesn't have reads as null, which is an empty cell.

Records to a table

Examples

Example 1Header and rows from one list of columns

Document

Query

Result

Example 2A list in one cell

Document

Query

Result

Example 3An object as two columns

Document

Query

Result

Good to know