JSON API
Read a JSON API into a table with a records pointer, column pointers and one of two pagination routes.
The question
Section titled “The question”What was each economy’s total population in each year the World Bank reports?
The publisher returns JSON. The records sit below the root, the fields nest, and the full series does not fit in one response. All three are ordinary.
The source
Section titled “The source”| Publisher | World Bank Indicators API |
| Address | https://api.worldbank.org/v2/country/all/indicator/SP.POP.TOTL?format=json&per_page=5000&page=N |
| Format | JSON, served as application/json;charset=utf-8 |
| Cadence | the series is revised a few times a year |
| Rights | World Bank Open Data carries CC BY 4.0, recorded as contractual_license_asserted with the terms page digest as evidence |
This source needs no credential and states an explicit open licence. The body is
[metadata, [records]], so the records live at the RFC 6901 pointer /1. Each record nests its
economy under /country.
The recipe
Section titled “The recipe”{ "dataset": { "name": "Total population by economy and year", "description": "Each row is one economy in one year, carrying the World Bank's total population estimate for it, read from the World Bank Indicators API." }, "question": { "text": "What was each economy's total population in each year the World Bank reports?" }, "table": { "name": "population_by_year", "description": "One row per economy and calendar year.", "grain": [ "country_id", "year" ], "columns": [ { "name": "country_id", "type": "string", "nullable": false, "description": "The World Bank's two-letter code for the economy or aggregate.", "presentation": { "chart": "none" } }, { "name": "country_iso3", "type": "string", "nullable": true, "description": "The ISO 3166-1 alpha-3 code, empty for aggregates that have none.", "presentation": { "chart": "none" } }, { "name": "country_name", "type": "string", "nullable": false, "description": "The economy or aggregate's name, as the publisher writes it.", "presentation": { "chart": "top_values" } }, { "name": "year", "type": "integer", "nullable": false, "description": "The calendar year the estimate is for.", "presentation": { "chart": "histogram", "bins": 12 } }, { "name": "population", "type": "integer", "nullable": true, "description": "Total population at mid-year, counting all residents.", "presentation": { "chart": "histogram", "bins": 8, "story": "Most economies are small and a handful are enormous, so the counts pile up at the left." } } ] }, "sources": [ { "name": "population_page_1", "description": "Total population by economy and year, as the World Bank Indicators API serves it.", "source_class": "user_api", "data_classification": "public", "locator": { "kind": "https_url", "display_locator": "https://api.worldbank.org/v2/country/all/indicator/SP.POP.TOTL?format=json&per_page=5000&page=1" }, "rights_claim": { "claimed_basis": "contractual_license_asserted", "claim_evidence_digest": "sha256:2222222222222222222222222222222222222222222222222222222222222222", "claim_note": "World Bank Open Data is published under CC BY 4.0; the digest is of the terms page as read on 12 September 2026." }, "connector": { "adapter_id": "public.https", "credential_mode": "none", "origin": "https://api.worldbank.org", "parameters": [ { "name": "reader.family_id", "value": "json.tabular" }, { "name": "reader.family_version", "value": "1.1.0" }, { "name": "reader.decode_options", "value": "{\"columns\":[{\"name\":\"country_id\",\"pointer\":\"/country/id\",\"required\":true},{\"name\":\"country_iso3\",\"pointer\":\"/countryiso3code\",\"required\":false},{\"name\":\"country_name\",\"pointer\":\"/country/value\",\"required\":true},{\"name\":\"year\",\"pointer\":\"/date\",\"required\":true},{\"name\":\"population\",\"pointer\":\"/value\",\"required\":false}],\"document_format\":\"json\",\"expand\":[],\"records_pointer\":\"/1\"}" } ] }, "limits": { "max_source_bytes": 3145728, "max_rows": 10000, "max_requests": 2 } }, { "name": "population_page_2", "description": "Total population by economy and year, as the World Bank Indicators API serves it.", "source_class": "user_api", "data_classification": "public", "locator": { "kind": "https_url", "display_locator": "https://api.worldbank.org/v2/country/all/indicator/SP.POP.TOTL?format=json&per_page=5000&page=2" }, "rights_claim": { "claimed_basis": "contractual_license_asserted", "claim_evidence_digest": "sha256:2222222222222222222222222222222222222222222222222222222222222222", "claim_note": "World Bank Open Data is published under CC BY 4.0; the digest is of the terms page as read on 12 September 2026." }, "connector": { "adapter_id": "public.https", "credential_mode": "none", "origin": "https://api.worldbank.org", "parameters": [ { "name": "reader.family_id", "value": "json.tabular" }, { "name": "reader.family_version", "value": "1.1.0" }, { "name": "reader.decode_options", "value": "{\"columns\":[{\"name\":\"country_id\",\"pointer\":\"/country/id\",\"required\":true},{\"name\":\"country_iso3\",\"pointer\":\"/countryiso3code\",\"required\":false},{\"name\":\"country_name\",\"pointer\":\"/country/value\",\"required\":true},{\"name\":\"year\",\"pointer\":\"/date\",\"required\":true},{\"name\":\"population\",\"pointer\":\"/value\",\"required\":false}],\"document_format\":\"json\",\"expand\":[],\"records_pointer\":\"/1\"}" } ] }, "limits": { "max_source_bytes": 3145728, "max_rows": 10000, "max_requests": 2 } }, { "name": "population_page_3", "description": "Total population by economy and year, as the World Bank Indicators API serves it.", "source_class": "user_api", "data_classification": "public", "locator": { "kind": "https_url", "display_locator": "https://api.worldbank.org/v2/country/all/indicator/SP.POP.TOTL?format=json&per_page=5000&page=3" }, "rights_claim": { "claimed_basis": "contractual_license_asserted", "claim_evidence_digest": "sha256:2222222222222222222222222222222222222222222222222222222222222222", "claim_note": "World Bank Open Data is published under CC BY 4.0; the digest is of the terms page as read on 12 September 2026." }, "connector": { "adapter_id": "public.https", "credential_mode": "none", "origin": "https://api.worldbank.org", "parameters": [ { "name": "reader.family_id", "value": "json.tabular" }, { "name": "reader.family_version", "value": "1.1.0" }, { "name": "reader.decode_options", "value": "{\"columns\":[{\"name\":\"country_id\",\"pointer\":\"/country/id\",\"required\":true},{\"name\":\"country_iso3\",\"pointer\":\"/countryiso3code\",\"required\":false},{\"name\":\"country_name\",\"pointer\":\"/country/value\",\"required\":true},{\"name\":\"year\",\"pointer\":\"/date\",\"required\":true},{\"name\":\"population\",\"pointer\":\"/value\",\"required\":false}],\"document_format\":\"json\",\"expand\":[],\"records_pointer\":\"/1\"}" } ] }, "limits": { "max_source_bytes": 3145728, "max_rows": 10000, "max_requests": 2 } }, { "name": "population_page_4", "description": "Total population by economy and year, as the World Bank Indicators API serves it.", "source_class": "user_api", "data_classification": "public", "locator": { "kind": "https_url", "display_locator": "https://api.worldbank.org/v2/country/all/indicator/SP.POP.TOTL?format=json&per_page=5000&page=4" }, "rights_claim": { "claimed_basis": "contractual_license_asserted", "claim_evidence_digest": "sha256:2222222222222222222222222222222222222222222222222222222222222222", "claim_note": "World Bank Open Data is published under CC BY 4.0; the digest is of the terms page as read on 12 September 2026." }, "connector": { "adapter_id": "public.https", "credential_mode": "none", "origin": "https://api.worldbank.org", "parameters": [ { "name": "reader.family_id", "value": "json.tabular" }, { "name": "reader.family_version", "value": "1.1.0" }, { "name": "reader.decode_options", "value": "{\"columns\":[{\"name\":\"country_id\",\"pointer\":\"/country/id\",\"required\":true},{\"name\":\"country_iso3\",\"pointer\":\"/countryiso3code\",\"required\":false},{\"name\":\"country_name\",\"pointer\":\"/country/value\",\"required\":true},{\"name\":\"year\",\"pointer\":\"/date\",\"required\":true},{\"name\":\"population\",\"pointer\":\"/value\",\"required\":false}],\"document_format\":\"json\",\"expand\":[],\"records_pointer\":\"/1\"}" } ] }, "limits": { "max_source_bytes": 3145728, "max_rows": 10000, "max_requests": 2 } } ], "transform": { "engine": "duckdb_sql", "steps": [ { "step_id": "population_rows", "sql": "select country_id, country_iso3, country_name, year, population from population_page_1 union all select country_id, country_iso3, country_name, year, population from population_page_2 union all select country_id, country_iso3, country_name, year, population from population_page_3 union all select country_id, country_iso3, country_name, year, population from population_page_4", "description": "Append the four pages. Every page decodes to the same ordered columns, so the union needs no alignment." }, { "step_id": "population_by_year", "sql": "select country_id, country_iso3, country_name, cast(try_cast(year as decimal(6, 0)) as integer) as year, cast(try_cast(population as decimal(20, 2)) as bigint) as population from population_rows", "description": "Cast the appended text. Going through decimal first is what lets a value spelled 1234567.0 reach an integer column." } ] }, "checks": [ { "check_id": "one_row_per_economy_year", "kind": "key_uniqueness", "enforcement": "required", "columns": [ "country_id", "year" ], "description": "One row per economy per year; a repeated page would break this." }, { "check_id": "population_mostly_present", "kind": "null_ceiling", "enforcement": "required", "columns": [ "population" ], "max_null_ppm": 400000 }, { "check_id": "population_plausible", "kind": "value_range", "enforcement": "required", "columns": [ "population" ], "min_value": "0", "max_value": "20000000000" }, { "check_id": "every_page_landed", "kind": "row_expectation", "enforcement": "required", "min_rows": 1000, "description": "Four pages of 5000 records cover the whole series. The floor is low enough that a sample run under a per-source row ceiling still passes it." } ], "units": [ { "column": "population", "unit": "{count}" } ], "timezone": "UTC"}Part by part
Section titled “Part by part”Sources, and pagination without a credential
Section titled “Sources, and pagination without a credential”Four sources, one per page, differing only in the page query value. public.https performs one
request, so more than one page means more than one source, and the transform unions them.
A recipe names at most 256 sources. Read total and pages out of the publisher’s own metadata
block before deciding how many to declare, and add sources as the series grows. A page that comes
back empty contributes no rows, so an extra source costs one request.
The other route is the frozen pagination grammar, and it needs a credential.
The Reader pin
Section titled “The Reader pin”{"name": "reader.family_id", "value": "json.tabular"},{"name": "reader.family_version", "value": "1.1.0"},{"name": "reader.decode_options", "value": "{\"columns\":[…],\"document_format\":\"json\",\"expand\":[],\"records_pointer\":\"/1\"}"}All three or none. A source that names a decoder without pinning its settings cannot be replayed from its own record.
reader.decode_options is the canonical JSON text of the options the family accepts, with every
defaulted key written out. document_format, expand and records_pointer are all present here,
though two of them carry the family’s own default. A stated subset digests differently from the pin
the worker reconstructs, and the read refuses it.
The settings:
| Setting | Value | What it does |
|---|---|---|
document_format |
json |
a single JSON document rather than newline-delimited records |
records_pointer |
/1 |
the second member of the top-level array is the record list |
expand |
[] |
no nested array is expanded into extra rows |
columns |
five entries | each a name, an RFC 6901 pointer relative to the record, and a required flag |
A root-level array uses records_pointer: "". A nested array uses its pointer:
/features, /results, /data/items.
Use required: false where a publisher omits a key. Publishers often omit a key rather than
sending null. A missing required column refuses the whole decode. A missing optional column becomes
an empty cell. country_iso3 and population are optional here, because aggregates carry no ISO
code and old years carry no estimate.
json.tabular@1.1.0 also reads a document labelled as ordinary text, which 1.0.0 rejects. Pin
the highest certified version for a source you have not reviewed yet.
Transform
Section titled “Transform”Two steps. The first appends the four pages. Every page decodes to the same ordered columns, so the union needs no alignment. The second casts:
select country_id, country_iso3, country_name, cast(try_cast(year as decimal(6, 0)) as integer) as year, cast(try_cast(population as decimal(20, 2)) as bigint) as populationfrom population_rowsTRY_CAST('1234567.0' AS INTEGER) is null, and a JSON number rendered into CSV can arrive with a
trailing .0. Casting to decimal and then to an integer type reads both spellings.
Checks
Section titled “Checks”key_uniqueness on ["country_id", "year"] catches a page declared twice, or a page value that
repeated. Both produce duplicate rows that nothing else reports.
row_expectation has a floor of 1,000 rather than a tight range. A tight row expectation fails on
a sample run, because --max-rows truncates each source separately and the sample is four partial
pages.
Cadence
Section titled “Cadence”The publisher revises the series rather than appending to it, so this document declares no window.
That makes it resync-only. Studio refuses a scheduled refresh with RESYNC_REQUIRED before
acquisition, and reading the address again is an explicit resync. Run one when a new edition is due.
A snapshot window records what changed rather than what is current, as with a directory that only
shows the current state. It stamps every decoded row with the UTC day it was acquired on and keeps
one partition per day. A transition is then a lag() over that column. It is resync-only too, so
each new day is a resync. A request window is what gives this table a schedule, where the publisher
spells time in its address.
Register, run, inspect
Section titled “Register, run, inspect”mr-data dataset create --name "Total population by economy and year" --jsonmr-data recipe recipe.json --json
mr-data run --recipe RECIPE_ID --digest RECIPE_DIGEST --sample --max-rows 2000 --jsonmr-data peek RUN_ID --jsonmr-data query RUN_ID "select count(*) as rows, count(distinct country_id) as economies from run_table" --jsonmr-data checks RUN_ID --json
mr-data run --recipe RECIPE_ID --digest RECIPE_DIGEST --full --jsonWhat the run reports
Section titled “What the run reports”mr-data receipt RUN_ID --json carries four source entries, one per page, each with its own byte
count, digest and truncated flag. A row ceiling cut any page whose truncated is true. The union
above it is a partial series, and every later count understates it.
mr-data peek RUN_ID --json reports the logical type the run wrote, read from the run’s own column_profile. A type of null means the run wrote no profile for that column, not that the
column is untyped. Check the run reached persist before reporting a schema from it.
Variant: a keyed API with frozen pagination
Section titled “Variant: a keyed API with frozen pagination”authenticated.https.api_key@2.0.0 freezes a complete API read in the recipe. It pins the request, the one place the run presents the credential, the response Reader, and a deterministic pagination grammar.
It walks the pages itself, so one source covers all of them.
{ "dataset": { "name": "Weather station measurements from a paginated API" }, "question": { "text": "What did each station report, across every page the API serves?" }, "table": { "name": "keyed_observations", "description": "One row per station and observation time.", "grain": [ "station", "observed_at" ], "columns": [ { "name": "station", "type": "string", "nullable": false, "description": "The reporting station." }, { "name": "observed_at", "type": "timestamp", "nullable": false, "description": "When the reading was taken." }, { "name": "temperature_c", "type": "decimal", "nullable": true, "description": "Air temperature." } ] }, "sources": [ { "name": "observations", "description": "Every observation the provider's paginated endpoint serves.", "source_class": "user_api", "data_classification": "public", "locator": { "kind": "https_url", "display_locator": "https://api.example.org/observations" }, "rights_claim": { "claimed_basis": "contractual_license_asserted", "claim_evidence_digest": "sha256:3333333333333333333333333333333333333333333333333333333333333333", "claim_note": "Read under the provider's API terms for the enrolled key." }, "connector": { "adapter_id": "authenticated.https.api_key@2.0.0", "credential_mode": "opaque_reference", "origin": "https://api.example.org", "parameters": [ { "name": "station", "value": "KNYC" } ], "generic_api": { "request": { "method": "GET", "path_template": "/observations", "static_query": [ { "name": "station", "value": "{station}" } ], "static_headers": [], "parameters": [ { "name": "station", "type": "string", "required": true } ] }, "auth": { "kind": "bearer" }, "response": { "format": "json", "records_pointer": "/results", "reader": { "family_id": "json.tabular", "family_version": "1.1.0", "decode_options": { "columns": [ { "name": "observed_at", "pointer": "/observed_at", "required": true }, { "name": "station", "pointer": "/station", "required": true }, { "name": "temperature_c", "pointer": "/temperature_c", "required": false } ], "document_format": "json", "expand": [], "records_pointer": "/results" }, "options_digest": "56352027265977c757e73796c9e53f1a9062d1fa7753e9560eeebd7c34556cbf" } }, "pagination": { "kind": "page", "max_pages": 50, "page_parameter": "page", "page_start": 1 } } }, "credential": { "secret_name": "example_api_key" }, "limits": { "max_source_bytes": 16777216, "max_rows": 1000000, "max_requests": 50 } } ], "transform": { "engine": "duckdb_sql", "steps": [ { "step_id": "keyed_observations", "sql": "select station, cast(observed_at as timestamptz) as observed_at, try_cast(temperature_c as decimal(6, 2)) as temperature_c from observations", "description": "Every page is decoded by the pinned Reader into the same ordered columns, so the transform reads one relation." } ] }, "checks": [ { "check_id": "grain_unique", "kind": "key_uniqueness", "enforcement": "required", "columns": [ "station", "observed_at" ] } ], "units": [ { "column": "temperature_c", "unit": "Cel" } ], "timezone": "UTC"}credential_mode is opaque_reference, with a sibling credential. The value never appears in
the recipe, the receipt, the event stream or a log. Enrol it once with
mr-data keys set example_api_key --json. The worker redeems a one-time handle at the moment it
fetches.
generic_api.auth is the sole credential presentation authority. V2 does not accept a
credential_scheme parameter. That parameter belongs to authenticated.https.api_key@1.0.0, the
closed single-request CSV GET adapter.
Every declared request parameter is frozen once in connector.parameters. The parameters array
inside generic_api.request declares a name and a type. The connector’s own parameters array
supplies the value as text. The engine revalidates that value against the declared type at
registration, and again before it redeems the credential.
response.reader.options_digest is the bare lowercase SHA-256 of the canonical decode options,
with no sha256: prefix, computed over the fully expanded object. Derive it rather than typing it.
Pagination is one of five closed shapes: none, page, offset, cursor, link.
limits.max_requests and the worker’s own 100-request ceiling narrow max_pages further. The
page kind stops when a page has no records.
A pointer that still selects an object or an array refuses the decode. json.tabular projects
scalars. /geometry is refused. /geometry/coordinates/0 is a number and works.
Duplicate object keys, malformed pointers, non-finite numbers and non-UTF-8 input all refuse. The Reader does not infer a table, guess paths, evaluate expressions, or serialize an object into a cell.
limits.max_requests counts every HTTP request, redirects and retries included. A page that
redirects once costs two.
A run that spends its budget on page four leaves the series short, and the row count still looks
plausible. key_uniqueness plus a row floor catch that. Read coverage rather than count(*).
Changing any pointer, expansion, column or required flag changes the recipe digest. The change is a revision. It keeps the same dataset and table, and it costs nothing.