Is That Temperature a Number or Just Text?

One sensor sends temperature as the number 42. Another sends the text “42”. A third sends no temperature at all, and an interrupted upload is not valid JSON. Botie must not treat these records as interchangeable just because a person can guess what some of them meant.

JSON lets a row carry a structured payload, but extraction still needs a contract. We will validate the text, inspect the property’s JSON type, then accept only the numeric values this report promises to use.

Read only valid numeric temperature properties, filter the unknown extraction results, and preserve record identity.

[
  {
    "record_id": 1,
    "temperature": 42
  }
]

json_valid asks whether the text follows the supported JSON syntax. A valid array, object or scalar can pass that check while still being the wrong structure for a sensor record.

Our contract expects a top-level temperature property whose JSON type is integer or real. It does not parse units from strings or convert booleans into readings. Production ingestion should also impose size limits, a schema and a policy for duplicate property names.

Syntax validation is the first boundary. It is not proof that the message has a useful meaning.

The path $.temperature addresses the temperature property at the root. json_type reports a type label such as integer, real, text or null; an absent path returns SQL NULL. json_extract returns a corresponding SQL scalar for a single scalar path.

A JSON null property and a missing property can both extract as SQL NULL, so retain the type result when that distinction matters. A boolean can extract numerically, which is why extraction alone is not the temperature check.

Check the JSON type before trusting the extracted SQL value.

SELECT json_type('{"temperature":null}', '$.temperature'),
       json_type('{}', '$.temperature'),
       json_extract('{"temperature":true}', '$.temperature');

Calling JSON functions on malformed text can raise an error. The nested CASE first checks json_valid, then checks the property type, then extracts the accepted value. SQLite evaluates only the required CASE branch.

Do not assume arbitrary AND conditions will always execute left to right to protect a failing expression. Put the protection into an expression with the required evaluation semantics.

The invalid payload follows the unknown extraction route without crashing the whole report.

CREATE TABLE telemetry(record_id INTEGER PRIMARY KEY, payload TEXT);
INSERT INTO telemetry VALUES (1,'{"temperature":42}'),(2,'{"temperature":"42"}'),(3,'{}'),(4,'{"temperature":null}'),(5,'broken'),(6,'{"temperature":true}');

WITH extracted AS (
 SELECT record_id,
   CASE WHEN json_valid(payload) THEN
     CASE WHEN json_type(payload, '$.temperature') IN ('integer','real')
          THEN json_extract(payload, '$.temperature') END
   END AS temperature
 FROM telemetry
)
SELECT record_id, temperature FROM extracted
ORDER BY record_id;

This teaching report omits invalid or unsuitable temperatures. A real pipeline should count and investigate those omissions, preserve a safe reference to the original event and avoid filling them with invented zero readings.

Range and unit checks would follow the type check. A valid numeric value is not necessarily a plausible Celsius measurement, and the same number can mean a different physical quantity under a different unit.

The worksheet tests type extraction. It does not claim to validate a complete sensor contract.

json_each returns one row for each top-level array element or object member. When joined to a source row containing an array, it can multiply that row. Carry the original record ID and the element key or position if you need to identify each result.

Do not sum an original invoice total after expanding its line-item array unless the intended aggregation accounts for that repetition. The same fan-out issue from relational joins appears here.

An array expansion changes the output grain from payload to payload element.

SELECT key, value FROM json_each('["bearing","seal"]') ORDER BY key;

Keeping an original JSON payload can be useful for occasional details. Frequently filtered or joined properties often deserve typed columns, constraints and an indexing strategy that fits the database. Otherwise every query repeats parsing and policy decisions.

Avoid assuming JSON syntax is portable across engines. The functions here use SQLite JSON text support available in the lesson runtime, rather than newer JSONB features or another database’s operators.

The representation should make the contract easier to enforce and the common queries easier to inspect.

Zero and negative numbers are still numbers. Whether they are physically valid is a later domain rule. The extraction should therefore retain zero and negative numeric values while excluding the strings that resemble them.

The workshop makes the typed property extraction a visible component, followed by a NULL filter. Inspect that intermediate result to see which records were rejected before changing the downstream analysis.

A truthiness check would lose a legitimate zero reading. Use the declared type and missing-value policy.