Puzzleshot #019 - Github API and jq

raw data→api→jq→result

In Puzzleshot 017 and 018 we worked with the PhishStats API which returned relatively flat JSON. This puzzle we’re switching to the GitHub API — which returns more interesting output: nested arrays and hashes within each record.

Setup

Save the response locally first to avoid rate limits (60 unauthenticated requests per hour):

curl -s 'https://api.github.com/search/repositories?q=duckdb&sort=stars&per_page=50' > github.json

Each repository record contains a topics field — an array of strings representing how the repo is tagged. For example:

{
  "name": "duckdb",
  "topics": ["analytics", "database", "embedded-database", "olap", "sql"]
}

The challenge:

Using jq:

Count the total number of repository records in the response
Extract the repo name and topics, exploding each topic into its own row, and count the resulting rows
Output the exploded results as CSV with two columns — repo and topic

Things to consider:

How does jq iterate over an array within a record?
Why is the exploded row count higher than the record count?
What would you do with this CSV output next?

Reveal solution

This puzzle we worked with nested arrays in the GitHub API response — specifically the topics field, which is an array of strings per repository.

Step 1 — Count the total records:

jq '.items | length' github.json

.items accesses the array of repositories in the response. length gives you the count. For our sample query this returns 50.

Step 2 — Explode topics and count the resulting rows:

jq '[.items[] | {repo: .name, topic: .topics[]}] | length' github.json

.items[] iterates over each repository. .topics[] is the main point of interest — it iterates over each topic within that repository’s topics array, producing one output per topic rather than one per repository. {repo: .name, topic: .topics[]} builds a flat object for every repo/topic pair. Wrapping the whole thing in [] collects the results into an array, and length counts them. For our sample query this returns 597 - note that your output might be different depending on the content when you query the Github API.

Why the count jumps from 50 to 597:

Each repository has anywhere from zero to many topics. A repo with 12 topics produces 12 rows in the exploded output — one per topic. This is the core idea behind array unwinding: nested data gets flattened into multiple flat rows, and the “row count” only makes sense once you’ve decided what a row represents. 50 repositories became 597 repo-topic pairs.

Step 3 — Output as CSV:

jq -r '["repo","topic"], (.items[] | .name as $repo | .topics[] | [$repo, .]) | @csv' github.json

["repo","topic"] outputs the header row. .name as $repo saves the repo name to a variable before iterating topics, since once you’re inside .topics[] you’ve lost direct access to the parent record. [$repo, .] builds each output row using the saved repo name and the current topic. @csv formats it properly, and -r strips the raw JSON string quoting.

What you’d do with this next:

The exploded CSV is now in a shape that’s easy to load into DuckDB or any other analytical tool — count topic frequency across repositories, find which topics commonly appear together, or join against other repository metadata. Flattening nested structures like this is often the necessary first step before any of that analysis is possible.