title: PySpark | Join two dataframes in specific column
body: <p>I have a problem, and after several searches, I have not found the answer to my question.
  I am new to pySpark and more generally to dataframes.</p>
  <p>I'm looking to &quot;join&quot; / &quot;merge&quot; two dataframes in one column.</p>
  <p>I have a profiles df and an orders df, in the profile df I have a column containing arrays of orders id.</p>
  <p>I would like to add an &quot;orders&quot; column to my profile dataframe containing the orders associated to the ordersId.</p>
  <p>Instinctively I tried that, but in the where context I lose the withColumn context so I can't access my ordersId array</p>
  <pre><code>profiles = profiles.withColumn('orders', lit(orders.where(array_contains(profiles.ordersId, orders.id)).collect()))
  </code></pre>
  <p>If anyone has the answer to my problem, I'm willing to take it.</p>
  <hr />
  <p>Samples:</p>
  <pre><code>from pyspark.sql.types import StructType,StructField, StringType, IntegerType, ArrayType
  
  
  data = [(&quot;aaaa&quot;, ['cc', 'dd'])]
  
  schema = StructType([ \
      StructField(&quot;id&quot;, StringType(), False), \
      StructField(&quot;ordersId&quot;, ArrayType(StringType(), False), False), \
    ])
   
  df = spark.createDataFrame(data=data, schema=schema)
  df.printSchema()
  df.show(truncate=False)
  
  
  data2 = [(&quot;cc&quot;, &quot;titi&quot;), (&quot;dd&quot;, &quot;toto&quot;)]
  
  schema2 = StructType([ \
      StructField(&quot;id&quot;, StringType(), False), \
      StructField(&quot;alias&quot;, StringType(), False), \
    ])
   
  df2 = spark.createDataFrame(data=data2, schema=schema2)
  df2.printSchema()
  df2.show(truncate=False)
  
  
  root
   |-- id: string (nullable = false)
   |-- ordersId: array (nullable = false)
   |    |-- element: string (containsNull = false)
  
  +----+--------+
  |id  |ordersId|
  +----+--------+
  |aaaa|[cc, dd]|
  +----+--------+
  
  root
   |-- id: string (nullable = false)
   |-- alias: string (nullable = false)
  
  +---+-----+
  |id |alias|
  +---+-----+
  |cc |titi |
  |dd |toto |
  +---+-----+
  </code></pre>
  <p>And I would like:</p>
  <pre><code>+----+--------+-----------------------------------------------+
  |id  |ordersId|orders                                         |
  +----+--------+-----------------------------------------------+
  |aaaa|[cc, dd]|[{id: cc, alias: titi}, {id: dd, alias: titi}] |
  +----+--------+-----------------------------------------------+
  </code></pre>