How to check if key exists in Spark SQL map type

Such construction can be used:

df.where($"map_column"("testKey").isNotNull)

For pure sql:

spark.sql("select * from my_table where mapColumn[\"testKey\"] is not null")

Figured it out. Following sql query works

select count(*) from my_table where map_column["testKey"] is not null

The solution will not work if testKey is not in the DataFrame schema, this will produce No such struct field error.

You must write a small UDF to check, like this:

import org.apache.spark.sql.functions.udf
import org.apache.spark.sql.catalyst.expressions.GenericRowWithSchema
import org.apache.spark.sql.Row

spark.udf.register("struct_get", (root:GenericRowWithSchema, path: String, defaultValue: String) => {

    var fields = path.split("\\.")
    var buffer:Row = root
    val lastItem = fields.last

    fields = fields.dropRight(1)

    fields.foreach( (field:String) => {
        if (buffer != null) {
            if (buffer.schema.fieldNames.contains(field)) {
                buffer = buffer.getStruct(buffer.fieldIndex(field))
            } else {
                buffer = null
            }
        }
    })

    if (buffer == null) {
        defaultValue
    } else {
        buffer.getString(buffer.fieldIndex(lastItem))
    }
})

SELECT struct_get(mapColumn, "testKey", "") FROM my_table

Since Spark 3.0

select * FROM table WHERE EXISTS (map_keys(field), x -> x == 'value')

Spark 3.3+

SQL:

map_contains_key(col_name, 'key')

PySpark:

F.expr("map_contains_key(col_name, 'key')")

Scala:

expr("map_contains_key(col_name, 'key')")