JSON to product feed

Why JSON has to be flattened first

A JSON product export is a tree. In the example below, one product object holds a variants array, an images array and option values, so two products carry three sellable items. A product feed is a table: Google's product data specification wants one row per variant, linked by item_group_id, with id, price, availability, color and size on every row. Meta's catalog and Pinterest's catalog work the same way. Nothing in that chain walks a nested tree for you. Merchant Center's file formats are delimited text and RSS or Atom XML, plus Google Sheets and the Merchant API. Meta and Pinterest take CSV, TSV and XML.

Textfinity sits after the flatten. Its sources are Shopify (native, with variants), a CSV or other delimited file reachable by URL or SFTP, Google Shopping-format XML, and Google Sheets. A JSON URL is detected only so the app can ask you for a CSV. That keeps the flattening rule (which object is a row, which array becomes a column) in a script you own and can read, and the channel rules (column names, currency, availability words) in Textfinity, where they apply to every feed.

The source: a typical nested product JSON

This is the shape most headless platforms and product APIs return by default: product-level keys (id, title, vendor, url), an images array of objects, and a variants array with sku, price, inventory and option values. Prices arrive as strings with no currency, and inventory is a count rather than a word. Both recipes below turn exactly this file into the CSV in the download.

[
  {"id": 8821, "title": "Ribbed Crop Tank", "vendor": "Loom",
   "url": "https://store.example/p/ribbed-tank",
   "images": [{"src": "https://cdn.example/tank-1.jpg"},
              {"src": "https://cdn.example/tank-2.jpg"}],
   "variants": [
     {"sku": "TANK-SAGE-S", "price": "24.00", "inventory": 12,
      "option1": "Sage", "option2": "S"},
     {"sku": "TANK-SAGE-M", "price": "24.00", "inventory": 0,
      "option1": "Sage", "option2": "M"}
   ]},
  {"id": 9912, "title": "Linen Lampshade", "vendor": "Hearth",
   "url": "https://store.example/p/linen-lampshade",
   "images": [{"src": "https://cdn.example/lamp-1.jpg"}],
   "variants": [
     {"sku": "LS-LINEN-01", "price": "59.00", "inventory": 4,
      "barcode": "00195866188174"}
   ]}
]

Flatten it with jq

jq is a small command-line JSON processor available in most package managers and CI images. The filter prints a header, then, for every variant of every product, one array combining the parent's keys with the variant's keys, written out as CSV. The first image becomes image_link and the remaining images are joined with commas into more_images. jq's @csv quotes every text field; the values are the same as the Python output below.

jq -r '
["product_id","title","vendor","url","image_link","more_images",
 "sku","price","inventory","option1","option2","barcode"],
(.[] | . as $p | .variants[] |
  [$p.id, $p.title, $p.vendor, $p.url, $p.images[0].src,
   ([$p.images[1:][].src] | join(",")),
   .sku, .price, .inventory, .option1, .option2, .barcode])
| @csv' products.json > products.csv

Or flatten it with Python

The same logic in Python's standard library, for when the flatten runs inside an existing script or needs to call a paginated API first. Missing keys (the lampshade has no options, the tank has no barcode) become empty cells, not errors. For large catalogs, write rows as you read pages instead of loading the whole API response at once; the loop body stays the same.

import csv, json

products = json.load(open("products.json"))
cols = ["product_id", "title", "vendor", "url", "image_link", "more_images",
        "sku", "price", "inventory", "option1", "option2", "barcode"]
with open("products.csv", "w", newline="") as f:
    out = csv.writer(f, lineterminator="\n")
    out.writerow(cols)
    for p in products:
        imgs = [i["src"] for i in p.get("images", [])]
        for v in p["variants"]:
            out.writerow([p["id"], p["title"], p["vendor"], p["url"],
                          imgs[0] if imgs else "", ",".join(imgs[1:]),
                          v["sku"], v["price"], v.get("inventory", ""),
                          v.get("option1", ""), v.get("option2", ""),
                          v.get("barcode", "")])

From the flat CSV to channel feeds in Textfinity

Publish products.csv at a stable address: a web server or storage bucket over HTTPS, an SFTP server, or a Google Sheet the script writes to. We add that address as the source in Textfinity. It detects the delimiter, lists the columns, counts the products and runs feed hygiene, which reports fill rate per column, over-long values, mixed languages, inconsistent spellings and malformed image URLs. From there each channel gets its own feed from the same source: the Google Shopping, Meta Ads, Pinterest Ads, TikTok Ads, Microsoft Ads, Snapchat Ads and Amazon Ads templates pre-name the columns, and a custom feed covers channels without a template, such as Idealo or Awin.

Every feed is a CSV or TSV file on a stable URL, rebuilt on the shop's sync schedule and within about five minutes of a mapping edit (median rebuild 10.9 seconds on our platform). Merchant Center fetches that CSV in production on a scheduled fetch, even though Google's help page lists .txt, .tsv and .xml by extension. Commerce Manager and the other channels fetch their feed URL the same way; we register each one with you.

The Google Shopping feed that comes out

Three rows, one per variant, in Google's attribute names: the two tank sizes share item_group_id 8821, the sold-out size reads out_of_stock, the currency is attached to the price, and the lampshade keeps its plain title because it has no options.

id,item_group_id,title,link,image_link,additional_image_link,availability,price,brand,gtin,condition,color,size
TANK-SAGE-S,8821,Ribbed Crop Tank – Sage S,https://store.example/p/ribbed-tank,https://cdn.example/tank-1.jpg,https://cdn.example/tank-2.jpg,in_stock,24.00 USD,Loom,,new,Sage,S
TANK-SAGE-M,8821,Ribbed Crop Tank – Sage M,https://store.example/p/ribbed-tank,https://cdn.example/tank-1.jpg,https://cdn.example/tank-2.jpg,out_of_stock,24.00 USD,Loom,,new,Sage,M
LS-LINEN-01,9912,Linen Lampshade,https://store.example/p/linen-lampshade,https://cdn.example/lamp-1.jpg,,in_stock,59.00 USD,Hearth,00195866188174,new,,