Infer JSON Schema | Dataford Interview Questions

Given an input dataset consisting of JSON records (e.g. [{"a": 1, "b": ["hello", "world"], "c": "foo"}]), return the schema of the dataset (a is int, b is list of string, c is string), handling nested records.

Asked in the tech screen stage. Use the records belonging to dataset ID 1.

Output

One row per scalar or list-valued leaf path, with field_path, schema_type, and is_nullable.
Represent nested paths with dot notation and arrays as list of <type>.
Mark a field nullable when it is missing from any record or contains a JSON null.
Order by field_path ascending.

Schema

datasets

Column	Type	Description
dataset_idPK	INTEGER	Unique dataset identifier
dataset_name	VARCHAR(255)	Dataset name

dataset_records

Column	Type	Description
record_idPK	INTEGER	Unique record identifier
dataset_id	INTEGER	Referenced dataset identifier
payload	JSONB	JSON record payload

Your question is Infer JSON Schema. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem