Parse Incoming Data Into JSON | Dataford Interview Questions

Parse this incoming data to json

Use PostgreSQL to convert each non-null incoming record into a JSON document. Each payload uses the format name=...;department=...;active=...;hours=.... Include records belonging to open batches only. Invalid boolean or integer values should become JSON null, while valid values should retain their appropriate JSON types.

Output

One row per eligible incoming record
Columns: record_id, batch_name, and parsed_json
Order by batch_id, then record_id ascending

Schema

incoming_batches

Column	Type	Description
batch_idPK	INT	Identifier for the incoming batch
batch_name	VARCHAR(100)	Human-readable batch name
is_open	BOOLEAN	Whether records from the batch are eligible for parsing

incoming_records

Column	Type	Description
record_idPK	INT	Identifier for the incoming record
batch_id	INT	Batch containing the incoming record
raw_payload	TEXT	Semicolon-delimited incoming data

Your question is Parse Incoming Data Into JSON. 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