unnest_data.malloynb

>>>markdown
# Arrays - Unnesting Data
Malloy can naturally read array data when it is in a source.  Currently there is no mechanism to join an array in the Malloy language so we can drop into to SQL to do this.

In the source below we add a column that is based on the `SPLIT` function that returns an array.  When Malloy looks at the SQL for this query, it sees an array and adds as a nested join.  In this case we are looking at the words that appear in the 'CITY' column.  

SQL is optimized so if this column isn't used in a subsequent query, it is not computed.
>>>malloy
source: airports is duckdb.sql("""
  SELECT 
    *,
    SPLIT(city,' ') as city_words
  FROM 'data/airports.parquet' 
""") extend {
  measure:
    airport_count is count()
}
>>>markdown
We can now 'city_words', the words that appear in the city column
>>>malloy
run: airports -> {
  group_by: city_word is city_words.value
  aggregate: airport_count
  # list_detail 
  nest: by_city is {
    group_by: city
    aggregate: airport_count
  }
}
>>>markdown
## Cross Joining an array
Sometimes it is useful to join in an array of numbers.  Example below joins in some number
>>>malloy
source: flights is duckdb.table('data/flights.parquet') extend {
  measure: flight_count is count()
}

run: flights -> {
  extend: {
    join_cross: threshold is duckdb.sql(""" SELECT UNNEST([5, 10, 11, 12, 13, 15, 20]) as num""" )
  }
  group_by: threshold.num
  aggregate:
    true_pos is flight_count {where: dep_delay < threshold.num and arr_delay > 15 }
    false_pos is flight_count {where: dep_delay < threshold.num and arr_delay >= 15}
    false_neg is flight_count {where: dep_delay >= threshold.num and arr_delay < 15}
    true_neg is flight_count {where: dep_delay >= threshold.num and arr_delay >= 15}
    total is flight_count
  order_by: num
  where:
    arr_delay is not null and dep_delay is not null
}