Table 🔗

Each dataset has a table/ endpoint providing access to both the variable definitions and column data of that dataset.

Fetching values 🔗

GET 🔗

Dataset editors can GET this resource to obtain a crunch:table JSON object. The crunch:table contains a "metadata": object describing the dataset variables and an optional "data": object containing column data for those variables.

The scope of variables included in the response and the scope of row-data provided can be controlled by query-string parameters.

The following query-string parameters are accepted on GET:

Name Default Description
include_personal false Include personal variables visible to this user in response.
filter null Filter limiting scope of rows in response
exclude_exclusion_filter false When true, include rows otherwise removed by dataset exclusion filter.
offset 0 Do not include rows prior to this position.
limit 0 (no data) Include at most this many rows in the response.
key null (variable-id) Specifies the value used to identify each variable and subvariable in the response (the JSON object field name used in the “metadata”: object). One of null (omitted) or “url”. When omitted, variables are keyed by their variable-id. When ?key=url is specified, each variable and subvariable is keyed by its Crunch URL.

The JSON crunch:table response payload includes a metadata object containing the definition of each variable, keyed as specified by the key query-string attribute. Each variable definition contains the following items:

Name Type Description
name string Human-friendly string identifier
alias string More machine-friendly, traditional name for a variable
description string Optional longer string
notes string Optional annotations for a variable
type string The string type name, one of “numeric”, “text”, “categorical”, “datetime”, “categorical_array”, or “multiple_response”
categories array If “type” is “categorical”, “multiple_response”, or “categorical_array”, an array of category definitions (see below). Other types have an empty array
resolution string Present in datetime variables; current resolution of data

Category objects have the following members:

Name Type Description
id integer identifier for the category, corresponding to values in the column of data
name string A unique label identifying the category
numeric_value numeric A quantity assigned to this category for numeric aggregation. May be null.
missing boolean If true, the given category is marked as “missing”, and is omitted from most calculations. For logical operations, this makes the category “none/null/NA”.
selected boolean If true, the given category is marked as “selected”. For logical operations, this makes the category “true”. Multiple response variables must have at least one category marked as selected and may have more than one

If the response contains data values (requested by specifying a ?limit= value other than 0), that data appears in a "data": object in the crunch:table response, as shown in the JSON example below. The "data": key is omitted when no data is returned. The "data": object is a collection of column data arrays keyed by variable-id or URL, depending on the ?key= setting described above.

Dataset viewers can only access the metadata portion of the response. This means they cannot make use of the limit and offset parameters to query data unless the dataset setting viewers_can_export is True, producing a 403 Forbidden response otherwise.

{
  "self": "https://alpha.crunch.io/api/datasets/:id/table/",
  "element": "crunch:table",
  "data": {
    "000007": [ 1, 1, 2 ],
    "000004": [ 1, 1, 1 ],
    "000005": [ 1, 0, 1 ],
    "000003": [ "red", "green", "MORE JUNK" ],
    "000000": [ 1, 2, 9 ],
    "000001": [ "2000-01-01T00:00:00", "2000-01-02T00:00:00", { "?": -1 } ],
    "000008": [ 1, 2, 3 ],
    "000009": [ 2, 3, 4 ],
    "00000c": [ [ 1, 1, 2 ], [ 1, 2, 3 ], [ 2, 3, 4 ] ]
  },
  "description": "A Crunch Table of data for this dataset.",
  "metadata": {
    "000004": {
      "alias": "bool1",
      "type": "categorical",
      "name": "mymrset | Response #1",
      "categories": [
        { "numeric_value": 1, "selected": true, "id": 1, "name": "1", "missing": false },
        { "numeric_value": 0, "id": 0, "name": "0", "missing": false },
        { "numeric_value": null, "id": -1, "name": "No Data", "missing": true }
      ],
      "description": "bool1"
    },
    "000005": {
      "alias": "bool2",
      "type": "categorical",
      "name": "mymrset | Response #2",
      "categories": [
        { "numeric_value": 1, "selected": true, "id": 1, "name": "1", "missing": false },
        { "numeric_value": 0, "id": 0, "name": "0", "missing": false },
        { "numeric_value": null, "id": -1, "name": "No Data", "missing": true }
      ],
      "description": "bool2"
    },
    "000003": {
      "alias": "str",
      "type": "text",
      "name": "str",
      "missing_reasons": { "No Data": -1 },
      "description": "40 character string"
    },
    "000000": {
      "alias": "x",
      "type": "categorical",
      "name": "x",
      "categories": [
        { "numeric_value": 1, "id": 1, "name": "red", "missing": false },
        { "numeric_value": 2, "id": 2, "name": "green", "missing": false },
        { "numeric_value": 3, "id": 3, "name": "blue", "missing": false },
        { "numeric_value": 4, "id": 4, "name": "4", "missing": false },
        { "numeric_value": 8, "id": 8, "name": "8", "missing": true },
        { "numeric_value": 9, "id": 9, "name": "9", "missing": false },
        { "numeric_value": null, "id": -1, "name": "No Data", "missing": true }
      ],
      "description": "Numeric variable with value labels"
    },
    "000001": {
      "name": "y",
      "type": "datetime",
      "missing_reasons": { "No Data": -1 },
      "alias": "y",
      "resolution": "s",
      "description": "Date variable"
    },
    "00000c": {
      "alias": "categorical_array",
      "type": "categorical_array",
      "name": "categorical_array",
      "subvariables": ["000007", "000008", "000009"],
      "subreferences": {
        "000009": {"alias": "ca_subvar_1", "name": "ca_subvar_1", "description": ""},
        "000007": {"alias": "ca_subvar_2", "name": "ca_subvar_2", "description": ""},
        "000008": {"alias": "ca_subvar_3", "name": "ca_subvar_3", "description": ""}
      },
      "categories": [
        { "numeric_value": null, "selected": false, "id": 1, "missing": false, "name": "a" },
        { "numeric_value": null, "selected": false, "id": 2, "missing": false, "name": "b" },
        { "numeric_value": null, "selected": false, "id": 3, "missing": false, "name": "c" },
        { "numeric_value": null, "selected": false, "id": 4, "missing": false, "name": "d" },
        { "numeric_value": null, "selected": false, "id": -1, "missing": true, "name": "No Data" }
      ],
      "description": ""
    }
  }
}
GET /datasets/:id/table/ HTTP/1.1

Filtering 🔗

This endpoint accepts values on its ?filter=... query-string parameter as described under Filtering endpoints.