title: PySpark - saving JSON column to Postgres
body: <p>I am trying to insert PySpark DataFrame with JSON / struct column to Postgres. My data:</p>
  <pre><code>+---------+------------+-------------+----------+
  |  user_id|    group_id|point_balance|   details|
  +---------+------------+-------------+----------+
  |        1|           1|          777|      null|
  |        2|           1|          888|      null|
  |        3|           2|          168|      null|
  |        4|           2|          425|{1, thing}|
  +---------+------------+-------------+----------+
  </code></pre>
  <p><code>details</code> is of type <code>struct&lt;abc:bigint,some:string&gt;</code> (this is test data, so those are example JSON keys and values).</p>
  <p>I'm trying to save the data to local Postgres:</p>
  <pre><code>db_url = &quot;jdbc:postgresql://localhost:5433/test_db&quot;
  table = &quot;user_points&quot;
  
  (
      user_points
      .write
      .format(&quot;jdbc&quot;)
      .option(&quot;url&quot;, db_url)
      .option(&quot;dbtable&quot;, table)
      .option(&quot;user&quot;, &quot;postgres&quot;)
      .option(&quot;password&quot;, &quot;postgres&quot;)
      .mode(&quot;overwrite&quot;)
      .save()
  )
  </code></pre>
  <p>I get error <code>IllegalArgumentException: Can't get JDBC type for struct&lt;abc:bigint,some:string&gt;</code>.</p>
  <p>I tried <a href="https://stackoverflow.com/questions/67601685/spark-write-dataframe-with-hashmap-to-postgres-as-json">this solution</a>:</p>
  <pre><code>(
      user_points
      .select(&quot;user_id&quot;, &quot;group_id&quot;, &quot;point_balance&quot;, F.to_json(F.struct(&quot;details)).alias(&quot;details))
      .write
      .format(&quot;jdbc&quot;)
      .option(&quot;url&quot;, db_url)
      .option(&quot;dbtable&quot;, table)
      .option(&quot;user&quot;, &quot;postgres&quot;)
      .option(&quot;password&quot;, &quot;postgres&quot;)
      .mode(&quot;overwrite&quot;)
      .save()
  )
  </code></pre>
  <p>This works, but it creates column of type <code>TEXT</code>, while I want proper <code>JSONB</code>.</p>
  <p>Explicitly using <code>JSONB</code> does not work:</p>
  <pre><code>.option(&quot;createTableColumnTypes&quot;, &quot;user_id INTEGER, group_id INTEGER, point_balance INTEGER, details JSONB&quot;)
  </code></pre>
  <pre><code>ParseException: 
  DataType jsonb is not supported.(line 1, pos 72)
  
  == SQL ==
  user_id INTEGER, group_id INTEGER, point_balance INTEGER, details JSONB
  ------------------------------------------------------------------------^^^
  </code></pre>
  <p>How can I achieve that? I would prefer to do this in Spark, but casting in Postgres would also be ok.</p>