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 MethodPOST

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:

ObjectDescriptionExample
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:

ObjectReturns
query

The query that was submitted. This object contains the databasemeasuresrecodes, 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.