An MQTT message is only a topic plus a payload. A useful historian record needs more: stable equipment identity, typed measurements, a trustworthy time, and a schema that stays meaningful as devices and sites grow.
The central design decision is not “How do I parse JSON?” It is “Which parts describe the measurement, and which parts describe the source that produced it?” Make that decision before configuring an ingestion tool. Otherwise a technically successful write can create tables that are difficult to query, compare, or maintain.
Separate identity from measurements
Industrial MQTT topics often carry stable context. A topic such as plant-a/line-3/press-17/telemetry identifies a site, production line, asset, and message class. The JSON body should normally carry values that change over time, such as pressure, temperature, quality, and an event timestamp.
Use the topic for identity only when its levels are governed and stable. If a device identifier can change position in a topic, or if a topic level mixes identity with a changing state, extract the durable identity from a payload field or an asset registry instead. The goal is a single, understandable source identity that remains consistent across records.
Here is an illustrative payload, not connector configuration:
{
"event_time": "2026-10-27T08:15:04.125Z",
"asset_id": "press-17",
"pressure_kpa": 742.6,
"temperature_c": 46.1,
"quality": "good"
}
For this message, press-17 is an identity candidate, the two numeric values are measurements, quality is contextual state, and event_time needs timestamp validation. The topic may reinforce the identity, but it should not contradict the payload without an explicit resolution rule.
Define the destination model before mapping fields
Write a field-to-destination contract that every engineer can inspect:
| Incoming value | Historian role | Validation question |
|---|---|---|
Topic levels or asset_id | Table or source identity; possibly tag values | Is the identity stable, normalized, and authorized? |
event_time | Record timestamp | Is the format, precision, timezone, and clock source known? |
pressure_kpa, temperature_c | Typed measurement columns | Are type and engineering unit fixed for this measurement? |
quality | Status column or defined contextual field | Is it a controlled set of values with documented meaning? |
For TDengine, a supertable defines a shared structure; its subtables represent individual sources and carry the supertable’s tags. The model needs a primary timestamp and at least one measurement column. That fits many equipment-telemetry patterns: use a stable source identity for the subtable, keep changing values as ordinary columns, and reserve tags for descriptive values shared by every row from that source. Do not put a rapidly changing measurement such as pressure into a tag merely because it appears beside the asset identifier in JSON.
Choose the table grain deliberately. A table per physical asset is often understandable for regularly sampled telemetry. When a payload contains readings from multiple assets, preserve each source’s identity and map the measurements to the appropriate subtables. Separate supertables may be appropriate when device types or message classes require different measurement schemas. Whatever you choose, avoid allowing arbitrary incoming field names to create an uncontrolled schema. That creates inconsistent columns and makes fleet-level queries unreliable.
Parse JSON with explicit types, units, and null rules
JSON has fewer type constraints than a historian. A value that looks numeric can arrive as a number, a quoted string, null, or a different unit after a firmware change. Set an expected type and unit for every measurement before ingestion:
- Parse numeric measurements as the agreed numeric type; reject or quarantine non-numeric values instead of silently converting them.
- Keep the engineering unit in the schema contract or a controlled asset attribute. Do not assume a field named
pressureis always kPa. - Define whether
nullmeans unavailable, unchanged, intentionally omitted, or malformed. The answer determines whether to store a null, skip the field, or send the message for review. - Extract nested JSON values with named paths and validate the resulting field, rather than treating an entire nested object as a measurement.
Arrays need special care. An array can represent a batch of timestamped readings, a set of channels, or an unordered list. Map each case differently. Split a true batch into records only when every element has a clear identity and timestamp; otherwise archive or transform it through a controlled process.
Make source time and ingestion time distinct
Use source event time for the historical position of a measurement when the source can provide a valid timestamp. Validate its timezone, epoch unit or text format, allowed clock skew, and behavior after a device reboot. This matters especially after buffering or replay: assigning the database arrival time makes an old process condition appear current.
Ingestion time is still valuable. It can reveal delay, backlog recovery, or a gateway clock issue. Store or observe it separately when the design calls for it, rather than replacing the source time by default. If a payload has no trustworthy event time, document that the historian uses arrival time and that delayed data cannot be reconstructed precisely.
Apply tool mapping only after the contract is clear
TDengine TSDB Enterprise’s MQTT ingestion documentation describes topic analysis, a JSON extractor, and mappings for table names, supertable names, ordinary columns, and tag columns. It also provides mapping previews. Those features can implement a reviewed contract; they do not decide the contract for you. Configure and inspect sample messages that include normal values, a missing optional field, a malformed value, and a nested structure.
Avoid copying connector expressions from an example without checking the deployed version. Mapping behavior, template use, and any automatic schema evolution have operational consequences. Start with a bounded, explicit schema and use preview results to confirm each source field lands in the intended destination role. For a product overview of time-series storage and queries, see TDengine TSDB.
Verify with queries, not just a successful task state
Publish a small controlled set of messages for two distinct assets. Include a known timestamp, an expected unit, one null or missing optional field, and a value that should be rejected. Then verify:
- Each asset resolves to the intended table or source identity.
- Tags remain stable while measurement columns vary over time.
- Query results use the supplied event time and preserve the expected precision.
- Types, null handling, and quality values match the written mapping contract.
- A schema or parsing error is visible in the configured exception path rather than silently becoming a misleading record.
Repeat the check after a producer software update or a new asset type is introduced. The goal is not merely that JSON reaches a database. It is that every stored record remains attributable, correctly timed, typed, and usable in historical analysis.
