Use SuperWEB2 to Build API Queries

If you are building complex table queries for use in the API, then you may find it easier to build the tables you want in SuperWEB2 first. You can then either:

Download your table in JSON format as an Open Data API Query.
Save the table to the User Data Repository and then submit a query for the saved table.

Download Table in JSON Format

Simply build the table that you want and then select Open Data API Query from the Download Table drop-down menu:

SuperWEB2 will download a text file containing the JSON query you will need to request the same table from the Open Data API.

For example:

Create your table in SuperWEB2 and select the Open Data API Query (.json) download format:
Click Go.

SuperWEB2 downloads a text file containing the JSON for your query:

JS

{
    "database": "str:database:bank",
    "measures": ["str:count:bank:F_Customer"],
    "recodes": {
        "str:field:bank:F_Customer:Gender": {
            "map": [["str:value:bank:F_Customer:Gender:C_Gender:M"], ["str:value:bank:F_Customer:Gender:C_Gender:F"], ["str:value:bank:F_Customer:Gender:C_Gender:U"], ["str:value:bank:F_Customer:Gender:C_Gender:-1"]],
            "total": true
        },
        "str:field:bank:F_Customer:Marital_Status": {
            "map": [["str:value:bank:F_Customer:Marital_Status:C_Marital_Status:S"], ["str:value:bank:F_Customer:Marital_Status:C_Marital_Status:M"], ["str:value:bank:F_Customer:Marital_Status:C_Marital_Status:D"], ["str:value:bank:F_Customer:Marital_Status:C_Marital_Status:U"], ["str:value:bank:F_Customer:Marital_Status:C_Marital_Status:-1"]],
            "total": true
        }
    },
    "dimensions": [["str:field:bank:F_Customer:Gender"], ["str:field:bank:F_Customer:Marital_Status"]]
}

You can now POST this JSON query to the /table endpoint.

Make sure you include your API key in the header and set the Content-Type of the body to application/json.

Limitations of Open Data API Query Downloads

Please note that some features are not supported by the JSON download option.

The following items will not be included in the downloaded JSON query:

Derivations
Ranges
Quantiles
User Defined Fields
Any settings applied in SuperWEB2 (such as percentages or zero suppression)

Disable Open Data API Query Downloads

The Open Data API download option will be available to all users by default, but if you wish you can disable this by changing the download.enableOpenDataApiQuery property in the configuration.properties file.

(Optional): Remove Recodes from JSON Download

When you download the current table as an Open Data API query, the result will always include recodes for every field item in the table. The recodes specify the exact set of field items included in the table at the time the query was saved, even in cases where the table includes an entire field. This means that if the dataset changes in future (for example because new time periods are added to a date field) then the downloaded query will still only contain the specific field items that were in the table when it was downloaded.

For example, the above sample table includes all items for the Gender and Marital Status fields in the Retail Banking sample dataset. However, the downloaded query includes a recodes section that specifically lists each field item:

JS

"recodes": { 
    "str:field:bank:F_Customer:Gender": { 
         "map": [["str:value:bank:F_Customer:Gender:C_Gender:M"], ["str:value:bank:F_Customer:Gender:C_Gender:F"], ["str:value:bank:F_Customer:Gender:C_Gender:U"], ["str:value:bank:F_Customer:Gender:C_Gender:-1"]], 
         "total": true 
    },
    "str:field:bank:F_Customer:Marital_Status": { 
         "map": [["str:value:bank:F_Customer:Marital_Status:C_Marital_Status:S"], ["str:value:bank:F_Customer:Marital_Status:C_Marital_Status:M"], ["str:value:bank:F_Customer:Marital_Status:C_Marital_Status:D"], ["str:value:bank:F_Customer:Marital_Status:C_Marital_Status:U"], ["str:value:bank:F_Customer:Marital_Status:C_Marital_Status:-1"]], 
         "total": true 
    } 
}

If the dataset is updated and new field items are added to either of these fields, this query will not include those new field items (it will only ever include the listed field items).

If you want to create a query that includes all field items and that will automatically update should new field items be added, then you can simply remove the recodes from the query. You can either remove individual field recodes or the entire recodes section.

For example, the following query is functionally equivalent to the example downloaded query above, except that this version of the query will always contain the full set of field items, even if the dataset structure changes in future:

JS

{
    "database": "str:database:bank",
    "measures": ["str:count:bank:F_Customer"],
    "dimensions": [["str:field:bank:F_Customer:Gender"], ["str:field:bank:F_Customer:Marital_Status"]]
}

Query Saved Table

As an alternative to downloading the table query as JSON, you can also use the API to query for tables that have been saved to the User Data Repository (as long as the account connecting to the API has permission to access the saved table).

Create your table in SuperWEB2 and save it:

Send a GET request to the /schema endpoint to get the ID of the saved table. For example:

CODE

{
  "id": "str:table:1c9bbc74-ee60-4783-817d-899470c1c965",
  "label": "My Saved Table",
  "location": "http://localhost:8080/webapi/rest/v1/schema/str%3Atable%3A1c9bbc74-ee60-4783-817d-899470c1c965",
  "type": "TABLE"
}

Submit a GET request to the /table/saved endpoint containing your table ID value. For example, for the above table you would send a GET request to /table/saved/str:table:1c9bbc74-ee60-4783-817d-899470c1c965