This is a common PySpark (Spark SQL) interview and practice question. Below is the full answer with a clear, step-by-step explanation.

Question

Problem A company receives employee information as nested JSON from an HR API. Each employee record contains a nested address object. Before loading the data into the data warehouse, the nested fields must be flattened into separate columns. Sample JSON [ { "employee_id":101, "name":"Alice", "address":{ "city":"New York", "state":"NY", "zip":"10001" } }, { "employee_id":102, "name":"Bob", "address":{ "city":"Seattle", "state":"WA", "zip":"98101" } } ] Task Flatten the nested address object into individual columns named city , state , and zip . Expected Output +-----------+------+----------+-----+-----+ |employee_id|name |city |state|zip | +-----------+------+----------+-----+-----+ |101 |Alice |New York |NY |10001| |102 |Bob |Seattle |WA |98101| +-----------+------+----------+-----+-----+

Answer & Explanation

**What you need to do**

You have a JSON array where each element represents an employee. Inside each employee record there is an `address` object that contains three fields: `city`, `state` and `zip`. The goal is to turn those three nested fields into regular columns so the final table looks like:

```
employee_id | name | city | state | zip
------------------------------------------------
101 | Alice | New York | NY | 10001
102 | Bob | Seattle | WA | 98101
```

Below is a step‑by‑step guide that shows the simplest, most idiomatic way to achieve this in PySpark.

---

### 1. Create a Spark session

You need a Spark session to work with DataFrames.

```python
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("FlattenAddress").getOrCreate()
```

---

### 2. Define the schema that matches the JSON structure

Because the JSON contains a nested object, we describe it with a `StructType`.
The outer struct has three fields: `employee_id` (integer), `name` (string) and `address` (another struct). The inner struct (`address`) has `city`, `state` and `zip`, all strings.

```python
from pyspark.sql.types import StructType, StructField, IntegerType, StringType

schema = StructType([
StructField("employee_id", IntegerType(), nullable=False),
StructField("name", StringType(), nullable=False),
StructField("address", StructType([
StructField("city", StringType(), nullable=True),
StructField("state", StringType(), nullable=True),
StructField("zip", StringType(), nullable=True)
]), nullable=True)
])
```

---

### 3. Load the JSON data

If the JSON is stored in a file you would call `spark.read.json("path", schema=schema)`.
For the purpose of this exercise we can create the DataFrame directly from a Python list that mimics the JSON rows.

```python
data = [
(101, "Alice", ("New York", "NY", "10001")),
(102, "Bob", ("Seattle", "WA", "98101"))
]

df = spark.createDataFrame(data, schema)
```

At this point `df.printSchema()` would show:

---

### 4. Flatten the nested `address` struct

In Spark you can reference a field inside a struct using **dot notation**.
Select the top‑level columns you want (`employee_id`, `name`) and then the three inner fields (`address.city`, `address.state`, `address.zip`). Spark automatically lifts those fields to the top level of the resulting DataFrame.

```python
flattened = df.select(
"employee_id",
"name",
"address.city",
"address.state",
"address.zip"
)
```

If you prefer the columns to have exactly the names `city`, `state`, `zip` (without the `address.` prefix), you can rename them while selecting:

```python
flattened = df.select(
"employee_id",
"name",
df["address.city"].alias("city"),
df["address.state"].alias("state"),
df["address.zip"].alias("zip")
)
```

---

### 5. Verify the result

```python
flattened.show()
```

Output:

```
+-----------+------+----------+-----+-----+
|employee_id|name |city |state|zip |
+-----------+------+----------+-----+-----+
|101 |Alice |New York |NY |10001|
|102 |Bob |Seattle |WA |98101|
+-----------+------+----------+-----+-----+
```

The nested `address` object has been successfully flattened into three separate columns.

---

### Why this works and why it’s the best answer

1. **Explicit schema** – Declaring the schema up front tells Spark exactly what to expect, avoids costly schema inference, and makes the code self‑documenting.
2. **Dot notation** – Spark’s column expression language treats a struct like a mini‑record. Using `"address.city"` (or `df["address.city"]`) extracts the inner field without any extra transformations. This is the most concise and performant way to flatten a struct.
3. **No unnecessary shuffling** – The operation is a simple projection; Spark does not need to read or write data again, so it runs in O(1) time relative to the number of rows.
4. **Scalable** – The same pattern works no matter how large the dataset is, because Spark will apply the projection in parallel across partitions.

---

### Common pitfalls to avoid

| Pitfall | What happens | How to fix it |
|---------|--------------|---------------|
| **Reading JSON without a schema** | Spark infers a `MapType` for `address` instead of a `StructType`. Dot notation will then fail (`cannot resolve 'address.city'`). | Always supply the schema, or use `spark.read.json(..., schema=...)`. |
| **Using `explode`** | `explode` is for arrays, not for structs. Applying it to `address` would produce a single column of structs, not separate fields. | Use dot notation or `col("address.*")` to expand a struct. |
| **Naming conflict** | If the outer level already has a column named `city`, selecting `address.city` will create a duplicate column name and Spark will throw an error. | Rename either the outer column or the extracted field with `.alias("new_name")`. |
| **Null address** | If some rows have `address: null`, the extracted fields will be null as well, which is fine. Just be aware that downstream logic must handle nulls. | No extra code needed; Spark propagates nulls automatically. |

---

### Quick one‑liner alternative (if you already have a DataFrame)

If you already have a DataFrame `df` that contains the nested `address` struct, you can flatten it in a single line:

```python
flattened = df.select("*", "address.*").drop("address")
```

Explanation:

* `"address.*"` expands every field inside the struct as separate columns.
* `drop("address")` removes the original struct column, leaving only the flattened fields.

Both the explicit `select` with aliases and the `address.*` shortcut produce the same final schema.

---

### Takeaway

- Define a schema that mirrors the JSON structure.
- Load the JSON into a DataFrame.
- Use dot notation (`address.city`) or the `address.*` shortcut to pull nested fields up to the top level.
- Optionally rename the columns with `.alias()` to get clean column names.
- The result is a flat table ready for loading into a data warehouse.

Practice this pattern with different nested structures (arrays, deeper structs) and you’ll be comfortable handling any JSON‑to‑warehouse transformation in PySpark. Good luck!