Puzzleshot #017 - Phishing with jq

raw data→API→jq→result

In Puzzleshot #016 we fetched and queried JSON data using Python and DuckDB. This time let’s introduce a tool that’s worth having in your toolkit whenever you’re working with JSON from the command line — jq.

jq is to JSON what awk is to CSV — a lightweight, powerful command-line tool for parsing, filtering, and transforming JSON data. It’s available on most Unix systems and pairs naturally with curl.

Same PhishStats API as last week:

https://api.phishstats.info/api/phishing?_sort=-id&_size=100

The challenge:

Using curl and jq, answer the following:

Pretty print a single record to explore the schema
Extract just the ip and countrycode fields from all 100 entries
Filter the results to only show entries where countrycode is “US”

A starting point:

curl -s 'https://api.phishstats.info/api/phishing?_sort=-id&_size=100' | jq '.'

Note: PhishStats limits anonymous requests to 50 per day per IP. Save your API response to a local file first to avoid hitting the limit while testing your jq expressions:

curl -s 'https://api.phishstats.info/api/phishing?_sort=-id&_size=100' > phishing.json

Then work against the local file:

jq '.' phishing.json

Things to consider:

How do you access a specific field in a jq expression?
How do you filter an array of objects based on a field value?
How does jq compare to awk for this kind of work?
Where does jq start to feel awkward?

Reveal solution

This puzzle we introduced jq — a lightweight command-line tool for parsing and filtering JSON. Let’s work through the three challenges using our saved PhishStats data.

Pretty print a single record:

jq '.[0]' phishing.json

.[0] accesses the first element of the array. A good way to explore the schema without scrolling through 100 records.

Extract just ip and countrycode from all entries:

jq '[.[] | {ip, countrycode}]' phishing.json

.[] iterates over every element in the array. {ip, countrycode} constructs a new object with just those two fields. The outer [] wraps the results back into an array.

Filter to US entries only:

jq '[.[] | select(.countrycode == "US") | {ip, countrycode}]' phishing.json

select() filters elements based on a condition — only passes through records where countrycode equals “US”. The pattern of .[] | select() | {fields} is a core jq technique worth remembering.

Where jq gets awkward:

Counting and basic filtering are clean. Aggregation is where jq starts to show its limits. You can count US entries:

jq '[.[] | select(.countrycode == "US")] | length' phishing.json

But grouping and counting by value — the equivalent of GROUP BY countrycode — requires group_by and map which gets verbose fast:

jq '[.[] | .countrycode] | group_by(.) | map({country: .[0], count: length}) | sort_by(-.count)' phishing.json

That works but it’s significantly less readable than a SQL GROUP BY. This is exactly where DuckDB takes over cleanly — and that’s a combination worth exploring in a future Puzzleshot.