columnsToJsonV2

Create a JSON column from a list of columns

Given multiple columns, create a single JSON column from them. The JSON object can then be manipulated by standard Spark UDFs, such as get_json_object(), in the LQL.

Operator Usage in Easy Mode

  1. Click + on the parent node.
  2. Enter Columns to JSON V2 operator in the search field and select the operator from the Results to open the operator form.
  3. In the Input Table drop-down, enter or select the table containing the data to run this operator on.
  4. Optional. In the List of Columns, click Add More to add a list of columns to create a JSON column.
  5. Click Run to view the result.
  6. Click Save to add the operator to the playbook.
  7. Click Cancel to discard the operator form.

Usage Details

columnsToJsonV2(table, columns)

table: Table name
columns: Comma-separated list of columns, such as "col1 as int", "col2", "col3 as json", "col4 as double"

Example

Input
table

score sources
3.5["VirusTotal", "Zscaler"]
columnsToJsonV2(table, "score as double", "sources as json")

Output
jsonColumnsTable

score sources lhub_json_column
3.5["VirusTotal", "Zscaler"]{"score": 3.5, "sources": ["VirusTotal", "Zscaler"]}

The json-field can then be extracted from LQL, for example:
SELECT get_json_object(lhub_json_column, "$.sources[0]") AS source FROM jsonColumnsTable


Did this page help you?