Converting a Nested JSON List of Users into a Simple CSV with jq and awk -

When a service spits out a JSON blob that contains a list of users, the data is usually nested and has more fields than you actually need. Turning that into a flat CSV is a common thing to do for automation scripts, reporting, or feeding into spreadsheets. The two most reliable command‑line tools for this job are jq (a JSON processor) and awk (a text filter). Together they give you a fast, secure, and scriptable pipeline.

Why not just use `jq` alone?

jq can output CSV directly with the @csv filter, but it has a few quirks:

It always quotes fields, even if they contain no commas or quotes. That’s fine for most CSV consumers, but sometimes you want a more compact output.
The @csv filter treats every array element as a separate column, which can be confusing when you have nested objects.
When the JSON is huge, jq’s memory usage can spike because it builds the whole structure in memory unless you use the --stream option.

awk gives you fine‑grained control over field separators, quoting, and can stream line‑by‑line without loading the entire file. By piping jq’s flattened output into awk, you get the best of both worlds.

Sample JSON

{
  "org": "Acme",
  "users": [
    {
      "id": 101,
      "name": "Alice Smith",
      "email": "alice@example.com",
      "roles": ["admin", "developer"]
    },
    {
      "id": 102,
      "name": "Bob Jones",
      "email": "bob@example.com",
      "roles": ["developer"]
    }
  ]
}

We want a CSV with columns: id,name,email,roles. The roles field will be a semicolon‑separated list.

One‑liner with jq only

jq -r '.users[] | [.id, .name, .email, (.roles | join(";"))] | @csv' users.json

Output:

101,"Alice Smith","alice@example.com","admin;developer"
102,"Bob Jones","bob@example.com","developer"

Pros: Short, no external dependencies beyond jq.
Cons: The output is fully quoted, and if you need to strip quotes or change delimiters you must add more filters.

Using jq + awk for more control

jq -r '.users[] | [.id, .name, .email, (.roles | join(";"))] | @tsv' users.json |
awk -F'\t' 'BEGIN{OFS=","} {print $1,$2,$3,$4}'

This pipeline:

jq builds a tab‑separated string (@tsv) to avoid quoting issues.
awk changes the field separator to comma and prints the fields.

Result:

101,Alice Smith,alice@example.com,admin;developer
102,Bob Jones,bob@example.com,developer

If you need to escape commas inside fields, add a simple gsub in awk:

awk -F'\t' 'BEGIN{OFS=","} {gsub(/,/, "\\,", $2); print $1,$2,$3,$4}'

Streaming large files

For JSON files that exceed available RAM, use jq’s streaming mode:

jq --stream 'select(length==4 and .[0][0]=="users") | .[1][0] as $id | .[1][1] as $name | .[1][2] as $email | .[1][3] as $roles | [$id,$name,$email,($roles|join(";"))] | @tsv' users.json |
awk -F'\t' 'BEGIN{OFS=","} {print $1,$2,$3,$4}'

The --stream option emits a flat array for every leaf node, so you can filter and rebuild the desired structure on the fly. This keeps memory usage low even for gigabyte‑sized JSON.

Security considerations

Avoid shell injection: Never interpolate user‑supplied JSON into shell commands. Always pipe the file directly into jq or use jq’s --slurpfile if you need to merge external data.
Validate input: Use jq’s --exit-status to detect malformed JSON. A non‑zero exit code can trigger an alert in your automation pipeline.
Least privilege: Run the conversion script as a dedicated user with read‑only access to the JSON source. This limits the impact if the script is compromised.
Audit: Keep the jq binary in a known location (e.g., /usr/bin/jq) and verify its checksum against the official release from the jq GitHub repository. This mitigates supply‑chain attacks.

Common pitfalls and how to fix them

Symptom	Likely cause	Fix
`jq: error: Cannot iterate over null`	Some user objects lack the `roles` field.	Use `?` to provide a default: `(.roles? // [])
Fields contain newlines	JSON strings with embedded newlines are preserved by `jq`.	Use `@csv` or `@tsv` to escape them, or replace newlines in `awk`: `gsub(/\n/, "\\n", $2)`
CSV header missing	You didn’t add a header line.	Prepend with `printf 'id,name,email,roles\n'` or use `awk`’s `NR==1{print header; next}` trick.
Performance slow on 10 GB JSON	`jq` loads the whole file.	Switch to `--stream` or split the JSON into smaller chunks before processing.

Putting it into a reusable script

#!/usr/bin/env bash
set -euo pipefail

INPUT="${1:-users.json}"
OUTPUT="${2:-users.csv}"

jq -r '.users[] | [.id, .name, .email, (.roles? // []) | join(";")] | @tsv' "$INPUT" |
awk -F'\t' 'BEGIN{OFS=","} {print $1,$2,$3,$4}' > "$OUTPUT"

echo "CSV written to $OUTPUT"

Make it executable and add it to your PATH. The script is safe to run on any JSON that follows the expected schema, and it will fail fast if the input is malformed.

When to choose awk over jq

You need to post‑process the CSV (e.g., filter rows, compute aggregates) in the same pipeline.
You’re working in an environment where jq is not available and installing it is undesirable.
You want to avoid the overhead of JSON parsing for

linux open-source

Why not just use jq alone?