Flatten nested JSON dynamically

PySpark data engineering interview problem. Difficulty: advanced. Pattern: Schema Drift. About 22 minutes. Part of the Pro drill bank.

Data from JSON files or APIs is often nested: a field can hold another object (a struct), or a list of objects (an array of structs). Tables and BI tools need flat columns. The hard part is that the nested shape can change, so a script that hard-codes the field names will break the day a new field appears.

The table orders was read from JSON. It has id, a struct customer (with name and a nested struct address that has city and zip), and an array of structs items (each with sku and qty). Produce one flat table. Every field inside a struct becomes a top-level column named parent_child, for example customer_name. Nested structs follow the same rule, so you get customer_address_city. Every array becomes rows, 1 row for each element. An order whose array is empty must still appear, with NULL in the element columns. The code must work from the schema of the DataFrame (frame.schema.fields). Do not write the names customer, address or items in your code. Sort by id. The editor creates orders and ends with result = orders. Change the last line.

Requirements

• Turn struct fields into columns named parent_child, also for nested structs.
• Turn each array into rows with explode_outer, so empty arrays keep the order.
• Read the column names and types from orders.schema, with no hard-coded field names.
• Sort by id and store the DataFrame in result.

Constraints

• Repeat the flattening until no struct or array columns are left. Nested levels need several rounds.
• Use explode_outer, not explode, so rows with empty arrays are not lost.
• Check the type of each field with isinstance(field.dataType, StructType) or ArrayType.

Examples

Input: raw = spark.sparkContext.parallelize([ '{"id": 1, "customer": {"name": "Ann", "address": {"city": "Pune", "zip": "411001"}}, "items": [{"sku": "A", "qty": 2}, {"sku": "B", "qty": 1}]}', '{"id": 2, "customer": {"name": "Bo", "address": {"city": "Oslo", "zip": "0150"}}, "items": []}', '{"id": 3, "customer": {"name": "Cy", "address": {"city": "Rome", "zip": null}}, "items": [{"sku": "C", "qty": 5}]}', ]) orders = spark.read.json(raw) Output: id | customer_name | items_sku | items_qty | customer_address_city | customer_address_zip 1 | Ann | A | 2 | Pune | 411001 1 | Ann | B | 1 | Pune | 411001 2 | Bo | NULL | NULL | Oslo | 0150 3 | Cy | C | 5 | Rome | NULL Order 1 has 2 items so it produces 2 rows. Order 2 has an empty items array and is kept with NULL sku and qty. The nested address fields become customer_address_city and customer_address_zip.

Topics: lakebench, pyspark, json, struct, explode_outer, schema.

More PySpark interview questions · All interview problems · Learn data engineering