jsonToColumns

[ 1 Operator usage in easy mode ] [ 2 Usage details ] [ 3 Example ]

Split a JSON (column) object into multiple fields, where the new field names consist of the input field name followed by underscore (_) followed by the JSON field ID.

Operator usage in easy mode

Click + on the parent node.
Enter JSON to Columns operator in the search field and select the operator from the Results to open the operator form.
In the Input Table drop-down, enter or select the table containing the data to run this operator on.
In the JSON Field drop-down, enter or select the column that splits the JSON objects into multiple columns.
In the Defaults Field, click Add More to add a string of default field names.
Click Run to view the result.
Click Save to add the operator to the playbook.
Click Cancel to discard the operator form.

Usage details

LQL Command

jsonToColumns(table, "jsonField")

-- with default fields
jsonToColumns(table, "jsonField", "a", "test_1")

Input:

table: Name of the input table.
"jsonField": Column (field) name that contains a JSON object (quotes are required).
"defaultFields": A comma-separated string of default field names. Only these fields that are mentioned here will be parsed and converted to columns. If a field does not exist in the JSON object, this operator will create the column and insert nulls.

Output:
A JSON object that is split into multiple columns. Each column is in the form jsonField__id, where jsonField is the input field and id is the JSON ID.

Example

Input
table = github_logs

id	jsonField

id	jsonField
id	{"a":{"b":2,"u":1},"e":3}
id	{"a":{"b":4,"v":9},"c":5,"d":7}

LQL command

jsonToColumns(table, "jsonfield")

Output

id	jsonField	jsonField__a	jsonField__c	jsonField__d	jsonField__e

id	jsonField	jsonField__a	jsonField__c	jsonField__d	jsonField__e
id	{"a":{"b":2,"u":1},"e":3}	{"b":2,"u":1}			3
id	{"a":{"b":4,"v":9},"c":5,"d":7}	{"b":4,"v":9}	5	7

Another example with default fields

LQL command

jsonToColumns(table, "jsonField", "a", "test_1")

Output

id	jsonField	jsonField__a	jsonField__test_1

id	jsonField	jsonField__a	jsonField__test_1
id	{"a":{"b":2,"u":1},"e":3}	{"b":2,"u":1}	null
id	{"a":{"b":4,"v":9},"c":5,"d":7}	{"b":4,"v":9}	null

This operator is not recursive. It splits only the first layer. In the example, "b" and "u" or "b" and "v" are subfields of the "a" field, so they are not split into multiple columns.