Create a VizQL Data Service Query


Note: This is the developer preview release of the VizQL Data Service. This release is intended to offer developers early access to unreleased features.

🕐 8 min read

In the VizQL Data Service Postman collection, you can either duplicate the example queries and change them, or modify the example queries directly. Then, you can type in the request body to change your query.

Query Components

A VizQL Data Service query is a JSON object. It contains three fundamental components.

Here are several examples based on the Superstore data source. Find more examples in the Postman Collection.

Basic queries to get started

The following is a query to return the sum of sales by segment. See the query schema under Function for the list of available functions.

{
  "columns": [
    {
      "columnName": "Segment"
    },
    {
      "columnName": "Sales",
      "function": "SUM"
    }
  ]
}

This next example is a query to return the sum of sales by segment, rounded to the nearest dollar.

{
  "columns": [
    {
      "columnName": "Segment"
    },
    {
      "columnName": "Sales",
      "function": "SUM",
      "maxDecimalPlaces": 0
    }
  ]
}

The following example is a query to show the profit margin by category subcategory sorted in descending order of profit margin. Shows the most profitable category/sub-category of items for sale.

{
    "columns": [
      {
        "columnName": "Category"
      },
      {
        "columnName": "Sub-Category"
      },
      {
        "columnName": "Profit Margin",
        "calculation": "SUM([Profit])/SUM([Sales])",
        "sortPriority": 1,
        "sortDirection": "DESC"
      }
    ]
  }

This next example is a query to show the AOV by year and quarter. Sorting is accomplished by providing the sortPriority field to a column. Sorting on multiple columns is supported. The column with the highest sort priority (meaning the lowest number) is sorted first, followed by subsequent columns. The default sort direction is in ascending order.

{
    "columns": [
        {
            "columnName": "Order Date",
            "function": "DATE_YEAR",
            "sortPriority": 1
        },
        {
            "columnName": "Order Date",
            "function": "DATE_QTR",
            "sortPriority": 2
        },
        {
            "columnName": "AOV",
            "calculation": "SUM([Profit])/COUNTD([Order ID])"
        }
    ]
}

Next up is a query to show sales by sub-category from highest to lowest (descending order). This example includes a filter to remove individual sales below 200 K.

{
  "columns": [
    {
      "columnName": "Sub-Category"
    },
    {
      "columnName": "Sales",
      "function": "SUM",
      "sortPriority": 1,
      "sortDirection": "DESC"
    }
  ],
  "filters": [
      {
          "columnName": "Sales",
          "filterType": "QUANTITATIVE",
          "quantitativeFilterType": "MIN",
          "min": 200000
      }
  ]
}

Full list of query functionality

Construct a column

A column can be constructed in one of three ways:

Columns

The following is a complete list of things you can add to your column object. (See the Column Object in the schema.)

Filters

A filter must have the following fields:

There are more requirements, depending on the filter type, as detailed in the following sections.

See Filter Object in the schema. Specifying the Column to filter on.

A Filter always references a single column of data to filter on. See How to Construct a Column above for details on how to specify your column.

Filter types

There are four kinds of filters you can add to your query. For each filter, you must set filterType, which could be QUANTITATIVE, SET, DATE, or TOP.

Quantitative filter

This filter is used for measures or dates. You can use it to set a minimum (MIN) value, a maximum (MAX) value, a range of values (RANGE), or a “special” type (SPECIAL). Some rules for this type of filter based on quantitativeFilterType:

     {
       "column": {
           "columnName": "Sales",
           "function": "SUM"
       },
       "filterType": "QUANTITATIVE",
       "quantitativeFilterType": "RANGE",
       "min": 266839,
       "max": 1149562
   }

For dates, use the minDate and maxDate values to specify a MIN, MAX, or RANGE quantitative filter. These values are Date objects, for which you must specify a “day”, “month”, and “year”. For example, the following filter is of type MIN and uses the date object type:

    {
    "columnName": "Order Date",
    "filterType": "QUANTITATIVE",
    "quantitativeFilterType": "MIN",
    "minDate": {
        "days": 1,
        "months": 1,
        "years": 2022
        }
  }

Set filter

This filter is used for dimensions or dates. You can use it to either include or exclude certain values. You must set the boolean exclude and provide a list of values to either exclude (when exclude=true) or include (when exclude=false).

    {
        "columnName": "Ship Mode",
        "filterType": "SET",
        "exclude": true,
        "values": ["First Class"]
      }

Relative date filter

This filter is used for setting a range of dates relative to an anchor. You can set the anchor by passing in an object that has the numeric day (DD), the numeric month (MM), and the numeric year (YYYY). If you set NO anchor, today’s date is used by default.

You must also set the variables Units, pastCount and futureCount. These are integers that specify the range. Units can be one of the following values: “DAYS”, “WEEKS”, “MONTHS”, “QUARTERS”, “YEARS”. pastCount is an integer, negative or positive, that specifies how many “Units” you want to start AWAY from the anchor. Use 0 to start from the anchor.

For example:

futureCount works the same way. It’s an integer, negative or positive, that specifies how many units of Units you want to end from AWAY from the anchor. Use 0 to end at the anchor.

For example:

Generally, pastCount is probably negative and futureCount is probably positive, but you don’t need to follow that convention. However, pastCount MUST be less than futureCount, or you’ll get 0 results.

{
  "filterType": "DATE",
  "columnName": "Order Date",
  "Units": "DAYS",
  "pastCount": 0, // Start from 1/1/2021
  "futureCount": 5, // End at 1/5/2021
  "anchor": {
              "day": 1,
              "month": 1,
              "year": 2021
          }
  }

Top N Filter

A top N filter, or filterType: "TOP" allows you to find the top or bottom N results of a given category. You must specify the following inputs:

The following is an example of a top N Filter to show the top 10 states by sales:

 {
    "columnName": "State/Province",
    "filterType": "TOP",
    "howMany": 10,
    "fieldToMeasure": {
        "columnName": "Profit",
        "function": "SUM"
      },
    "direction": "TOP"
  }