Changing Ingestions from JSON

Hi MoFabricIoT​ 

Thanks for the update. The behaviors you're seeing are consistent with documented Kusto schema and ingestion behavior.

The duplicated columns

.create-merge table extends an existing table schema by adding columns that don't already exist, while retaining existing columns. Kusto identifiers, including column names, are case-sensitive. Therefore, differences in column naming can result in separate identifiers rather than replacing an existing column.

The missing values

When mapped fields are missing from the incoming data, Kusto fills the corresponding value with NULL. This can occur without an ingestion failure, which explains why nothing appeared in .show ingestion failures.

This also allows a single table to accommodate different payloads. If one device sends x and another sends y, the column not present in a particular payload can remain NULL.

Using a raw JSON column:

Your current approach of landing the raw JSON in a payload column can be used as the source for a structured target table. An update policy can transform the JSON during ingestion and write the extracted fields into the typed table.

KQL

.create-merge table SensorData ( timestamp:datetime, deviceId:string, x:real, y:real ) .create-or-alter function ParseSensorPayload() { RawSensorData | extend p = todynamic(payload) | project timestamp = todatetime(p.timestamp), deviceId = tostring(p.deviceId), x = toreal(p.x), y = toreal(p.y) } .alter table SensorData policy update

JSON

[ { "IsEnabled": true, "Source": "RawSensorData", "Query": "ParseSensorPayload()", "IsTransactional": false, "PropagateIngestionProperties": false } ]

The source and target tables must be in the same database, and the update policy function schema and target table schema must match in column names, types, and order.

For the materialized view, build it over SensorData rather than the raw payload table and keep the transformation schema deterministic. Microsoft documents that bag_unpack and pivot can produce a nondeterministic schema, which can cause a materialized view to be automatically disabled.

Since you already have a Microsoft Support case open, the observations about automatic schema generation and duplicated columns should also be included in that investigation.

References:

• .create-merge table command
• Entity names
• How invalid data affects ingestion
• .alter table policy update command
• Materialized views limitations

Thanks,
C Srikanth
Community Support Team