Create a Query


🕐 9 min read

Anatomy of a query

The JavaScript Object Notation (JSON) request body for the Query data source method has three components.

The following example shows the basic structure of a query.

{
"datasource": { 
    "datasourceLuid": "",
 },
"options": {
    "debug": true
},
  "query": { 
    "fields": [
       // fields here
    ]
  }
}

Connect to your data source

The datasource object takes a datasourceLuid.

{
"datasource": {
    "datasourceLuid": "",
 },
  "query": { 
    "fields": [
       // fields here
    ]
  }

Data sources that require credentials

If you have a data source that requires credentials, enter them in an additional connection object.

{
"datasource": {
    "datasourceLuid": "",
    "connections": [
        {
          "connectionLuid": "31752d0a-ec9d-11ee-9ad5-0a61dcca52fb",
          "connectionUsername": "test",
          "connectionPassword": "password"
        }
    ]
 },
  "query": { // See below for more details
    "fields": [
       // Fields here
    ]
  }
}
Note: While VDS does not support data sources that have more than one connection that requires a username and password, VDS does support data sources that have a single connection that requires authentication. Use connectionUsername and connectionPassword to provide authentication for your connection. The connectionLuid property isn't required for now.

For data sources that require non-embedded credentials, add the following fields to your connection object in the body of the request.

The query object

The query object contains two basic components:

Fields

You can list the fields that you want from your published data source and see the returned data. Fields can come in multiple forms: dimensions, measures, or custom calculations. Fields can also be sorted, aliased, and have their decimal places formatted (if they’re a number).

Note: The fieldCaption must be the fieldCaption returned from Request data source metadata method.

Dimensions

Pass in a fieldCaption and get the data for that field as a dimension.

The following example shows sorting, which is optional.

"fields": [
        {
            "fieldCaption": "Category",
            "sortPriority": 1
        },
        {
            "fieldCaption": "Sub-Category",
            "sortPriority": 2,
            “sortDirection”: “DESC”
        },
]

Fields with aggregations or measures

You can add an aggregation, or function, to a field to treat the field as a measure.

"fields": [
        {
            "fieldCaption": "Sales",
            "function": "SUM",
            "maxDecimalPlaces": 2
        }
]

VDS supports the following functions for aggregations:

Custom calculations

To specify a new, custom calculation as a field, give it a fieldCaption and a string in Tableau calculation syntax. To query an existing calculation, use the preceding methods to query an existing field.

"fields": [
        {
            "fieldCaption": "Profit Margin",
            "calculation": "SUM([Profit])/SUM([Sales])"
        }
]

The following is a complete list of things you can add to your field object.

Additional rules about fields

Filters

You can also filter the fields you receive back from the datasource. To specify a filter, you can create a field (like above) to operate on. This field does not have to be in the list of original fields. There are different kinds of filters, each requiring a filterType and a field to filter on.

Set filters

You can include or exclude certain values from your dataset when showing results. The following filter is used for dimensions. You must set the Boolean exclude and provide a list of values to either exclude (when exclude=true) or include (when exclude=false).

"filters": [
    {
      "field": {
         "fieldCaption": "Ship Mode"
      },
      "filterType": "SET",
      "values": [ "First Class"],
      "exclude": false
    }
]

Note that the values array can’t be empty.

Quantitative filters

A quantitative filter operates on a field with an aggregation. It can specify a minimum, maximum, or range of values. It can also be used to handle both null and non-null values.

Quantitative filters must have quantitativeFilterType, which can be one of the following (with some rules):

If you have type ONLY_NULL or ONLY_NON_NULL, you can’t have minimums and maximums specified.

If you have type MIN, MAX, or RANGE, you can also set the additional property includeNulls to true (it’s false by default) if you’d like to include nulls.

There are two types of quantitative filters, one for dates and one for numerical values.

Quantitative numerical filters

For measures, set the filterType to QUANTITATIVE_NUMERICAL.

// measure range example
"filters": [
  {
    "column": {
        "fieldCaption": "Sales",
        "function": "SUM"
    },
    "filterType": "QUANTITATIVE_NUMERICAL",
    "quantitativeFilterType": "MIN",
    "min": 10000
  }
]
Quantitative date filters

For dates, set the filterType to QUANTITATIVE_DATE.

The only difference here is that, instead of specifying min and max, you can instead specify minDate and maxDate.

// date range example
"filters": [
  {
     "field": {
         "fieldCaption": "Order Date"
      },
    "filterType": "QUANTITATIVE_DATE",
    "quantitativeFilterType": "MAX",
    "maxDate": "2020-04-01"
  }
]

Relative date filter

A relative date filter is a way to specify a range of dates from a given anchor. If you set NO anchor, today’s date will be used by default.

You must also set the variables periodType and dateRangeType.

periodType can be one of the following values:

dateRangeType can be one of the following values;

See the following screenshot of the Tableau user interface to conceptualize what these dateRangeTypes mean.

Filter [Order Date] screen

Some examples
// January 1st, 2020 - March 1st, 2020
"filters": [
  {
    "filterType": "DATE",
    "field": {
    	"fieldCaption": "Order Date"
     },
    "periodType": "MONTHS",
    "dateRangeType": "NEXTN,
    "rangeN": 3,
    "anchorDate": "2020-01-01"
  }
]
// January 1st, 2020 - December 31st, 2020
"filters": [
  {
    "filterType": "DATE",
    "field": {
    	"fieldCaption": "Order Date"
     },
    "periodType": "YEARS",
    "dateRangeType": "CURRENT,
    "anchorDate": "2020-01-01"
  }
]
// All dates up to today's date (no anchor defaults to today)
"filters": [
  {
    "filterType": "DATE",
     "field": {
    	"fieldCaption": "Order Date"
     },
    "periodType": "YEARS",
    "dateRangeType": "TODATE,
  }
]

Requirement: You must have a periodType and a dateRangeType. If you specify a rangeN, then your dateRangeType must be lastN or nextN.

Top N filter

You can see top or bottom values of a given field by some aggregation.

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

See the following example, “Top 10 States with the highest Profit”.

"filters": [
  {
    "field": {
    	"fieldCaption": "State/Province"
     },
    "filterType": "TOP",
    "howMany": 10,
    "fieldToMeasure": {
      "fieldCaption": "Profit",
      "function": "SUM"
    },
    "direction": "TOP"
  }
]

Requirement: You must have howMany and fieldToMeasure.

Match filter

You can also provide substrings to conditionally match various filter types.

"filters": [
    {
      "field": {
    	"fieldCaption": "State/Province"
      },
      "filterType": "MATCH",
      "startsWith": "A",
      "endsWith": "a",
      "contains": "o",
      "exclude": false
    }
]

Requirement: You must have at least one of startsWith, endsWith, or contains.

Context filter

You can add a context filter and create a dependent filter on a quantitative filter or a top N filter, ideally to improve performance. You can add “context” as a boolean field to any filter to make it a context filter. For more information about Tableau context filters, see Use Context Filters.

"filters": [
    {
        "field": {
    	    "fieldCaption": "SubCategory"
        },
        "filterType": "TOP",
        "howMany": 10,
        "fieldToMeasure": {
            "fieldCaption": "Sales",
            "function": "SUM"
        },
       "direction": "TOP"
    },
    {
        "field": {
    	    "fieldCaption": "Category"
        },
        "filterType": "SET",
        "values": [ "Furniture"],
        "exclude": false,
        "context": true
    }
]

Some additional rules about filters

Additional options

In addition to the Datasource object and the Query object, VDS lets you use the following additional options that can adjust the behavior of your query.

{
"datasource": { // See above for more details
	// datasource info here
 },
"query": { // See above for more details
  "fields": [
       // Fields here
  ]
 },
"options": {
   "returnFormat": "OBJECTS",
   "debug": true,
   "disaggregate": false
}
}

Return format

VDS always returns the response body in JSON.

OBJECTS return vs ARRAYS return option

The OBJECTS option returns field names as human-readable JSON objects. The ARRAYS option returns lists of data values.

// OBJECTS return
{
    "data": [
        {
            "Ship Mode": "Second Class",
            "SUM(Sales)": 466671.11140000017
        },
        {
            "Ship Mode": "Standard Class",
            "SUM(Sales)": 1378840.5509999855
        },
        {
            "Ship Mode": "Same Day",
            "SUM(Sales)": 129271.955
        },
        {
            "Ship Mode": "First Class",
            "SUM(Sales)": 351750.73690000066
        }
    ]
}

// ARRAYS return
{
    "data": [
        [
            "Second Class",
            466671.11140000017
        ],
        [
            "Standard Class",
            1378840.5509999855
        ],
        [
            "Same Day",
            129271.955
        ],
        [
            "First Class",
            351750.73690000066
        ]
    ]
}

Date formats

For more information, see the VizQL Data Service API documentation.