Puzzleshot #018 - JSON to CSV

raw data→api→jq→result

In Puzzleshot 017 we introduced jq for filtering and extracting fields from JSON. This week — a practical extension. Sometimes you need to get JSON data into CSV format for use with other tools, pipelines, or colleagues who don’t work with JSON directly.

Same PhishStats data as before — work against your saved local file:

jq '.' phishing.json

The challenge:

Using jq, convert the PhishStats JSON response to CSV containing just the following fields:

ip
countrycode
isp
tld

Include a header row in the output.

Things to consider:

How does jq handle CSV output?
How do you add a header row?
What happens with fields that contain commas or quotes?
What could you do with this CSV output once you have it?

Reveal solution

This puzzle we converted JSON to CSV using jq — a practical skill for getting JSON data into a format other tools can work with.

The solution:

jq -r '["ip","countrycode","isp","tld"], (.[] | [.ip, .countrycode, .isp, .tld]) | @csv' phishing.json

Breaking it down:

["ip","countrycode","isp","tld"] — the header row as a jq array, output first before the data.

.[] | [.ip, .countrycode, .isp, .tld] — iterates over every record and builds an array of just the four fields we want.

| @csv — converts each array to a properly formatted CSV line. This is the key jq builtin for CSV output.

-r — raw output mode. Without this jq wraps each line in JSON quotes. -r gives you clean plain text output.

On the embedded comma and quote question:

@csv handles this correctly and automatically — fields containing commas or quotes are properly quoted and escaped. This is one of jq’s genuinely useful behaviors that naive string concatenation with commas would get wrong.

What you can do with the output:

Save to a file for use elsewhere:

jq -r '["ip","countrycode","isp","tld"], (.[] | [.ip, .countrycode, .isp, .tld]) | @csv' phishing.json > phishing_subset.csv

Or pipe directly into DuckDB for analytics — which should feel familiar by now:

jq -r '["ip","countrycode","isp","tld"], (.[] | [.ip, .countrycode, .isp, .tld]) | @csv' phishing.json | \
duckdb -c "
  SELECT countrycode, count(*) as count
  FROM read_csv('/dev/stdin')
  GROUP BY countrycode
  ORDER BY count DESC
"

That last example brings jq and DuckDB together — jq handles the JSON to CSV conversion, DuckDB handles the analytics. A natural combination worth exploring further.