title: How to select a set of fields from input data as an array of repeated fields in beam SQL
body: <p>Problem Statement:</p>
  
  <p>I  have  an  input  PCollection  with  following  fields:</p>
  
  <pre><code>{
     firstname_1,
     lastname_1,
     dob,
     firstname_2,
     lastname_2, 
     firstname_3,
     lastname_3,
  }
  </code></pre>
  
  <p>then I execute a Beam SQL  operation such that output of resultant  PCollection should be like</p>
  
  <pre><code> ----------------------------------------------
     name.firstname |  name.lastname | dob
   ---------------------------------------------- 
        firstname_1 |  lastname_1    | 202009
        firstname_2 |  lastname_2    | 
        firstname_3 |  lastname_3    |
  -----------------------------------------------
  </code></pre>
  
  <p>To be precise:</p>
  
  <pre><code>array[
      (firstname_1,lastname_1,dob),
      (firstname_2,lastname_2,dob),
      (firstname_3,lastname_3,dob)
  ]
  </code></pre>
  
  <p>Here is the code snippet where I execute Beam SQL:</p>
  
  <pre class="lang-java prettyprint-override"><code>PCollectionTuple tuple=
      PCollectionTuple.of(new TupleTag&lt;&gt;("testPcollection"), testPcollection);
  
  PCollection&lt;Row&gt; result = tuple
      .apply(SqlTransform.query(
          "SELECT array[(firstname_1,lastname_1,dob), (firstname_2,lastname_2,dob), (firstname_3,lastname_3,dob)]"));
  
  </code></pre>
  
  <p>I am not getting proper results.</p>
  
  <p>Can someone guide  me  how to query an array of repeated field in Beam  SQL?</p>