我有一个实体:
{
"id": "123",
"col_1": null,
"sub_entities": [
{ "sub_entity_id": "s-1", "col_2": null },
{ "sub_entity_id": "s-2", "col_2": null }
]
}我把它加载到spark:val entities = spark.read.json("...")上。
entities.filter(size($"sub_entities.col_2") === 0)什么也不返回。这个行为看起来很奇怪,因为所有的col_2都是空的,但是空值是计数的。
然后我尝试选择col_2,并注意到它返回一个空值数组(在本例中为2个空值)。
entities.select($"col_1", $"sub_entities.col_2").show(false)
+--------+------------------+
|col_1 |sub_entities.col_2|
+--------+------------------+
|null |[,] |
+--------+------------------+如何编写只从col_2不为空的数组中返回对象的查询?
发布于 2020-02-26 03:04:23
要查询array对象,我们需要首先使用explode函数将数组扁平化,然后查询数据!
Example:
val df=spark.read.json(Seq("""{"id": "123","col_1": null,"sub_entities": [ { "sub_entity_id": "s-1", "col_2": null }, { "sub_entity_id": "s-2", "col_2": null }]}""").toDS)
df.selectExpr("explode(sub_entities)","*").select("col.*","id","col_1").show()
//+-----+-------------+---+-----+
//|col_2|sub_entity_id| id|col_1|
//+-----+-------------+---+-----+
//| null| s-1|123| null|
//| null| s-2|123| null|
//+-----+-------------+---+-----+
df.selectExpr("explode(sub_entities)","*").select("col.*","id","col_1").filter(col("col_2").isNull).show()
//+-----+-------------+---+-----+
//|col_2|sub_entity_id| id|col_1|
//+-----+-------------+---+-----+
//| null| s-1|123| null|
//| null| s-2|123| null|
//+-----+-------------+---+-----+发布于 2020-02-26 03:13:38
这只过滤掉了前面提到的col_2数组,如果在执行df.select($"col_1", $"sub_entities").show时需要不同的输出,我可以更新答案:
val json =
"""
{
"id": "123",
"col_1": null,
"sub_entities": [
{ "sub_entity_id": "s-1", "col_2": null },
{ "sub_entity_id": "s-2", "col_2": null }
]
}
"""
val df = spark.read.json(Seq(json).toDS)
val removeNulls = udf((arr : Seq[String]) => arr.filter((x: String) => x != null))
df.select($"col_1", removeNulls($"sub_entities.col_2").as("sub_entities.col_2")).show(false)
+-----+------------------+
|col_1|sub_entities.col_2|
+-----+------------------+
|null |[] |
+-----+------------------+https://stackoverflow.com/questions/60405279
复制相似问题