Virtual columns

Apache Druid supports two query languages: Druid SQL and native queries. This document describes the native language. For information about functions available in SQL, refer to the SQL documentation.

Virtual columns are queryable column “views” created from a set of columns during a query.

A virtual column can potentially draw from multiple underlying columns, although a virtual column always presents itself as a single column.

Virtual columns can be referenced by their output names to be used as dimensions or as inputs to filters and aggregators.

Each Apache Druid query can accept a list of virtual columns as a parameter. The following scan query is provided as an example:

{
 "queryType": "scan",
 "dataSource": "page_data",
 "columns":[],
 "virtualColumns": [
    {
      "type": "expression",
      "name": "fooPage",
      "expression": "concat('foo' + page)",
      "outputType": "STRING"
    },
    {
      "type": "expression",
      "name": "tripleWordCount",
      "expression": "wordCount * 3",
      "outputType": "LONG"
    }
  ],
 "intervals": [
   "2013-01-01/2019-01-02"
 ]
}

Virtual column types

Expression virtual column

Expression virtual columns use Druid’s native expression system to allow defining query time transforms of inputs from one or more columns.

The expression virtual column has the following syntax:

{
  "type": "expression",
  "name": <name of the virtual column>,
  "expression": <row expression>,
  "outputType": <output value type of expression>
}

Nested field virtual column

The nested field virtual column is an optimized virtual column that can provide direct access into various paths of a COMPLEX<json> column, including using their indexes.

This virtual column is used for the SQL operators JSON_VALUE (if processFromRaw is set to false) or JSON_QUERY (if processFromRaw is true), and accepts ‘JSONPath’ or ‘jq’ syntax string representations of paths, or a parsed list of “path parts” in order to determine what should be selected from the column.

You can define a nested field virtual column with any of the following equivalent syntaxes. The examples all produce the same output value, with each example showing a different way to specify how to access the nested value. The first is using JSONPath syntax path, the second with a jq path, and the third uses pathParts.

    {
      "type": "nested-field",
      "columnName": "shipTo",
      "outputName": "v0",
      "expectedType": "STRING",
      "path": "$.phoneNumbers[1].number"
    }

    {
      "type": "nested-field",
      "columnName": "shipTo",
      "outputName": "v1",
      "expectedType": "STRING",
      "path": ".phoneNumbers[1].number",
      "useJqSyntax": true
    }

    {
      "type": "nested-field",
      "columnName": "shipTo",
      "outputName": "v2",
      "expectedType": "STRING",
      "pathParts": [
        {
          "type": "field",
          "field": "phoneNumbers"
        },
        {
          "type": "arrayElement",
          "index": 1
        },
        {
          "type": "field",
          "field": "number"
        }
      ]
    }

Nested path part

Specify pathParts as an array of objects that describe each component of the path to traverse. Each object can take the following properties:

See Nested columns for more information on ingesting and storing nested data.

List filtered virtual column

This virtual column provides an alternative way to use ‘list filtered’ dimension spec as a virtual column. It has optimized access to the underlying column value indexes that can provide a small performance improvement in some cases.

    {
      "type": "mv-filtered",
      "name": "filteredDim3",
      "delegate": "dim3",
      "values": ["hello", "world"],
      "isAllowList": true
    }