JSON functions
JavaScript Object Notation (JSON) functions read values from JSON columns, such as event properties stored as JSON. Use them to pull a single field or array item into a formula. Before you start, review the NetScript language reference for formula syntax.
ARRAY[INDEX]
Use the [INDEX] subscript to get an array item by position, such as the first user in a list. Enter the index as a number, starting at 0. The return type varies because arrays hold items of any type. To use the value as a specific type, convert it with a function such as to_string or to_int.
Function
ARRAY[INDEX]Data type – varies
Example
users[0]Result
The first item in the users arrayOBJECT['KEY']
Use the ['KEY'] subscript to get a value from an object by key, such as an event property. Enter the key as a string in single quotes. The return type varies because object values have no fixed type. To use the value as a specific type, convert it with a function such as to_string or to_int.
Function
OBJECT['KEY']Data type – varies
Example
event_properties['type']Result
The value of the type key in event_propertiesto_array(JSON_ARRAY)
Use the to_array function to group results by each item in a JSON array, such as each product tag. When you add to_array to a by operation, Analytics returns one group for each array item. Analytics converts each item to a string.
Function
to_array(JSON_ARRAY)Data type – array
Example
Events | count by (to_array(Events.event_properties['tags']))Result
The number of events for each tag in the tags arraylax_*(JSON_VALUE)
Use the lax_* functions to convert a JSON value to a specific data type in BigQuery. When BigQuery cannot convert the value, the function returns null instead of an error. Each function calls the BigQuery function of the same name and returns the following data type:
- lax_string – string
- lax_int64 – number
- lax_float64 – number
- lax_bool – boolean
The lax_* functions work only on BigQuery. For other data warehouses, use a type conversion function, such as to_int or to_string. On BigQuery, those functions already call the matching lax_* function when the input is a JSON value. For the type conversion functions, go to Utility functions.
Function
lax_string(JSON_VALUE)
lax_int64(JSON_VALUE)
lax_float64(JSON_VALUE)
lax_bool(JSON_VALUE)Data type – string, number, or boolean
Example
lax_int64(event_properties['quantity'])Result
The quantity as a number, or null when BigQuery cannot convert the valueJSON for BigQuery structs
Analytics reads JSON-like columns directly. A BigQuery struct is a record that groups related fields. For BigQuery structs, create a derived column with the bq_property_list function first. The derived column adds type information to the struct, so Analytics reads it like a JSON column. The following is an example of a derived column:
bq_property_list(
Events.event_params,
'key',
'value',
[
['int_val', 'DATA_TYPE_INT64'],
['float_val', 'DATA_TYPE_DOUBLE'],
['double_val', 'DATA_TYPE_DOUBLE'],
['string_val', 'DATA_TYPE_STRING']
])The bq_property_list function takes the following arguments, in order:
- STRUCT_COLUMN – The column that contains the struct.
- KEY_FIELD – The name of the key field in the struct entries, typically 'key'.
- VALUE_FIELD – The name of the value field in the struct entries, typically 'value'.
- FIELD_LIST – (Optional) The fields in each struct item's value and their data types. The valid data types are DATA_TYPE_INT64, DATA_TYPE_DOUBLE, DATA_TYPE_STRING, and DATA_TYPE_BOOL. If you omit the list, Analytics reads each value as a string.
After you create the derived column, use it like any other JSON-like column.