jsonListToArray

Convert a JSON list object to a SQL array so you can extract values or split the array into multiple columns using the explore UDF.

Operator Usage in Easy Mode

1. Click + on the parent node.
2. Enter the JSON List to Array 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. In the JSON Field, enter or select the column that contains the JSON list object.
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

jsonListToArray(table, column)

-- INPUT: 
-- table: table name
-- column: name of a column that contains the json list object

-- OUTPUT:
-- prefix array_ is added to input column name in the arg.

-- NOTE: if value is not json list, then it will return null. If it is valid json array, then it will convert to ARRAY(values).

Example

Input

Step 1:

Apply jsonListToArray operator

a = jsonListToArray(table, "_raw")

Output

Step 2:

Explode array column in above table

b = select *, explode(array__raw) as explodedField from a

Output

Note: When you explode an empty array, it is removed in the SQL. In the example, id = 2 is removed.
id3 is not JSON list object, so it returned as it is.