đ 9 min read
The JavaScript Object Notation (JSON) request body for the Query data source method has three components.
datasource
: Required. This is an object that tells you which data source to query and, optionally, takes credentials. To find your data source LUID, see Find the data source LUID.options
: Optional. Options are metadata that you can use to adjust the behavior of the query.query
: Required. This is how you specify which fields you want to retrieve information from. To get the names of the fields in your data source, see Get Data Source Information.The following example shows the basic structure of a query.
{
"datasource": {
"datasourceLuid": "",
},
"options": {
"debug": true
},
"query": {
"fields": [
// fields here
]
}
}
The datasource
object takes a datasourceLuid
.
{
"datasource": {
"datasourceLuid": "",
},
"query": {
"fields": [
// fields here
]
}
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
]
}
}
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.
connectionUsername
connectionPassword
The query
object contains two basic components:
fields
: Required. This contains an array of fields that define the desired output of the query. Itâs essentially the Tableau equivalent of âdragging a field to the viewâ.filters
: Optional. This contains an array of filters to apply to the query. They can include fields that arenât in the fields array.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).
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â
},
]
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:
SUM
AVG
MEDIAN
COUNT
COUNTD
MIN
MAX
STDEV
VAR
COLLECT
YEAR
QUARTER
MONTH
WEEK
DAY
TRUNC_YEAR
TRUNC_QUARTER
TRUNC_MONTH
TRUNC_WEEK
TRUNC_DAY
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.
fieldCaption
: Required. The name of the column that must be supplied. Either a reference to a specific column in the data source or, in the case of a calculation, a user-supplied name for the calculation.fieldAlias
: Optional. An alternate name to give the column in the output. This is only used in Object format output.function
: Optional. Provide a Function for a Measure to generate an aggregation against that Fieldâs values. For example, providing the SUM Function will cause an aggregated SUM to be calculated for that Field. A Field cannot contain both a Function and a Calculation.calculation
: Optional. Provide a Calculation to generate a new data Field based on that Calculation. The Calculation should contain a string based on the Tableau Calculated Field Syntax. Since this is a newly generated Field, you must give it its own unique fieldCaption
. A Field cannot contain both a Function and a Calculation.maxDecimalPlaces
: Optional. The maximum number of decimal places in the returned value. Any trailing 0s will be dropped. The maxDecimalPlaces value must be greater or equal to 0.sortDirection
: Optional. The direction of the sort, either ascending or descending.sortPriority
: Optional. To enable sorting on a specific Field, provide a sortPriority for that Field, and that Field will be sorted. The sortPriority provides a ranking of how to sort Fields when multiple Fields are being sorted. The highest priority (lowest number) field is sorted first. If only one field is being sorted, then any value may be used for sortPriority. The value should be an integer and can be negative.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.
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.
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):
MIN
: The âminâ value must be set.MAX
: The âmaxâ value must be set.RANGE
: Both the âminâ and âmaxâ values must be set.ONLY_NULL
: Show only null values in the return data set. That is, donât include a âminâ value or a âmaxâ value.ONLY_NON_NULL
: Show only non-null values. That is, donât include a âminâ value or a âmaxâ value.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.
For measures, set the filterType
to QUANTITATIVE_NUMERICAL
.
// measure range example
"filters": [
{
"column": {
"fieldCaption": "Sales",
"function": "SUM"
},
"filterType": "QUANTITATIVE_NUMERICAL",
"quantitativeFilterType": "MIN",
"min": 10000
}
]
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"
}
]
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:
DAY
WEEK
MONTH
QUARTER
YEAR
dateRangeType
can be one of the following values;
last
current
next
lastN
(requires you to add rangeN
field)nextN
(requires you to add rangeN
field)Todate
See the following screenshot of the Tableau user interface to conceptualize what these dateRangeTypes
mean.
// 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
.
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:
column
or columnName
: The same as other queries, this is the column on which you want to filter.fieldToMeasure
: This is a filter column on which you are finding the top or bottom results of.direction
: Either TOP
or BOTTOM
to show the highest or lowest results.howMany
: An integer for how many results you would like to see.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
.
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
.
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
}
]
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.
returnFormat
: Whether the return format is OBJECTS (human-readable) or ARRAYS (compact).debug
: (Boolean) Returns more detailed error messages from VDS in debug mode.disaggregate
: (Boolean) Determines whether to aggregate results. This is the equivalent of Tableau web authoring UI. For help, see Disaggregate Data. This is only available for Query data source.{
"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
}
}
VDS always returns the response body in JSON.
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
]
]
}
For more information, see the VizQL Data Service API documentation.