Flatten Nested JSON to CSV

HardPython

Problem

Quest Global data pipelines receive JSON records with nested objects and arrays. Implement a function that flattens the records into CSV-ready rows, using dot notation for nested object keys and indexed notation for array elements.

Formal Specification

Implement flatten_json(data). The input is either a JSON object represented by a Python dictionary or a JSON array represented by a list of dictionaries. Each top-level dictionary becomes one output row. Nested dictionary keys must use dot notation, such as asset.id. Array elements must use zero-based indexed keys, such as measurements[0]. Scalar values remain unchanged. Empty objects and arrays should produce no additional fields.

Return a list of dictionaries. The output rows must contain every flattened key found in any input record. Missing values must be represented by None, and keys in each row must appear in deterministic alphabetical order when iterated.

A caller can write the returned rows with Python's csv.DictWriter, using the union of all output keys as the CSV header.

Constraints

1 <= number of records <= 10^4
Maximum nesting depth is 50
Keys are non-empty strings without dots or brackets
Values are valid JSON values

Function Signature

def flatten_json(data):

Formal Specification

A caller can write the returned rows with Python's csv.DictWriter, using the union of all output keys as the CSV header.