scala - 将 JSON 键/值对列表拆分为数据集中一行的列

Question

我有一列包含键/值对象列表:

+----+--------------------------------------------------------------------------------------------+
|ID  | Settings                                                                                   |
+----+--------------------------------------------------------------------------------------------+
|1   | [{"key":"key1","value":"val1"}, {"key":"key2","value":"val2"}, {"key":"key3","value":"val3"}] |
+----+--------------------------------------------------------------------------------------------+

是否可以将此对象列表拆分为自己的行？因此:

+----+------+-------+-------+
|ID  | key1 | key2  |  key3 |
+----+------+-------+-------+
|1   | val1 | val2  |  val3 |
+----+------+-------+-------+

我尝试过爆炸并放入结构中:

   case class Setting(key: String, value: String)
   val newDF = df.withColumn("setting", explode($"settings"))
                .select($"id", from_json($"setting" Encoders.product[Setting].schema) as 'settings)

这给了我:

+------+------------------------------+
|ID    |settings                      |
+------+------------------------------+
|1     |[key1,val1]                   |
|1     |[key2,val2]                   |
|1     |[key3,val3]                   |
+------+------------------------------+

从这里我可以使用这样的settings.key指定的行但这并不完全是我所需要的。我需要访问一行数据中的多个键

Answer 1

如果你已经得到了这个，你就快到了

+------+------------------------------+
|ID    |settings                      |
+------+------------------------------+
|1     |[key1,val1]                   |
|1     |[key2,val2]                   |
|1     |[key3,val3]                   |
+------+------------------------------+

现在您可以使用数据透视表将数据 reshape 为

newDF.groupBy($"ID")
  .pivot("settings.key")
  .agg(first("settings.value"))

按 ID 分组并使用pivot，使用agg获取first值，但您可以使用任何其他函数此处。

输出:

+---+----+----+----+
|ID |key1|key2|key3|
+---+----+----+----+
|1  |val1|val2|val3|
+---+----+----+----+

希望这有帮助!