# Food API schema

Version 1.0.0

Use this guide to load the dataset, join nutrition records to products and interpret missing values.

## Choose a format

`products.sqlite` contains the complete dataset in a single SQLite database. Open it with any SQLite client or library.

`products.jsonl` contains one product per line. Each product includes a `nutrition_sets` array, with a `nutrients` array inside each set.

The CSV version uses three files: `products.csv`, `nutrition_sets.csv` and `nutrition_nutrients.csv`. They contain the same columns as the corresponding database tables. Join nutrition sets to products using `product_id`. Join nutrients to sets using both `product_id` and `nutrition_set_id`.

Product IDs are integers and remain stable across releases. Barcodes are text containing 14 digits. Preserve leading zeros when importing them.

## Products

One row per product. Missing product facts are null; inclusion does not require an ingredient statement, a brand, nutrition or an image. Lists and objects are stored as JSON text in SQLite and CSV. In JSONL, each product contains its nutrition sets and their nutrients.

| Field | Type | Required | Meaning |
| --- | --- | --- | --- |
| `id` | `integer` | yes | Permanent product id. Gaps are normal; an id is never renumbered or reused, so it is safe to key on across releases. |
| `gtin` | `string` | yes | The product barcode in canonical GTIN-14 form (14 digits, left-zero-padded: a UPC-A `28239002169` appears as `00028239002169`). Checksum-validated. To look a scanned code up, pad it to 14 digits first. |
| `name` | `string` | no | Product title as sold, including variant and package wording (`(4 pack) …`, `…, 16 oz`). |
| `brand` | `string` | no | Brand name. |
| `product_type` | `string` | no | `food`, `beverage` or `supplement`. |
| `category` | `string[]` | no | Category path from broad to narrow, e.g. `["Food", "Snacks, Cookies & Chips", "Cookies"]`. |
| `net_content` | `string` | no | Package quantity as printed, e.g. `16 oz`, `12 fl oz, 12 count`. |
| `pack_count` | `integer` | no | Number of units in a multipack, when stated. |
| `food_condition` | `string` | no | Storage condition as stated, lowercased: `shelf_stable`, `refrigerated`, `frozen`, `dry`, `ready-to-eat`, … |
| `food_form` | `string` | no | Physical form as stated, lowercased, e.g. `whole`, `powder`, `liquid`. |
| `flavor` | `string` | no | Flavor as stated. |
| `dietary_claims` | `string[]` | no | Claims as stated on the listing or pack, e.g. `Gluten-Free`, `Organic`, `Vegetarian`, `Natural`. |
| `organic` | `boolean` | no | `true` when the product carries an organic claim; otherwise `null`, never `false`. |
| `nutrition_default_set_id` | `string` | no | ID of the selected nutrition set owned by this product; null means no selected default. |
| `ingredients` | `string` | no | The full ingredient statement as printed, with formatting cleaned but no words changed. |
| `ingredients_parsed` | `object[]` | no | The statement as a tree of components and ingredients in label order; see [Ingredient tree](#ingredient-tree). Null when the statement could not be parsed reliably; the text is still in `ingredients`. |
| `sweeteners` | `string[]` | no | Non-nutritive sweeteners and sugar alcohols named in the ingredients, as identities in order of appearance: `aspartame`, `sucralose`, `acesulfame_potassium`, `saccharin`, `steviol_glycosides`, `monk_fruit_extract`, `erythritol`, `xylitol`, `sorbitol`, `maltitol`, `allulose`, … Sugars, syrups and honey are not listed. |
| `allergens_declared` | `string[]` | no | Allergens the package declares it contains (`Contains: …`), as identities: `milk`, `egg`, `wheat`, `soy`, `peanut`, `tree_nut`, `almond`, `cashew`, `walnut`, `pecan`, `hazelnut`, `pistachio`, `macadamia`, `brazil_nut`, `sesame`, `fish`, `shellfish`, `crustacean`, `mollusc`, `mustard`, `celery`, `sulfite`, `coconut`, `gluten`. A declaration that names a species (`sardines`, `tuna`, `cod`; `shrimp`, `crab`; `clams`, `oysters`) is read as its class (`fish`, `crustacean`, `mollusc`); the literal word `shellfish` stays `shellfish`. |
| `allergens_may_contain` | `string[]` | no | Precautionary allergens, same identities: `May contain …` claims and facility, equipment or line notes (`Made in a bakery that may also use …`). |
| `allergens_statement` | `string` | no | The allergen wording verbatim, including facility notes. |
| `caffeine_statement` | `string` | no | The caffeine claim verbatim: `Caffeine-Free`, `Decaffeinated`, `Naturally Caffeinated`. |
| `caffeine_amount_mg` | `number` | no | Declared caffeine amount in milligrams, paired with an explicit `caffeine_basis`. Unknown amounts stay `null`; a caffeine-free or decaffeinated claim alone does not supply a number. |
| `caffeine_basis` | `string` | no | Quantity to which the caffeine amount applies, e.g. `per 15 fl oz (443 mL) can`. For multipacks, a per-can amount describes one can. Both caffeine fields are null when the amount or its basis is unverified. |
| `alcoholic_beverage` | `boolean` | yes | `true` when the product is an alcoholic beverage (a stated ABV of 0.5% or more, or listed as one). |
| `alcohol_percent` | `number` | no | Alcohol by volume as stated, e.g. `4.7`. Null when not stated. |
| `warnings` | `string[]` | no | Package warnings verbatim: Proposition 65 notices, phenylketonuria statements, choking and heat cautions, allergen warning text. |
| `directions` | `string` | no | Preparation, use and safe-handling instructions as supplied. |
| `countries_of_sale` | `string[]` | yes | ISO 3166 country codes where the product is sold, e.g. `["US"]`. |
| `additives`, `nutri_score`, `nova_group` | null | no | Reserved, always `null` in this version. |
| `image_name` | `string` | no | Filename of the main product image; see [Images](#images). |
| `label_image_names` | `string[]` | no | Reserved; always `null`. This download includes primary product images only. |
| `source_name` | `string[]` | yes | Unique source names contributing to this product, such as `["walmart"]`. Multiple sources may contribute to the same product. |
| `updated_at` | `string` | yes | ISO 8601 timestamp of the release in which this record's data last changed; see [Updates](#updates). |

## Nutrition sets

Primary key `(product_id, id)`. `id` is the set ID, scoped to its product. `position` preserves canonical array ordering; read with `ORDER BY position`. The deferred foreign key from `(products.id, nutrition_default_set_id)` to this key prevents cross-product defaults. A null default selects nothing even if alternative sets exist.

| Column | Type | Meaning |
| --- | --- | --- |
| product_id | integer | Owning product |
| id | text | Set ID, unique within the product |
| position | integer | Nonnegative array position, unique within product |
| preparation | text | `as_sold`, `as_prepared`, `unknown` |
| heading | nullable text | Printed heading |
| component_name | nullable text | Component or assortment member described by this panel, if stated |
| preparation_description | nullable text | Preparation instructions or context |
| serving_size | nullable text | Printed serving wording |
| servings_per_container | nullable number | Supported numeric count; unknown stays null |
| servings_per_container_text | nullable text | Original count wording |
| basis | text | `per_serving`, `per_100g`, `per_100ml`, `per_container`, `per_declared_quantity`, `unknown` |
| reference_quantity_present | 0 / 1 | Distinguishes null reference object from a populated one |
| reference_quantity_amount | nullable number | Strictly positive when known |
| reference_quantity_unit | nullable text | Normalized reference unit |
| reference_quantity_text | nullable text | Original reference wording |

JSONL groups the reference quantity columns into `reference_quantity`, which is either `null` or an object with `amount`, `unit` and `text`. It omits `product_id` and `position` because the parent object and array order provide that information. Separate sets describe different serving sizes, preparation methods or components. A component panel is not selected as the default for the whole product.

## Nutrition nutrients

Primary key `(product_id, nutrition_set_id, name)`, composite foreign key to the owning set. `position` preserves canonical ordering within the set. There is one row per named nutrient; amount and percentage are separate columns in that row.

| Column | Type | Meaning |
| --- | --- | --- |
| product_id | integer | Owning product |
| nutrition_set_id | text | Owning nutrition set |
| position | integer | Nonnegative array position, unique within set |
| name | text | Nutrient name |
| amount | nullable number | Exact nonnegative amount; printed zero remains zero |
| unit | nullable text | Unit of amount or bound; null when unknown, even with a known amount |
| daily_value_percent | nullable number | Printed nonnegative daily value; values above 100 are valid |
| bound | nullable text | `less_than`, `less_than_or_equal`, or null |
| bound_value | nullable number | Nonnegative threshold; null if unreadable/unknown |
| amount_text | nullable text | Verbatim amount wording |

Exact amount and bound are mutually exclusive. A threshold without an operator is invalid. Unknown threshold and unit may independently remain null. Daily-value-only rows are valid. No missing value means zero. Numeric fields must be finite. Nutrient comparisons require known compatible units, basis and quantity context; a SQL join does not make unlike servings comparable. For example, a nutrient can have 350% DV without an absolute quantity, and a fiber entry can retain a bound with an unknown threshold or unit. Caffeine claims remain separate from nutrition panels unless the panel itself contains caffeine.

## Ingredient tree

`ingredients_parsed` is an array of component objects. Each has `component`, which may be null, and an `ingredients` array. Each ingredient has a `name`, a nullable `max_percent` for stated percentage limits, and a nullable `sub_ingredients` array. The order follows the ingredient statement. Use `ingredients` for the full text.

## Images

`image_name` identifies the primary product photo. `images-manifest.json` maps each filename to its image ZIP. Download the parts you need and extract them into the same directory. Each ZIP is at most 2 GB and each image is smaller than 500 KB. Images may be JPEG, PNG or WebP; use the filename extension to determine the format. Label images are not included.

## Loading CSV and JSONL

CSV files use UTF-8, quoted fields and a header row. Keep barcodes as text. A cell containing exactly `\N` means null. An empty cell means an empty string. A non-null value beginning with a backslash has an extra leading backslash; remove one after checking for the null marker. Arrays and objects are encoded as JSON text. Booleans use `0` and `1`.

JSONL uses native JSON arrays, objects, booleans and nulls. Read one line at a time to avoid loading the entire file into memory.

```python
import json

with open("products.jsonl", encoding="utf-8") as records:
    for line in records:
        product = json.loads(line)
        # Process one product at a time.
```

## Querying nutrition

This query returns calories from each product's default panel when it describes one serving. Other panels may use different quantities or preparation methods, so check the context before comparing values.

```sql
SELECT p.id, p.name, s.serving_size, n.amount, n.unit
FROM products AS p
JOIN nutrition_sets AS s
  ON s.product_id = p.id AND s.id = p.nutrition_default_set_id
JOIN nutrition_nutrients AS n
  ON n.product_id = s.product_id AND n.nutrition_set_id = s.id
WHERE s.basis = 'per_serving' AND n.name = 'Calories'
  AND n.bound IS NULL AND n.amount IS NOT NULL AND n.unit = 'kcal';
```

```python
import sqlite3

with sqlite3.connect("products.sqlite") as db:
    for row in db.execute("SELECT id, gtin, name FROM products LIMIT 10"):
        print(row)
```

## Updates

`updated_at` records when a product's data last changed. Product IDs are never reused. Gaps in IDs are normal and do not indicate an incomplete download. When importing a new complete release, replace your existing dataset with that complete release. A differential refresh contains changes only and must be applied to your existing snapshot; do not use it as a full replacement.

`product-aliases.jsonl` maps retired IDs to their replacements. The database also includes a `product_aliases` table for these mappings and a `meta` table containing release information.

## Checking your downloads

`checksums.json` records the SHA-256 fingerprint of each file. Verification is optional, but helps detect incomplete or damaged downloads. Place it beside the files and run this script to check the files you downloaded:

```python
import hashlib
import json
from pathlib import Path

checksums = json.loads(Path("checksums.json").read_text())
for name, expected in checksums.items():
    path = Path(name)
    if not path.is_file():
        continue
    digest = hashlib.sha256()
    with path.open("rb") as stream:
        for chunk in iter(lambda: stream.read(1024 * 1024), b""):
            digest.update(chunk)
    print(name, "OK" if digest.hexdigest() == expected else "MISMATCH")
```