JSON functions

5 min

Analytics can handle JSON fields and columns using NetScript. This page lists the JSON operations you can perform in NetScript.

'array[number]'

Use this function to extract items from an array by index. The return type varies because arrays can contain items of any type.

Function

array[number]

Data type – string

Example

users[0]

object['key1']

Use this function to extract values from an object (or dictionary) by key. The return type varies because the values can be of any type.

Function

object['key1']

Data type – string

Example

event_properties['type']

JSON for BigQuery structs

For JSON-like types, you can explore the column directly. However, for BigQuery structs, create a derived column to annotate the struct with extra information. 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']
])

Use this derived column like any other JSON-like column. The arguments for the bq_property_list function are the following:

Struct column – Contains the struct.

Key field name – Specifies the name of the key field in the struct entries, typically 'key'.

Value field name – Specifies the name of the value field in the struct entries, typically 'value'.

List of fields – Lists the fields that display within each struct item's value and corresponding data types. The valid data types are DATA_TYPE_INT64, DATA_TYPE_DOUBLE, and DATA_TYPE_STRING.