Dataset Data API

The dataset_data API endpoint allows to get and filter out the Dataset data.

Prerequisites:

Table of contents:

  1. Access Admin > System > API Toolkit
  2. Configure Dataset Data
    1. Get Dataset Data
    2. Filtering Dataset Data
      1. Limit, Offset, Amount
      2. Contains
      3. Equals, Is Greater Than, Is Less Than
      4. Operator AND
      5. Operator OR

See Dataset API for instructions on configuring Dataset information via the dataset API endpoint.

1. Access Admin > System > API Toolkit

2. Configure Dataset Data

2.1. Get Dataset Data

  1. Item: dataset_data
  2. Methods: GET
  3. dataset: Enter ID of the Dataset
  4. view: Optionally, enter ID of the Dataset View
  5. Optionally, enter measurement_time for which you want to retrieve Dataset data
  6. Enter an API Token
  7. [Run request]
  8. The returned object contains Dataset's info
  9. See Example Response  and Fields Description for details
Example Response

This example demonstrates an object which represents a single element.

{
	"data": 
	[
		{"Name":"User8","Country":"Algeria","Customer ID":"3","Items Purchased":"4","Date":null},
		{"Name":"User7","Country":"Algeria","Customer ID":"3","Items Purchased":"10","Date":null},
		{"Name":"User10","Country":"Italy","Customer ID":"2","Items Purchased":"12","Date":null}, 
		{"Name":"User6","Country":"Algeria","Customer ID":"4","Items Purchased":"13","Date":null}
	]
	"metadata": 
	[
		{"name":"Name","type":"text"},
		{"name":"Country","type":"text"},
		{"name":"Customer ID","type":"numeric"}, 
		{"name":"Items Purchased","type":"numeric"},
		{"name":"Date","type":"datetime"}
	],
	"amount":6
}
Click to copy

Fields Description

Field Name Value Type Description
data array An array of objects with returned Dataset data.
metadata array The metadata containing column names and types.
amount integer The total number of Dataset rows. Is displayed only if the amount parameter is set to “Y”

2.2. Filtering Dataset Data

This functionality is not available in the API Toolkit and can be performed via direct API calls, such as described in this section.

2.2.1. Limit, Offset, Amount

This API call demonstrates how to apply filtering and sorting by using additional query parameters:

$.ajax({
  	"url":  "/api/dataset_data?dataset=<Dataset ID>&limit=<Number of returned rows>&offset=<Number of rows to skip>&amount=<Y/N>", 
  	"type":  "POST", 
  	data:  
    {
      "sort":  
             [
               {"field":"<Field 1 Name>","dir":"<ASC/DESC>"},
               {"field":"<Field 2 Name>","dir":"<ASC/DESC>"}
             ]
    },
  	"headers": {"Accept":"application/json"}
}).done(response=>console.log(response))

Assign dataset the value of Dataset ID and enter value of the following query parameters to filter out the Dataset data:

  • limit - the number of returned rows,
  • offset - the number of rows to be skipped before returning any rows,
  • amount -  whether to display the total amount of rows in the Dataset.

To sort out the returned data, add an object to the sort array providing two properties' values:

  • field: The name of the column by which the Dataset data is filtered.
  • dir: The direction in which the Dataset data is sorted can take one of the two values:
    • ASC: Ascending
    • DESC: Descending

Example request:

$.ajax({
  	"url":  "/api/dataset_data?dataset=568&limit=4&offset=1&amount=Y", 
  	"type":  "POST", 
  	data:  
    {
      "sort":  
             [
               {"field":"items_purchased","dir":"DESC"},
               {"field":  "country","dir":"ASC"}
             ]
    },
  	"headers": {"Accept":"application/json"}
}).done(response=>console.log(response))
Click to copy

2.2.2. Contains

This API call demonstrates how to filter out the Dataset data based on text entry and return only columns that contain entered words or letters.

$.ajax({
    "url": '/api/dataset_data?dataset=<Dataset ID>',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        "filters": [
            {
                "column_name": "<Column Name>",
                "condition": "contains",
                "data": "<Data to filter Columns>"
            }
        ]
    })
});

Assign dataset the value of Dataset ID and enter values of the following filter parameters:

  • column_name: The filtered column name.
  • data: A text entry by which the column data is filtered.
    • The text entry can contain one or more symbols or words.

Example request:

$.ajax({
    "url": '/api/dataset_data?dataset=568',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        "filters": [
            {
                "column_name": "country",
                "condition": "contains",
                "data": "Italy"
            }
        ]
    })
});

2.2.3. Equals, Is Greater Than, Is Less Than

This API call allows to filter the numeric data of the Dataset:

$.ajax({
    "url": '/api/dataset_data?dataset=<Dataset ID>',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        filters: [
            {
                column_name: "<Column Name>",
                condition: "<Operator>",
                data: "<Numeric Value>"
            }
        ]
    })
});

Assign dataset the value of Dataset ID and enter the values of the following filter parameters:

  • column_name: The filtered column name.
  • condition: Select one of the three conditions:
    • equals: Returns rows where the column value is equal to the user input
    • is greater than: Returns rows where the column value is greater than the value of user input
    • is less than: Returns rows where the column value is less than the value of user input
  • data: A numeric value

Request example:

$.ajax({
    "url": '/api/dataset_data?dataset=568',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        filters: [
            {
                column_name: "items_purchased",
                condition: "is less than",
                data: "12"
            }
        ]
    })
});

2.2.4. Operator AND

This API call allows to return Dataset data which corresponds to multiple filter conditions:

$.ajax({
    "url": '/api/dataset_data?dataset=<Dataset ID>',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        filters: [
            {
                group: {
                    operator: "AND",
                    rules: [
                        {
                            column_name: "<Column Name>",
                            condition: "<Condition>",
                            data: "<Data to filter columns>"
                        },
                        {
                            column_name: "<Column Name>",
                            condition: "<Condition>",
                            data: "<Data to filter columns>"
                        }
                    ]
                }
            }
        ],
    })
});

Assign dataset the value of Dataset ID. Any number of rules can be applied, each rule can be either a rule with contains condition or equals, is greater than, is less than.

Example request:

$.ajax({
    "url": '/api/dataset_data?dataset=568',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        filters: [
            {
                group: {
                    operator: "AND",
                    rules: [
                        {
                            column_name: "items_purchased",
                            condition: "is greater than",
                            data: "10"
                        },
                        {
                            column_name: "country",
                            condition: "contains",
                            data: "Italy"
                        }
                    ]
                }
            }
        ],
    })
});

2.2.5. Operator OR

This API call allows to return Dataset data which corresponds to at least one of the provided filter conditions:

$.ajax({
    "url": '/api/dataset_data?dataset=<Dataset ID>',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        filters: [
            {
                group: {
                    operator: "OR",
                    rules: [
                        {
                            column_name: "<Column Name>",
                            condition: "<Condition>",
                            data: "<Data to filter columns>"
                        },
                        {
                            column_name: "<Column Name>",
                            condition: "<Condition>",
                            data: "<Data to filter columns>"
                        }
                    ]
                }
            }
        ],
    })
});

Assign dataset the value of Dataset ID. Any number of rules can be applied, each rule can be either a rule with contains condition or equals, is greater than, is less than.

Example request: 

$.ajax({
    "url": '/api/dataset_data?dataset=568',
    "headers": { "Content-type": "application/json", "Accept": "application/json" },
    "type": "POST",
    "data": JSON.stringify({
        filters: [
            {
                group: {
                    operator: "OR",
                    rules: [
                        {
                            column_name: "items_purchased",
                            condition: "is less than",
                            data: "15"
                        },
                        {
                            column_name: "country",
                            condition: "contains",
                            data: "Canada"
                        },
                    ]
                }
            }
        ],
    })
});