如何读取Spark中的多嵌套JSON数据[duplicate]

Question

我需要将该模式格式提取到如下所示的治疗区域行项目:

trialTherapeuticAreas_ID,trialTherapeuticAreas_name,trialDiseases_id,trialDiseases_name,trialPatientSegments_id,trialPatientSegments_name

Answer 1

您需要以嵌套方式爆炸数组并在单独的列中选择结构元素为此，您需要爆炸内置函数和选择api和混淆现象。

要尝试的代码：

import org.apache.spark.sql.functions._
val finalDF = df.withColumn("trialTherapeuticAreas", explode(col("trialTherapeuticAreas")))
                                      .select(col("trialTherapeuticAreas.id").as("trialTherapeuticAreas_ID"), col("trialTherapeuticAreas.name").as("trialTherapeuticAreas_name"), explode(col("trialTherapeuticAreas.trialDiseases")).as("trialDiseases"))
                                      .select(col("trialTherapeuticAreas_ID"), col("trialTherapeuticAreas_name"), col("trialDiseases.id").as("trialDiseases_id"), col("trialDiseases.name").as("trialDiseases_name"), explode(col("trialDiseases.trialPatientSegments")).as("trialPatientSegments"))
                                      .select(col("trialTherapeuticAreas_ID"), col("trialTherapeuticAreas_name"), col("trialDiseases_id"), col("trialDiseases_name"), col("trialPatientSegments.id").as("trialPatientSegments_id"), col("trialPatientSegments.name").as("trialPatientSegments_name"))

你应该满足你的要求

您也可以使用三个< code>withColumn api和一个< code>select语句来完成上述转换。

import org.apache.spark.sql.functions._
val finalDF = df.withColumn("trialTherapeuticAreas", explode(col("trialTherapeuticAreas")))
                .withColumn("trialDiseases", explode(col("trialTherapeuticAreas.trialDiseases")))
                .withColumn("trialPatientSegments", explode(col("trialDiseases.trialPatientSegments")))
                .select(col("trialTherapeuticAreas.id").as("trialTherapeuticAreas_ID"), col("trialTherapeuticAreas.name").as("trialTherapeuticAreas_name"), col("trialDiseases.id").as("trialDiseases_id"), col("trialDiseases.name").as("trialDiseases_name"), col("trialPatientSegments.id").as("trialPatientSegments_id"), col("trialPatientSegments.name").as("trialPatientSegments_name"))

对于大型数据集，不建议连续使用 withColumn，因为它可能会给出随机输出。原因是与Column是分布式的，并且未证明以串行方式遵循执行顺序