Native functions are disabled by default. See the Enabling native functions section in this article for instructions on how to enable native functions.
The NATIVE_DIMENSION function lets you write SQL directly into a Looker Studio field. This is useful for functions that are not included in the function list, but that your database supports.
- Enabling native functions
- Sample usage
- Syntax
- NATIVE_DIMENSION examples
- Limits of NATIVE_DIMENSION
- Related resources
Enabling native functions
Native functions are disabled by default and are managed at the data source level.
The NATIVE_DIMENSION function can be used only with a Google BigQuery data source for which the BigQuery native integration in Looker Studio is enabled.
If you are an owner of a Google BigQuery data source, you can enable native functions for the data source by following these steps:
- Enable the BigQuery native integration in Looker Studio.
- In Looker Studio, edit the data source.
- In the top right of the data source editor, click Native functions.
- In the Native functions dialog, select On.
- Click Save.
If you disable native functions, no users can create fields using native functions. Additionally, any existing charts that reference fields with native functions will display an error.
Sample usage
NATIVE_DIMENSION("JSON_VALUE('{"name": "Dana"}', '$.name')","STRING")
Syntax
NATIVE_DIMENSION(expression, type)
Parameters
expression - An expression to be evaluated by the underlying dataset. This can be any valid Google BigQuery SQL that would go into a single column of a SELECT statement. The expression cannot include any aggregations.
type - A data type literal. See the following section for the types that this function supports.
NATIVE_DIMENSION types
You can select from the following types:
| Type | Expected data | Report examples |
|---|---|---|
|
Zero or more letters, numbers, characters, or symbols. |
|
BOOL |
A logical value. |
|
DOUBLE |
A floating point number. | 1,234.56 |
INT64 |
An integer. | 1,234 |
DATE |
A calendar date. |
|
DATETIME |
A calendar date with time. | 2020-09-21 12:35 PM |
GEOGRAPHY |
A collection of points and polygons that correspond to the BigQuery geography data type. Only the BigQuery functions |
|
Looker Studio will return an error if your expression returns an unsupported data type, such as ARRAY, STRUCT, or JSON.
NATIVE_DIMENSION examples
JSON functions in SQL
Assume that your data includes a users_ages_json field whose values are formatted as JSON payloads. An example row value of this field might look like the following example:
{"name": "Jakob", "age": "26"}
Use the NATIVE_DIMENSION function to retrieve the age from users_ages_json, using BigQuery's JSON_VALUE function:
NATIVE_DIMENSION("JSON_VALUE(user_ages_json, '$.age')","INT64")
For this example row value, the NATIVE_DIMENSION function would return the age 26 as an integer.
Limits of NATIVE_DIMENSION
Supported data sources
The NATIVE_DIMENSION function can be used only with a Google BigQuery data source for which the BigQuery native integration in Looker Studio is enabled.
Who can use native functions
If native functions are enabled, owners and editors of the data source can create fields with native functions on the data source.
Viewers of the data source can use fields with native functions in charts. However, viewers cannot create new fields using native functions, nor can they edit any fields using native functions.
Aggregate fields
You cannot include any aggregated fields in the expression parameter.
Window functions
You cannot include any< window functions in the expression parameter.
Blends
You cannot create a field with the NATIVE_DIMENSION function on a blended data source.
However, you can create a field with this function prior to blending and then use that field in a blend.
TIMESTAMP data types
If your expression returns a timestamp data type, such as BigQuery's TIMESTAMP type, use the CAST function to cast it to a DATETIME type. For example:
NATIVE_DIMENSION("CAST({expression} AS DATETIME)", "DATETIME")