joinListToArray

[ 1 Operator usage in easy mode ] [ 2 Usage details ] [ 3 Example ] [ 4 Step 1: ] [ 5 Step 2: ]

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

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

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

id	user	_raw

id	user	_raw
1	emil	[{"A":1}, {"A":2, "B":2}]
2	kumar	[]
3	peter	{B:[{"A":1}, {"A":2, "B":2}]}
4	saurabh	null
5	mridul

Step 1:

Apply jsonListToArray operator

LQL command

a = jsonListToArray(table, "_raw")

Output

id	user	_raw	array__raw

id	user	_raw	array__raw
1	emil	[{"A":1}, {"A":2, "B":2}]	WrappedArray({"A":1}, {"A":2,"B":2})
2	kumar	[]	WrappedArray()
3	peter	{B:[{"A":1}, {"A":2, "B":2}]}	null
4	saurabh	null	null
5	mridul		null

Step 2:

Explode array column in above table

LQL Command

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

Output

id	user	_raw	array__raw	explodedField

id	user	_raw	array__raw	explodedField
1	emil	[{"A":1}, {"A":2, "B":2}]	WrappedArray({"A":1}, {"A":2,"B":2})	{"A":1}
1	emil	[{"A":1}, {"A":2, "B":2}]	WrappedArray({"A":1}, {"A":2,"B":2})	{"A":2, "B":2}

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.