Introduction

Spark where() function is used to filter the rows from DataFrame based on the given condition or SQL expression.

Spark DataFrame where() Syntaxes

// Spark DataFrame where() Syntaxes
1) Dataset<T> where(Column condition)
2) Dataset<T> where(String conditionExpr) // using SQL expression
3) Dataset<T> filter(FilterFunction<T> func)

The first signature is used with conditions using Column names with col("colname") and applies condition expressions.

The second signature uses SQL expressions to filter rows.

The third signature uses a FilterFunction class applied on each row.

Before we start with examples, let's create a DataFrame.

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.types.DataTypes;
import org.apache.spark.sql.types.StructField;
import org.apache.spark.sql.types.StructType;
import org.apache.spark.sql.functions;

import java.util.Arrays;
import java.util.List;

public class SparkDataFrameExample {

    public static void main(String[] args) {
        SparkSession spark = SparkSession.builder()
                .appName("Filter DataFrame Rows")
                .master("local")
                .getOrCreate();

        List<Row> arrayStructureData = Arrays.asList(
                RowFactory.create(RowFactory.create("James", "", "Smith"), Arrays.asList("Java", "Scala", "C++"), "OH", "M"),
                RowFactory.create(RowFactory.create("Anna", "Rose", ""), Arrays.asList("Spark", "Java", "C++"), "NY", "F"),
                RowFactory.create(RowFactory.create("Julia", "", "Williams"), Arrays.asList("CSharp", "VB"), "OH", "F"),
                RowFactory.create(RowFactory.create("Maria", "Anne", "Jones"), Arrays.asList("CSharp", "VB"), "NY", "M"),
                RowFactory.create(RowFactory.create("Jen", "Mary", "Brown"), Arrays.asList("CSharp", "VB"), "NY", "M"),
                RowFactory.create(RowFactory.create("Mike", "Mary", "Williams"), Arrays.asList("Python", "VB"), "OH", "M")
        );

        StructType arrayStructureSchema = new StructType(new StructField[]{
                DataTypes.createStructField("name", new StructType(new StructField[]{
                        DataTypes.createStructField("firstname", DataTypes.StringType, true),
                        DataTypes.createStructField("middlename", DataTypes.StringType, true),
                        DataTypes.createStructField("lastname", DataTypes.StringType, true)
                }), true),
                DataTypes.createStructField("languages", DataTypes.createArrayType(DataTypes.StringType), true),
                DataTypes.createStructField("state", DataTypes.StringType, true),
                DataTypes.createStructField("gender", DataTypes.StringType, true)
        });

        Dataset<Row> df = spark.createDataFrame(arrayStructureData, arrayStructureSchema);

        df.printSchema();
        df.show();
    }
}

This yields the following schema and DataFrame results.

Output:

root
 |-- name: struct (nullable = true)
 |    |-- firstname: string (nullable = true)
 |    |-- middlename: string (nullable = true)
 |    |-- lastname: string (nullable = true)
 |-- languages: array (nullable = true)
 |    |-- element: string (containsNull = true)
 |-- state: string (nullable = true)
 |-- gender: string (nullable = true)

+--------------------+------------------+-----+------+
|                name|         languages|state|gender|
+--------------------+------------------+-----+------+
|    [James, , Smith]|[Java, Scala, C++]|   OH|     M|
|      [Anna, Rose, ]|[Spark, Java, C++]|   NY|     F|
| [Julia, , Williams]|      [CSharp, VB]|   OH|     F|
|[Maria, Anne, Jones]|      [CSharp, VB]|   NY|     M|
|  [Jen, Mary, Brown]|      [CSharp, VB]|   NY|     M|
|[Mike, Mary, Will...|      [Python, VB]|   OH|     M|
+--------------------+------------------+-----+------+

Use Column conditions to filter the rows from DataFrame, where you can express complex conditions by referring to column names using col(name). Use .equalTo() for comparison.

// DataFrame where() with Column condition
df.where(df.col("state").equalTo("OH"))
    .show(false);

This yields the following DataFrame results.

Output:

+----------------------+------------------+-----+------+
|name                  |languages         |state|gender|
+----------------------+------------------+-----+------+
|[James, , Smith]      |[Java, Scala, C++]|OH   |M     |
|[Julia, , Williams]   |[CSharp, VB]      |OH   |F     |
|[Mike, Mary, Williams]|[Python, VB]      |OH   |M     |
+----------------------+------------------+-----+------+

where() with SQL Expression

If you have a SQL background, you can use that knowledge in Spark to filter DataFrame rows with SQL expressions.

// DataFrame where() with SQL Expression
df.where("gender = 'M'")
    .show(false);

Output:

+----------------------+------------------+-----+------+
|name                  |languages         |state|gender|
+----------------------+------------------+-----+------+
|[James, , Smith]      |[Java, Scala, C++]|OH   |M     |
|[Maria, Anne, Jones]  |[CSharp, VB]      |NY   |M     |
|[Jen, Mary, Brown]    |[CSharp, VB]      |NY   |M     |
|[Mike, Mary, Williams]|[Python, VB]      |OH   |M     |
+----------------------+------------------+-----+------+

Filtering with multiple conditions

To filter rows on DataFrame based on multiple conditions, you can use either Column conditions or SQL expression. Below is a simple example, which can be extended with AND(&&), OR(||), and NOT(!) conditional expressions as needed.

// Multiple conditions
df.where(df.col("state").equalTo("OH").and(df.col("gender").equalTo("M")))
    .show(false);

Output:

+----------------------+------------------+-----+------+
|name                  |languages         |state|gender|
+----------------------+------------------+-----+------+
|[James, , Smith]      |[Java, Scala, C++]|OH   |M     |
|[Mike, Mary, Williams]|[Python, VB]      |OH   |M     |
+----------------------+------------------+-----+------+

Filtering on an Array column

You can use the first syntax when you want to filter rows from DataFrame based on a value present in an array collection column. The example below uses array_contains() SQL function which checks if a value is in an array; if present it returns true, otherwise false. You will learn more about SQL functions in later chapters.

// Filtering on an Array column
df.where(functions.array_contains(df.col("languages"), "Java"))
    .show(false);

Output:

+----------------+------------------+-----+------+
|name            |languages         |state|gender|
+----------------+------------------+-----+------+
|[James, , Smith]|[Java, Scala, C++]|OH   |M     |
|[Anna, Rose, ]  |[Spark, Java, C++]|NY   |F     |
+----------------+------------------+-----+------+

Filtering on Nested Struct columns

If your DataFrame consists of nested struct columns, you can use any of the above syntaxes to filter the rows based on the nested column.

// Struct condition
df.where(df.col("name.lastname").equalTo("Williams"))
    .show(false);

Output:

+----------------------+------------+-----+------+
|name                  |languages   |state|gender|
+----------------------+------------+-----+------+
|[Julia, , Williams]   |[CSharp, VB]|OH   |F     |
|[Mike, Mary, Williams]|[Python, VB]|OH   |M     |
+----------------------+------------+-----+------+