我有一個如下的資料框
val data = Seq(
"""{"Data": [{ "name": "FName", "value": "Alex" }, { "name": "LName", "value": "Johnson" }]}""",
"""{"Data": [{ "name": "FName", "value": "Alexis" }, { "name": "LName", "value": "Paul" }]}""",
"""{"Data": [{ "name": "FName", "value": "Alexander" }, { "name": "LName", "value": "Strong" }]}""",
"""{"Data": [{ "name": "FName", "value": "Baron" }, { "name": "LName", "value": "Corbin" }]}""",
)
val df = spark.read.json(spark.sparkContext.parallelize(data))
df.createOrReplaceTempView("df")
架構如下
root
|-- Data: array (nullable = true)
| |-- element: struct (containsNull = true)
| | |-- name: string (nullable = true)
| | |-- value: string (nullable = true)
以上df的資料輸出如下
Data
[{"name":"FName","value":"Alex"},{"name":"LName","value":"Johnson"}]
[{"name":"FName","value":"Alexis"},{"name":"LName","value":"Paul"}]
[{"name":"FName","value":"Alexander"},{"name":"LName","value":"Strong"}]
[{"name":"FName","value":"Baron"},{"name":"LName","value":"Corbin"}]
我需要以“Alex”開頭的 Fname 的所有記錄
預期產出
Data
[{"name":"FName","value":"Alex"},{"name":"LName","value":"Johnson"}]
[{"name":"FName","value":"Alexis"},{"name":"LName","value":"Paul"}]
[{"name":"FName","value":"Alexander"},{"name":"LName","value":"Strong"}]
火花 SQL 查詢 1：
select * from df where array_contains (Data.value, "Al%")
火花 SQL 查詢 2：
select * from df where array_contains (Data.value, "Al*")
這兩個查詢結果都是空的。
火花 SQL 查詢 3：
select * from df where array_contains (Data.value, "Alex")
結果：
Data
[{"name":"FName","value":"Alex"},{"name":"LName","value":"Johnson"}]
如何在 array_contains 上做 Like 或 regex？
uj5u.com熱心網友回復：
改用exists函式：
select * from df where exists(Data.value, x -> x like 'Al%')
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/448521.html
標籤：sql 斯卡拉 阿帕奇火花 apache-spark-sql
上一篇：根據串列打開多個WORDFILES，執行任務，保存并關閉
下一篇：將多列轉換為具有可變列數的單行