JSON Functions

  • Updated

Analytics has the capability to handle JSON fields/columns using NetScript. This page lists the JSON operations you can perform in NetScript.

'array[number]'​

This function extracts items from an array by index. The return type varies, since arrays can hold items of any type.

Function: array[number]

Data Type: string

Example:

users[0]
 

object['key1']​

This function extracts values from an object (i.e. dictionary) by key. The return type varies, since the values can be any type.

Function: object['key1']

Data Type: string

Example:

event_properties['type']
 

JSON indexing for BigQuery structs​

For other JSON-like types, the column can be used for JSON exploration directly, however for BigQuery structs, a derived column must be created to annotate the struct with some extra information. Here is an example of such 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']
])

This derived column can then be used like any other JSON-like column. Here are the arguments to this new bq_property_list function, in order:

  1. The struct column.
  2. String literal, name of the key field in the struct entries, i.e. this is often just 'key'.
  3. String literal, name of the value field in the struct entries, i.e. this is often just 'value'.
  4. List of fields that appear within each struct item's value, and the data type of each. The 3 data types listed above are the only ones that should be used.