From Raw JSON to Clean Records

Many targets are not HTML at all: the page loads, a hidden API returns JSON, and the only scraping problem left is turning a deeply nested, sometimes messy JSON tree into rows for a database. This lesson is that transformation, applied to real payloads with defensive code instead of brittle ["a"]["b"]["c"] chains.

Read the Shape First

Never write extractors blind. Save one response and print the top keys and nested types before you write any loop:

import json, requests

payload = requests.get("https://api.example.com/search", params={"q": "phones"}).json()
print(payload.keys())
print(type(payload.get("results")), len(payload.get("results", [])))
print(json.dumps(payload["results"][0], indent=2)[:800])

That inspect-first habit answers three questions cheaply: is the container a dict or a list, where do records live (under results, items, data, hits or a wrapper object), and what does one record actually contain. Guessing the shape costs you a debugging cycle; printing it costs two seconds.

Traverse Defensively

JSON from production APIs is undefined where it matters least: a record may be missing an optional price or expose null instead of a number. Guard every access so a missing key yields a safe default instead of a KeyError that kills the crawl:

def get(record, *path, default=None):
    cur = record
    for key in path:
        if not isinstance(cur, dict) or key not in cur:
            return default
        cur = cur[key]
    return cur

price = get(rec, "price", "current", "amount", default=0.0)

For nested records stored as lists, write one small function per record type instead of repeating chains — a parse_product(rec) that returns a flat dict is easy to test in isolation and easy to reuse on both the first page and the forty-first.

Flatten Nested Objects

Storage likes flat rows; JSON likes trees. The json_normalize helper from pandas collapses dotted paths into columns automatically, so {"address": {"city": "Berlin"}} becomes a top-level address.city field. It also accepts a record_path to unwrap a list of nested records and keeps parent fields via meta.

import pandas as pd

df = pd.json_normalize(
    payload["results"],
    record_path="offerings",
    meta=["id", "title", ["seller", "name"]],
)
records = df.to_dict("records")

When every column becomes a dotted string, that is fine — rename them at the storage boundary. The point of normalizing is that price, price.currency and offering.0.id land as actual columns you can filter, sort and dedupe, instead of living one indent deep forever.

Deduplicate by a Stable ID

JSON payloads are not guaranteed to be unique across pages — the same product appears on page 1 and page 3. Dedupe by a stable natural key (an id, sku, or canonical slug) rather than by title or URL, and treat the rest of the pipeline as a per-key normalization:

seen = set()
unique = []
for rec in records:
    key = get(rec, "id")
    if key is not None and key not in seen:
        seen.add(key)
        unique.append(rec)

This is the same dedupe discipline the storing-data lesson demands, applied before the data reaches the database so upserts become idempotent.

Respect Pagination In the Payload

JSON APIs paginate with their own vocabulary — page/pageSize, offset/limit, or opaque cursor/next tokens. Read the metas carefully: a next cursor is usually an absolute URL or token you pass back verbatim, while total/hasMore govern how long you keep looping. Mirror the browser's own requests (the XHR lesson) so you use the exact fields the site itself uses; then the JSON-to-records step above is the last thing in the chain. Data that starts as JSON ends as rows — the middleware never touches HTML.