title: Extract N values from a column which is a struct within an array within a struct within an array
body: <p>Say I have a table with the following structure inside one of the cells:</p>
  <pre><code>/*
  [] = array
  () = struct
  {} = map
  */
  
  master_array = 
  [
    (
      thing_starts: ,
      thing_ends: ,
      main_var: ,
      main_var_desc: {var: , id: , element: }
      main_var_desc_plus: {outcome: , outcome_plus: },
      main_var_desc_ids: {care: [], into: []},
      main_var_process: [
                             (thing_starts: ,       **I want this data point**
                              thing_ends: ,         **and this one**
                              main_var: ,           **and this one**
                              main_var_metrics: {outcome: , outcome_plus: }
                             ), 
                             (thing_starts: ,
                              thing_ends: , 
                              main_var: , 
                              main_var_metrics: {outcome: , outcome_plus: }
                             )
                            ]
    ),
    (
      thing_starts: ,
      thing_ends: ,
      main_var: ,
      main_var_desc: {var: , id: , element: }
      main_var_desc_plus: {outcome: , outcome_plus: },
      main_var_desc_ids: {care: [], into: []},
      main_var_process: [
                             (thing_starts: ,
                              thing_ends: , 
                              main_var: , 
                              main_var_metrics: {outcome: , outcome_plus: }
                             )
                            ]
    )
  ]
  </code></pre>
  <p>As you can see there are structs within arrays within structs within an array. In the same table I have column_1 and column_2 which contain a unique ID for that row, and a date respectively. And the nested array-struct column, shown above, can repeat any number of times (it's not always a fixed number).</p>
  <p>I would like to create a new table from this one that includes column_1, and column_2 (that bit's easy) as well as thing_starts, thing_ends, and main_var nested within each master_array. I've added in comments (*<em>I want this data point</em>*, and *<em>and this one</em>*) to the above structure to show you exactly which bits I want.</p>
  <p>In the end I would end up with a table with that includes the N values of <code>thing_starts</code>, <code>thing_ends</code>, and <code>main_var</code>, but split out into separate columns so we have <code>thing_starts_1</code>, <code>thing_ends_1</code>, <code>main_var_1</code>, <code>thing_starts_2</code>, <code>thing_ends_2</code>, <code>main_var_2</code>, etc., up to <code>thing_starts_N</code>, <code>thing_ends_N</code>, and <code>main_var_N</code>. The end result being a flat table with one row per ID number and one column per thing_starts for each value of main_var, thing_starts for each value of main_var, and the corresponding main_var names.</p>
  <p>I can do the first bit easily, e.g.:</p>
  <pre><code>select column_1, 
  column_2
  from table limit 10
  </code></pre>
  <p>And my latest attempt at trying to get the main_var, thing_ends, and thing_starts values was something like this:</p>
  <pre><code>select column_1, 
  column_2,
  master_array.main_var_process.thing_starts[1],
  master_array.main_var_process.thing_ends[1],
  master_array.main_var_process.main_var[1]
  from table limit 10
  </code></pre>
  <p>But clearly this is totally wrong. Any ideas on how I can do what i've described above?</p>
  <p>P.S. i'm using Spark with MySQL</p>