SQL
Extract measurements without guessing their type
BOTIE
Examples, experiments and workspace controls
Connect a tool's inputs to see what it produces. You can try a branch before connecting it to Result.
Try your own inputs
Use a copy of the example to try your own data. Keep the problem's assumptions in mind, and predict the effect of a change before checking the output. You can also add another branch to compare two ways of doing the calculation.
Context for this challenge
Read the top-level temperature only from valid JSON numeric properties. Produce NULL for invalid text, missing properties, JSON null, strings or booleans.
A sensor import now mixes numeric readings with strings and malformed messages.
Inspect the component calculation
Read a numeric JSON property( JSON records: JSON payload records JSON text field: payload Top-level property: temperature Numeric field: temperature )
Examples and results
- JSON payload records
[ { "record_id": 1, "payload": "{\"temperature\":42}" }, { "record_id": 2, "payload": "{\"temperature\":\"42\"}" }, { "record_id": 3, "payload": "{}" }, { "record_id": 4, "payload": "broken" }, { "record_id": 5, "payload": "{\"temperature\":true}" } ]- Required result
[ { "record_id": 1, "payload": "{\"temperature\":42}", "temperature": 42 }, { "record_id": 2, "payload": "{\"temperature\":\"42\"}", "temperature": null }, { "record_id": 3, "payload": "{}", "temperature": null }, { "record_id": 4, "payload": "broken", "temperature": null }, { "record_id": 5, "payload": "{\"temperature\":true}", "temperature": null } ]
- JSON payload records
[ { "record_id": 4, "payload": "{\"temperature\":null}" }, { "record_id": 1, "payload": "[1,2]" } ]- Required result
[ { "record_id": 4, "payload": "{\"temperature\":null}", "temperature": null }, { "record_id": 1, "payload": "[1,2]", "temperature": null } ]
Try a fresh construction
This clears this draft, including its undo history. Earlier successful attempts stay recorded. Build again without opening hints to record an independent attempt.
Stuck? Inspect a worked construction
The tools below form one possible solution. You can assemble it, follow its signals, and undo that change to return to your own construction.
Assumptions and test precision
The workshop uses finite JavaScript numbers and top-level JSON properties. Units, physical ranges and duplicate-key policy require separate ingestion validation. Numerical tests allow a difference of 0.001. Lists and structures must also have the right shape.
Your last tested pieces can start the full construction. An existing draft there keeps its own pieces.
A mixed new batch contains a real zero and several lookalikes
This check uses the pieces from “Retain known numeric extractions”.
BOTIE
- JSON payload records
[ { "record_id": 12, "payload": "{\"temperature\":5.5}" }, { "record_id": 10, "payload": "{\"temperature\":0}" }, { "record_id": 11, "payload": "{\"temperature\":false}" }, { "record_id": 13, "payload": "{\"temperature\":\"5.5\"}" }, { "record_id": 14, "payload": "[5.5]" }, { "record_id": 15, "payload": "{" } ]
Use a number, text, or an array matching the requested result. For a matrix, put each row in brackets. Saving your prediction runs your current construction on these inputs and checks the earlier examples too.
Build a construction in the workspace above first. This case will test those pieces.
Which checks would follow type extraction before a numeric value could be accepted as a physical temperature?
Validity, type and physical plausibility are separate boundaries. Keep rejection evidence instead of manufacturing zero readings.
The workshop uses finite JavaScript numbers and top-level JSON properties. Units, physical ranges and duplicate-key policy require separate ingestion validation.
Work through the SQL challenges →