Stat-Xplore : Table Endpoint

The /table endpoint allows you to submit table queries and receive the results.

Overview

Endpoint	https://stat-xplore.dwp.gov.uk/webapi/rest/v1/table
HTTP Method	POST

Request Headers

Accept-Language	The language that labels will be returned in (setting this is equivalent to changing the dataset and user interface language in Stat-Xplore).	Optional. If not set, the server default language will be used.
APIKey	The API Key to use to authenticate this request. You can obtain your API key from the Account page in Stat-Xplore.	Required in all requests.

Content-Type	Must be set to `application/json`.	Required in all requests.

Request Body

The body of the request contains your query. It must be in JSON format and contain the following objects:

Object	Description	Example
database	The ID of the dataset you want to query.	"database" : "str:database:PIP_Monthly"
measures	An array of IDs of all the variables you want to return.	"measures" : [ "str:count:PIP_Monthly:V_F_PIP_MONTHLY" ]
recodes	(Optional). An object containing recodes for any of the fields specified in the request. Use this if you: Only want to see results for specific field values. For example, you are querying the National - Regional - LA - OAs field and you only want results for a handful of geographies rather than every geography available. Want to return results for combinations of field values. For example, you want to see results for the geographical regions North East and North West combined into a single cell (this is equivalent to creating a custom group in Stat-Xplore). Want to request totals for any of the fields in the query. For each field you want to recode, you need to include the field's ID as a key and within that object set a `map` property to an array of all the field value IDs you want to return for this field. Each field value itself must be specified as an array: to combine field values into a custom group, simply specify multiple value IDs within that array. The example on the right shows a recode for the Region field. In this example the API will return only two field values for the Region field: one will be for the London region alone (code: E12000007), and the second will be the combined total of North East (code: E12000001) and North West (code: E12000002) combined into a single value. To request a total for a field, include the field's ID as a key and within that object set the `total` property to `true`. The example on the right requests a total for recoded Region field but not the Gender field. You can omit the `recodes` object entirely from your request, in which case all field values will be returned for all fields. If you choose to include the `recodes` object then you only need to specify the fields you actually want to recode or request a total for. Any fields that are included in your request in the `dimensions` object, but not specified in the `recodes` object will simply have all available field values returned. If you set `total` to `true` for a field, but do not include a `map` for that field, then you will get the totals for all values in that field.	"recodes" : { "str:field:PIP_Monthly:V_F_PIP_MONTHLY:LA_code" : { "map" : [ [ "str:value:PIP_Monthly:V_F_PIP_MONTHLY:LA_code:V_C_GEOG05_REGION_TO_COUNTRY:E12000007" ], [ "str:value:PIP_Monthly:V_F_PIP_MONTHLY:LA_code:V_C_GEOG05_REGION_TO_COUNTRY:E12000001", "str:value:PIP_Monthly:V_F_PIP_MONTHLY:LA_code:V_C_GEOG05_REGION_TO_COUNTRY:E12000002" ] ], "total" : true }, "str:field:PIP_Monthly:V_F_PIP_MONTHLY:SEX" : { "total" : false } }
dimensions	An array of IDs of the fields in each dimension (e.g., row, column, wafer) of your table. Each dimension must be specified as an array: to combine fields within an axis, simply specify multiple field IDs within that array.	"dimensions" : [ [ "str:field:PIP_Monthly:V_F_PIP_MONTHLY:LA_code" ], [ "str:field:PIP_Monthly:V_F_PIP_MONTHLY:SEX" ] ]

As shown here, all IDs specified in the request need to be in the ID format that is returned by the /schema endpoint.

The following is an example of a request to the API to obtain a table of Region by Gender from the Personal Independence Payments dataset:

{
    "database" : "str:database:PIP_Monthly",
    "measures" : [
        "str:count:PIP_Monthly:V_F_PIP_MONTHLY"
    ],
    "recodes" : {
        "str:field:PIP_Monthly:V_F_PIP_MONTHLY:LA_code" : {
            "map" : [
                [
                    "str:value:PIP_Monthly:V_F_PIP_MONTHLY:LA_code:V_C_GEOG05_REGION_to_COUNTRY:E12000001"
                ],
                [
                    "str:value:PIP_Monthly:V_F_PIP_MONTHLY:LA_code:V_C_GEOG05_REGION_to_COUNTRY:E12000002"
                ]
            ],
            "total" : true
        },
        "str:field:PIP_Monthly:V_F_PIP_MONTHLY:SEX" : {
            "total" : true
        }
    },
    "dimensions" :
    [
        [
            "str:field:PIP_Monthly:V_F_PIP_MONTHLY:LA_code"
        ],
        [
            "str:field:PIP_Monthly:V_F_PIP_MONTHLY:SEX"
        ]
    ]
}

In this example:

The Region field has been recoded so that only values for North East and North West will be returned.
No recodes are specified for the Gender field, so all available field values will be returned.
Totals have been requested for both fields.

Response Body

The response contains the results of your table query. It contains the following objects:

Object Returns

query

The query that was submitted. This object contains the database, measures, recodes, and dimensions objects from your query.

The recodes object is returned in the response even if you did not include it in the query, or did not specify recodes for all fields. The response will contain the full list of returned field values for any fields that were not recoded in the original request. This allows you to use the recodes object in the response to determine what the returned cell values represent.

For example, the above sample query requests a table containing Region and Gender, but only specifies a recode for the Region field. The recodes object in the response would therefore include all field values for Gender, as well as the two specified field values for Region.

database

Details of the dataset that was queried:

id	The ID of this dataset
annotationKeys	An array of keys to annotations for this dataset. If any annotations are available, their descriptions will be returned in the `annotationMap` object.

fields

Details of the fields that were queried:

uri

The Open Data ID of this field. This matches the ID format this is returned by the /schema endpoint.

label

The display name for this field. This is the label that is displayed in Stat-Xplore.

items

An array containing all the field values returned for this field:

type	The field type (`RecodeItem`).
labels	The display name(s) for this field item. In most cases there will be a single label. However, if you have used the `recodes` in the query to combine multiple field items into a single value, then this will contain the labels of each constituent field value.
annotationKeys	An array of keys to annotations for this field item (or these field items, if you have combined multiple field items into a single value). If any annotations are available, their descriptions will be returned in the `annotationMap` object.
uris	The Open Data ID of this field item (or these field items, if you have combined multiple field items into a single value). This matches the ID format this is returned by the `/schema` endpoint.

annotationKeys

An array of keys to annotations for this field. If any annotations are available, their descriptions will be returned in the annotationMap object.

measures

An array containing all the measures (summation options) returned for this query. For each measure, the API returns:

uri	The Open Data ID of this measure. This matches the ID format this is returned by the `/schema` endpoint.
label	The display name for this measure. This is the label that is displayed in Stat-Xplore.

cubes

An array containing the results of the query. There will be one item in this array for each measure you requested.

Each item specifies the measure, and then provides the values for each cell in the cube for that measure.

annotationMap

Any annotations that apply to this query. If there are annotations for the dataset or its fields, then the annotation keys and descriptions will be returned in this object.