title: Join 2 dataframes and nesting a list PysparkSQL
body: <p>Hello community could you please help me to fix the method create_document_structure
  I have two dataframes returned from my query here is a simplified example :</p>
  <p>Students :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">student_id</th>
  <th style="text-align: center;">firstName</th>
  <th style="text-align: center;">lastName</th>
  <th style="text-align: center;">Age</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">tata</td>
  <td style="text-align: center;">tato</td>
  <td style="text-align: center;">23</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">xxx</td>
  <td style="text-align: center;">yyy</td>
  <td style="text-align: center;">26</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Classes:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">student_id</th>
  <th style="text-align: center;">className</th>
  <th style="text-align: center;">professorName</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">maths</td>
  <td style="text-align: center;">Mr Math</td>
  </tr>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">physics</td>
  <td style="text-align: center;">Mr Physics</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">Maths</td>
  <td style="text-align: center;">Mr XXX</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want to have a third dataframe or to write on MangoDB that will have this format :</p>
  <pre><code>
  [
    {
      &quot;student_id&quot;: &quot;string&quot;,
      &quot;firstname&quot;: &quot;string&quot;,
      &quot;lastName&quot;: &quot;string&quot;,
      &quot;classes&quot;: [
        {
          &quot;className&quot;: &quot;string&quot;,
          &quot;professorName&quot;: &quot;string&quot;
        },
        {
          &quot;className&quot;: &quot;string&quot;,
          &quot;professorName&quot;: &quot;string&quot;
        }
      ]
    }
  ]
  </code></pre>
  <p>This is the Main function that i wrote</p>
  <pre><code>def create_document_structure(df_agreements,df_third_parties, columns_to_keep):
         
   #/* Junk Method needs to be fixed */
  
      new_df = df_agreements.Array(
          'classes', struct(
              col('className'),
              col('studentName')
          )) \
          .col(&quot;student_id&quot;)
          .col(&quot;studentName&quot;)
          .col(&quot;studentLastName&quot;)
          .col(&quot;age&quot;)
      ))
  
      new_df = new_df.select(*columns_to_keep)
  
      return new_df
  
   #/* Main Method */
  
   if __name__ == &quot;__main__&quot;:
          sc = SparkContext(&quot;local&quot;, &quot;SEND TO API&quot;)
          glueContext = GlueContext(sc)
          spark = glueContext.spark_session
          job = Job(glueContext)
          df_agreements = spark.sql(finan_agreem_query)
          df_third_parties = spark.sql(third_party_query)
          #/* new df format code goes here */
          new_df = create_document_structure(df_agreements,df_third_parties,columnsToKeep)
          #/* write to MangoDB  */
          new_df.write.format(&quot;mongo&quot;).mode(&quot;overwrite&quot;) \
              .option(&quot;uri&quot;,
                      &quot;xxxxx&quot;) \
              .option(&quot;database&quot;, &quot;xxxx&quot;) \
              .option(&quot;collection&quot;, &quot;xxxxx&quot;) \
              .save()
  </code></pre>