# JSON API Source: https://mostlyright.md/docs/recipes/json-api/ Read a JSON API into a table with a records pointer, column pointers and one of two pagination routes. ## 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 | | | | --- | --- | | 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 ```json title="recipe.json" { "dataset": { "name": "Total population by economy and year" }, "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 ### 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](#variant-a-keyed-api-with-frozen-pagination), and it needs a credential. ### The Reader pin ```json {"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 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: ```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 ``` `TRY_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 `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 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 Use the real table for one full build. For a bounded test first, follow the [scratch-table workflow](/docs/build/run-and-inspect/#test-before-the-full-build). A successful sample on the real table can publish truncated data. Stop if the forecast refuses; size the recipe before running. ```sh mr-data dataset create --name "Total population by economy and year" --json mr-data recipe recipe.json --json mr-data recipe explain RECIPE_ID --json mr-data recipe forecast RECIPE_ID --digest RECIPE_DIGEST --json mr-data run --recipe RECIPE_ID --digest RECIPE_DIGEST --full --json mr-data watch RUN_ID --json mr-data peek RUN_ID --json mr-data query RUN_ID "select count(*) as rows, count(distinct country_id) as economies from run_table" --json mr-data checks RUN_ID --json ``` ## 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 `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. ```json title="recipe-keyed.json" { "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. ## Traps 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.