Blog / Data engineering

Save API JSON to Parquet in Python

29 Sept 2026Perceptlayer6 min read

To save JSON to Parquet in Python, flatten each record into a dict, build a pyarrow table with an explicit schema, and write it with pyarrow.parquet.write_to_dataset, partitioned by date. The explicit schema is the part most examples skip, and it's the part that breaks first: in the recorded Google Shopping response used here, extracted_price is 441 for one product and 393.73 for another, which type inference reads as two different types.

What do you need?

Python 3.10 or later and pyarrow. The code here ran on pyarrow 26.0.0, installed with pip install pyarrow requests. You also need API responses to store. This tutorial uses GET /v1/google-shopping/search, which returns Google Shopping results with a title, a shop, a price as text and as a number, a rating and a review count.

curl "https://api.perceptlayer.com/v1/google-shopping/search?q=iphone+15&gl=us&hl=en" \
  -H "X-API-Key: $API_KEY"

That's the exact request behind the recorded response the code was tested on. Get a free API key for your own calls; keys start with sk_live_ and go in the X-API-Key header, as the authentication docs describe. The Google Shopping API guide covers the endpoint's other parameters.

What does the JSON look like before you flatten it?

The response is a search_parameters object, a shopping_results array and pagination. Each result is a flat object except extensions, a list of strings. Here are two results from the recording, trimmed with …:

{
  "search_parameters": { "q": "iphone 15", "gl": "us", "hl": "en", "start": 0, "device": "desktop" },
  "shopping_results": [
    {
      "position": 1,
      "category": "Popular products",
      "title": "Apple iPhone 15",
      "source": "Walmart - Wireless Source",
      "rating": 4.7,
      "reviews": 45000,
      "extensions": [" & more"],
      "product_id": "4263606017411565992",
      "link": "https://www.google.com/shopping/product/4263606017411565992",
      "price": "$393.73",
      "extracted_price": 393.73,
      …
    },
    {
      "position": 2,
      "title": "Apple iPhone 15 128GB",
      "source": "Best Buy",
      "price": "$441.00",
      "extracted_price": 441,
      …
    },
    …
  ],
  "pagination": { "current_page": 1, "start": 0, "next_start": 10 }
}

Three things in it shape the schema. extracted_price is a whole number when the price has no cents. extensions starts with a non-breaking space ( ). And product_id is digits, but it's an identifier, not a quantity, so keep it a string.

Step 1: Why do you need an explicit schema?

Because pyarrow infers each column's type from the values it sees, and API data doesn't keep its types steady. A day where every product happens to have a whole-dollar price infers extracted_price as int64; a day with cents infers double. Writing those days separately works. Reading them back as one dataset, or concatenating them, fails.

We ran exactly that on the recorded results: one table from the whole-dollar result, one from the others, then pa.concat_tables:

import pyarrow as pa

a = pa.Table.from_pylist([{"price": 441}])
b = pa.Table.from_pylist([{"price": 393.73}])
pa.concat_tables([a, b])
# pyarrow.lib.ArrowInvalid: Schema at index 1 was different:
# price: int64
# vs
# price: double

The fix is to decide the types once. An explicit schema also documents the data: anyone reading the code sees every column and its type, and a field the API adds later doesn't silently become a new column.

Step 2: How do you define the schema and flatten the rows?

List every column you keep, with its type, and write a function that turns one response into rows. Cast extracted_price to float, keep the original price text next to it, strip the whitespace from extensions, and add the collection time and the query, which aren't in each result but are what you'll filter on later.

"""Write Google Shopping responses to a date-partitioned Parquet dataset with a fixed schema."""
import datetime

import pyarrow as pa
import pyarrow.parquet as pq

SCHEMA = pa.schema([
    ("date", pa.string()),                      # partition key, YYYY-MM-DD
    ("collected_at", pa.timestamp("s", tz="UTC")),
    ("query", pa.string()),
    ("gl", pa.string()),
    ("position", pa.int32()),
    ("product_id", pa.string()),
    ("title", pa.string()),
    ("source", pa.string()),
    ("price_text", pa.string()),                # as shown, e.g. "$441.00"
    ("price", pa.float64()),                    # extracted_price, always a float
    ("rating", pa.float64()),
    ("reviews", pa.int64()),
    ("category", pa.string()),
    ("extensions", pa.list_(pa.string())),
    ("link", pa.string()),
])


def rows(response, collected_at):
    params = response.get("search_parameters", {})
    for r in response.get("shopping_results", []):
        price = r.get("extracted_price")
        yield {
            "date": collected_at.date().isoformat(),
            "collected_at": collected_at,
            "query": params.get("q"),
            "gl": params.get("gl"),
            "position": r.get("position"),
            "product_id": r.get("product_id"),
            "title": r.get("title"),
            "source": r.get("source"),
            "price_text": r.get("price"),
            "price": float(price) if price is not None else None,
            "rating": r.get("rating"),
            "reviews": r.get("reviews"),
            "category": r.get("category"),
            "extensions": [e.strip() for e in r.get("extensions", [])],
            "link": r.get("link"),
        }

Fields the schema doesn't list, such as thumbnail and the long product_token, are dropped. That's deliberate: the recorded thumbnail is a placeholder data URI, and tokens are for follow-up calls, not analysis. If you might want them later, keep the raw JSON too, gzipped, alongside the Parquet.

When you do need a new column, add it to the end of SCHEMA as a nullable field and start filling it from that day. Older files simply lack it, and a dataset read with the new schema returns nulls for those days. Never change an existing column's type in place; add a new column instead and backfill it if you need to, so old and new files still read as one dataset.

Step 3: How do you write a date-partitioned dataset?

Build one table per day with the schema and hand it to write_to_dataset with partition_cols=["date"]. pyarrow writes a folder per value, shopping/date=2026-10-24/, and moves the date column into the folder name, the Hive-style layout that query engines read (Apache Arrow, pyarrow.parquet.write_to_dataset, 2026). zstd compression is a sound default for text-heavy columns.

def write_day(responses, root="shopping", collected_at=None):
    """One call per day: every response collected that day goes into one file."""
    collected_at = collected_at or datetime.datetime.now(datetime.timezone.utc).replace(microsecond=0)
    records = [row for resp in responses for row in rows(resp, collected_at)]
    table = pa.Table.from_pylist(records, schema=SCHEMA)
    pq.write_to_dataset(table, root_path=root, partition_cols=["date"], compression="zstd")
    return table.num_rows

Collect a day's responses first, then call write_day once. Fetching looks like this, one call per query; it's a sketch we haven't run against the live API:

import os
import requests

URL = "https://api.perceptlayer.com/v1/google-shopping/search"
HEADERS = {"X-API-Key": os.environ["API_KEY"]}
QUERIES = ["iphone 15", "pixel 9", "galaxy s25"]

responses = []
for q in QUERIES:
    r = requests.get(URL, headers=HEADERS, params={"q": q, "gl": "us", "hl": "en"}, timeout=60)
    if r.ok:
        responses.append(r.json())
print(write_day(responses), "rows written")

Failed calls are skipped and cost nothing; for retries, see handling web data API errors in Python.

Step 4: How do you read it back?

Open the folder as a dataset with Hive partitioning and filter on date. pyarrow reads only the matching folders, which is the point of partitioning: a query for one day doesn't touch the others.

import pyarrow.dataset as ds

day = ds.dataset("shopping", format="parquet", partitioning="hive").to_table(
    filter=ds.field("date") == "2026-10-24"
)
print(day.select(["source", "price_text", "price"]).to_pylist())

We wrote the recorded response as two days, 23 and 24 October, and read back the second. It returned three rows with price as double on every one, including the whole-dollar 441.0:

[{'source': 'Walmart - Wireless Source', 'price_text': '$393.73', 'price': 393.73},
 {'source': 'Best Buy', 'price_text': '$441.00', 'price': 441.0},
 {'source': 'T-Mobile', 'price_text': '$17.50/mo', 'price': 17.5}]

The third row shows why price_text is worth keeping: the T-Mobile result is a monthly instalment, and extracted_price holds only the number. Filter rows whose text ends in /mo before you compare prices. The same output in DuckDB or pandas is one line, since both read Hive-partitioned Parquet. For loading the same rows into a database instead, see loading API JSON into Postgres.

Is Parquet smaller than gzipped JSON?

Not for small files. On the recorded response, the full JSON was 2,068 bytes and 896 gzipped; the Parquet file for its three rows was 4,910 bytes. The file's metadata footer alone was 3,019 bytes, because Parquet stores the schema and column statistics in every file (Apache Parquet file format, 2026).

That overhead is per file, not per row, so it shrinks in relative terms as files grow. We measured only the three recorded rows, so we can't give you a ratio for a full day; measure it on your own data. The practical rule follows from the footer: write one file per day, not one per API response, and Parquet's typed, column-by-column storage starts to pay. Its other advantage doesn't depend on size: a reader can load just price and date without reading titles and links.

What does the data collection cost?

The Parquet steps run locally and cost nothing; only the API calls cost credits. Each /v1/google-shopping/search call costs 1 credit on success, and failed calls cost nothing and return X-Credits-Used: 0. The credits docs explain billing.

Worked example: 100 product queries a day is 100 × 30 = 3,000 credits a month; a Starter pack of 10,000 credits covers three months of it, and purchased credits last 12 months. Thirty queries a day, 900 credits, fit in the 1,000 free monthly credits. See pricing for what credits cost. To run the job daily, our guide to scheduling web data pulls compares cron, queues and workflow tools.

To start collecting, get a free API key and run a query in the playground on the Google Shopping API page. Data is collected from publicly available sources and returned as found at the time of the call.

parquetpythonpyarrowdata engineeringjson