Apache Drill - FLATTEN Function

FLATTEN separates the elements in a repeated field into individual records.

Syntax

FLATTEN(x)

Where,

x - JSON array.

Create a JSON file named “array.json” as shown in the following program.

{
   "num1" : 10,
   "num2" : [10,20,30],
   "num3" : " simple json array",
   "num4" : 50.5
}

Now we can execute this query in Drill.

The following program shows the query for this function −

Query

0: jdbc:drill:zk = local> select *,flatten(num2) as flatten from
   dfs.`/Users/../workspace/Drill-samples/array.json`;

Result

num1    num2     num3  num4   flatten
 10  [10,20,30] simple json array 50.5 10
 10  [10,20,30] simple json array 50.5 20
 10  [10,20,30] simple json array 50.5 30