Question

A data engineer loads deeply nested JSON documents into a VARIANT column. Each document contains an 'employees' array, and each employee element itself contains a nested 'projects' array of objects. The engineer needs a single query that returns one row per project, with columns for the employee name and the project name. Which approach correctly produces this result?

Accepted Answer

Use LATERAL FLATTEN on the 'employees' array, then chain a second LATERAL FLATTEN on the 'projects' array of each flattened employee record

Answer

Use a single FLATTEN with RECURSIVE => TRUE to automatically expand every level of nesting into individual rows

Answer

Use FLATTEN on the 'employees' array only, then apply PARSE_JSON to the projects column to split it into rows

Answer

Use two separate queries joined by UNION ALL, one flattening employees and one flattening projects