Query a Data Source


🕐 11 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
    ]
  }
}

The data source object

The datasource object takes a datasourceLuid. To find your data source LUID, see Find the data source LUID.

{
"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. If you only have a single connection that requires a username and password, the connectionLuid property isn't required. The connectionLuid property is only required if you must provide credentials for multiple connections.

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

Data sources that require multiple credentials

If you have multiple connections that each require a username and password, add a list of connections. For each connection, you must provide a connectionLuid, a connectionUsername, and a connectionPassword.

To find the connectionLuid, use the Query Data Source Connections method. This method returns an ID for each connection on the specified data source. The value of your connection’s ID is the value to use for connectionLuid.

{
"datasource": {
    "datasourceLuid": "2bac64a3-e216-4d8f-891c-905f9ce33ac3",
    "connections": [
        {
          "connectionLuid": "31752d0a-ec9d-11ee-9ad5-0a61dcca52fb",
          "connectionUsername": "test",
          "connectionPassword": "password"
        },
        {
          "connectionLuid": "54ec51f6-7b1c-40d8-a55c-d4a70333c358",
          "connectionUsername": "test2",
          "connectionPassword": "password2"
        }
    ]
 },
  "query": { // See below for more details
    "fields": [
       // Fields here
    ]
  }
}

The query object

The query object contains these 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 Tableau Filter[Order Date] screen to help understand what these dateRangeTypes mean.

Filter [Order Date] screen

  1. LAST
  2. LASTN
  3. CURRENT
  4. NEXTN
  5. NEXT
  6. TODATE
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

Parameters

When you query data sources, you can include parameters in the query request payload to override existing default parameter values. You can use parameters in:

Parameters with ad-hoc calculations

Parameters can be used within ad-hoc calculated fields to create dynamic calculations directly in the data source query. Ad-hoc calculations are temporary calculations that you create by adding a calculated field. This lets you test parameter-driven logic quickly. To use parameters in ad-hoc calculations, reference them using the syntax [Parameter Caption/Name] within your calculation expression.

For example, you can create calculations like INT([Profit] / [Profit Bin Size]) * [Profit Bin Size] directly. This approach is particularly useful for testing parameter logic, experimenting with what-if scenarios, and rapid prototyping. For more information, see Ad-Hoc Calculations.

"fields": [
	{
		"fieldCaption": "Binned Profit",
		"calculation": "INT([Profit] / [Profit Bin Size]) * [Profit Bin Size]"
	}
]
Bin with a hard-coded bin size

To query a bin on Sales with a static hard-coded bin size of 10:

"query": {
    "fields": [
        {
            "fieldCaption": "Sales",
            "function": "COUNT"
        },
       // bin size is hard coded as 10 on the published data source, no way to change it here
        {
            "fieldCaption": "Sales (bin)",
            "sortPriority": 1
        }
    ]
  }

The response:

{
    "data": [
        {
            "COUNT(Sales)": 1398,
            "Sales (bin)": 0
        },
        {
            "COUNT(Sales)": 1528,
            "Sales (bin)": 10
        },
        {
            "COUNT(Sales)": 858,
            "Sales (bin)": 20
        },
        {
            "COUNT(Sales)": 690,
            "Sales (bin)": 30
        },
        {
            "COUNT(Sales)": 492,
            "Sales (bin)": 40
        },
        {
            "COUNT(Sales)": 349,
            "Sales (bin)": 50
        },
        {
            "COUNT(Sales)": 337,
            "Sales (bin)": 60
        },
... etc. ...
    ]
}

Bin with a parameter bin size

This example queries Profit (bin). This bin has a dynamic bin size, which is a parameter. We know from our metadata request that the default value of the bin size is 200. In this example, we change the bin size to 50.

Note: Because of the rules around what Profit Bin Size can be (as seen in the metadata), It can only be between 50 and 200 with a step size of 50. Eventually we will throw an error if you try to pass in a value that is not allowed.

If you try the query without using a parameter:

"query": {
    "fields": [
        {
            "fieldCaption": "Profit",
            "function": "COUNT"
        },{
            "fieldCaption": "Profit (bin)", // default value of bin size is 200
            "sortPriority": 1
        }
    ]
  }

The response:

{
    "data": [
        {
            "COUNT(Profit)": 1,
            "Profit (bin)": -33
        },
        {
            "COUNT(Profit)": 1,
            "Profit (bin)": -20
        },
        {
            "COUNT(Profit)": 1,
            "Profit (bin)": -19
        },
        {
            "COUNT(Profit)": 1,
            "Profit (bin)": -17
        },
... etc. ...
    ]
}

The same query, but you have overridden the bin size to be a different value:

"query": {
    "fields": [
        {
            "fieldCaption": "Profit",
            "function": "COUNT"
        },{
            "fieldCaption": "Profit (bin)", // We are overriding bin size below
            "sortPriority": 1
        }
    ],
    "parameters": [
        {
            "parameterCaption": "Profit Bin Size",
            "value": 50 // Override value from 200 to 50
        }
    ]
  }

The response:

{
    "data": [
        {
            "COUNT(Profit)": 1,
            "Profit (bin)": -132
        },
        {
            "COUNT(Profit)": 1,
            "Profit (bin)": -77
        },
        {
            "COUNT(Profit)": 1,
            "Profit (bin)": -75
        },
        {
            "COUNT(Profit)": 1,
            "Profit (bin)": -68
        },
        {
            "COUNT(Profit)": 1,
            "Profit (bin)": -59
        },
... etc. ...
    ]
}

Calculated field with a parameter

This example queries a calculation that makes use of a parameter.

Edit Parameter dialog box

User Greeting edit and test area

The following query doesn’t override the parameter:

"query": {
    "fields": [
        {
            "fieldCaption": "User Greeting"
        }
    ]
}

The returns shows the default greeting:

{
    "data": [
        {
            "User Greeting": "Hello, test!"
        }
    ]
}

This next query uses a parameter to override the value of the Greeting String:

"query": {
    "fields": [
        {
            "fieldCaption": "User Greeting"
        }
    ],
    "parameters": [
        {
            "parameterCaption": "Greeting String",
            "value": "Hi" // Override default with "Hi"
        }
    ]
  }

The response shows the new value:

{
    "data": [
        {
            "User Greeting": "Hi, test!"
        }
    ]
}
Create a new bin

To make a new bin on a field, give the measure field and the bin size. VDS validates that the field you provide a bin on is a measure.

For example, this query:

"query": {
    "fields": [
        {
            "fieldCaption": "Discount", // Create a new bin on the field "Discount"
            "binSize": 0.1,
            "sortPriority": 1
        },
        {
            "fieldCaption": "Discount",
            "function": "COUNT"
        }
    ]

  }

returns:

{
    "data": [
        {
            "Discount (bin)": 0.0,
            "COUNT(Discount)": 4925
        },
        {
            "Discount (bin)": 0.1,
            "COUNT(Discount)": 148
        },
        {
            "Discount (bin)": 0.2,
            "COUNT(Discount)": 3936
        },
        {
            "Discount (bin)": 0.3,
            "COUNT(Discount)": 27
        },
... etc. ...
    ]
}

The options object

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
}
}

Query using fieldName value

In the example of a calculation on the data source, the response shows both the fieldName and the fieldCaption:

{
    "data": [
        {
            "fieldName": "Calculation_1368249927221915648",
            "fieldCaption": "Profit Ratio",
            "dataType": "REAL",
            "defaultAggregation": "AGG",
            "columnClass": "CALCULATION",
            "formula": "SUM([Profit])/SUM([Sales])"
        }
    ]
}

If you set "interpretFieldCaptionsAsFieldNames": true,, you can use the fieldName value as the value for fieldCaption in your query. In this case, your query would use "fieldCaption":"Calculation_1368249927221915648".

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.