hospitalization
column is of type string & It contains json object
. To extract or convert string to json, prepare schema
as per data in that column.
Check below code.
scala> import org.apache.spark.sql.types._
import org.apache.spark.sql.types._
scala> val schema = DataType.fromJson("""{"type":"struct","fields":[{"name":"admitSourceCode","type":"string","nullable":true,"metadata":{}},{"name":"admitSourceReason","type":"string","nullable"
:true,"metadata":{}},{"name":"destination","type":"string","nullable":true,"metadata":{}},{"name":"eid","type":"long","nullable":true,"metadata":{}},{"name":"origin","type":"string","nullable":tr
ue,"metadata":{}},{"name":"preAdmissionIdentifierSystem","type":"string","nullable":true,"metadata":{}},{"name":"preAdmissionIdentifierValue","type":"string","nullable":true,"metadata":{}}]}""").
asInstanceOf[StructType]
scala> df.withColumn("hospitalization",from_json($"hospitalization",schema)).printSchema
root
|-- appointmentRef: string (nullable = true)
|-- billingAccount: string (nullable = true)
|-- eid: string (nullable = true)
|-- encounterLengh: string (nullable = true)
|-- hospitalization: struct (nullable = true)
| |-- admitSourceCode: string (nullable = true)
| |-- admitSourceReason: string (nullable = true)
| |-- destination: string (nullable = true)
| |-- eid: long (nullable = true)
| |-- origin: string (nullable = true)
| |-- preAdmissionIdentifierSystem: string (nullable = true)
| |-- preAdmissionIdentifierValue: string (nullable = true)
|-- priority: string (nullable = true)
|-- resourceType: string (nullable = true)
|-- status: string (nullable = true)
|-- subject: string (nullable = true)
scala> df.withColumn("hospitalization",from_json($"hospitalization",schema)).show(false)
+----------------------+--------------+---+--------------+---------------------------------------------------------------------------+--------+------------+-------+-----------+
|appointmentRef |billingAccount|eid|encounterLengh|hospitalization |priority|resourceType|status |subject |
+----------------------+--------------+---+--------------+---------------------------------------------------------------------------+--------+------------+-------+-----------+
|Appointment/12213#4200|savingsAccount|200|2 |[outp, some thing, hospital, 200, hospital, https://system123445.html, pqr]|abc |Encounter |triaged|Patient/435|
+----------------------+--------------+---+--------------+---------------------------------------------------------------------------+--------+------------+-------+-----------+
Update
Created small helper class to extract or convert json without schema.
import org.apache.spark.sql.functions._
import org.apache.spark.sql.expressions._
import org.json4s.JsonDSL._
import org.json4s._
import org.json4s.jackson.JsonMethods._
val append = udf((rowId: Long,json: String) => {
compact(render(Map("rowId" -> parse(rowId.toString),"data" ->parse(json))))
})
implicit class DFHelper(df: DataFrame) {
import df.sparkSession.implicits._
def parseJson = df.sparkSession.read.option("multiLine","true").json(df.map(_.getString(0)))
//Convert string to json object or array of json object
def extract(column: Column) = {
val updatedDF = df.withColumn("rowId",row_number().over(Window.orderBy(lit(1))))
val parsedDF = updatedDF.filter(column.isNotNull)
.select(append($"rowid",column).as("row"))
.parseJson
updatedDF.join(
parsedDF.select($"rowId",$"data".as(column.toString())),
updatedDF("rowId") === parsedDF("rowId"),
"left"
)
.drop("rowId") // Deleting added rowId column.
}
}
scala> df.extract($"hospitalization").printSchema()
root
|-- appointmentRef: string (nullable = true)
|-- billingAccount: string (nullable = true)
|-- eid: string (nullable = true)
|-- encounterLengh: string (nullable = true)
|-- hospitalization: string (nullable = true)
|-- priority: string (nullable = true)
|-- resourceType: string (nullable = true)
|-- status: string (nullable = true)
|-- subject: string (nullable = true)
|-- hospitalization: struct (nullable = true)
| |-- admitSourceCode: string (nullable = true)
| |-- admitSourceReason: string (nullable = true)
| |-- destination: string (nullable = true)
| |-- eid: long (nullable = true)
| |-- encounterLengh: string (nullable = true)
| |-- origin: string (nullable = true)
| |-- preAdmissionIdentifierSystem: string (nullable = true)
| |-- preAdmissionIdentifierValue: string (nullable = true)
scala> df.extract($"hospitalization").show(false)
+----------------------+--------------+---+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------+-------+-----------+------------------------------------------------------------------------------+
|appointmentRef |billingAccount|eid|encounterLengh|hospitalization |priority|resourceType|status |subject |hospitalization |
+----------------------+--------------+---+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------+-------+-----------+------------------------------------------------------------------------------+
|Appointment/12213#4200|savingsAccount|200|1 |{"encounterLengh": "1","preAdmissionIdentifierSystem":"https://system123445.html","preAdmissionIdentifierValue":"pqr","origin":"hospital","admitSourceCode":"outp","admitSourceReason":"some thing","eid":200,"destination":"hospital"}|abc |Encounter |triaged|Patient/435|[outp, some thing, hospital, 200, 1, hospital, https://system123445.html, pqr]|
+----------------------+--------------+---+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------+-------+-----------+------------------------------------------------------------------------------+