Need a KQL query to parse the given json according to the table structure given .

Question

Dear Team,

I am seeking your expertise to flatten a JSON file and store its contents in the specified table structure within Azure Data Explorer (ADX). Additionally, I require this transformation to be encapsulated as a function for reusability and efficiency.

{"modelNo":"","category":"","productSerialNo":"VIN-17107","sequenceNumber":971354452,"metadata":{"unit":{"sensor1":"℃","sensor2":"℃","sensor3":"V","sensor4":"V","sensor5":"V"}},"value":{"0":{"productionDate":"3/18/2024 12:00:00 AM","productionTime":"12.04:51:25","shift":1,"dateTime":"2024-03-18T10:52:36.9252124Z","sensorValues":{"DateTime":null,"String":null,"Double":null,"Integer":{"sensor1":268,"sensor2":228,"sensor3":211,"sensor4":229,"sensor5":237},"Bool":null}},"1":{"productionDate":"3/18/2024 12:00:00 AM","productionTime":"12.04:51:25","shift":1,"dateTime":"2024-03-18T10:53:36.9252124Z","sensorValues":{"DateTime":null,"String":null,"Double":null,"Integer":{"sensor1":255,"sensor2":264,"sensor3":200,"sensor4":297,"sensor5":222},"Bool":null}},"2":{"productionDate":"3/18/2024 12:00:00 AM","productionTime":"12.04:51:25","shift":2,"dateTime":"2024-03-18T10:53:36.9252124Z","sensorValues":{"DateTime":null,"String":null,"Double":null,"Integer":{"sensor1":292,"sensor2":255,"sensor3":210,"sensor4":240,"sensor5":232},"Bool":null}},"3":{"productionDate":"3/18/2024 12:00:00 AM","productionTime":"12.04:51:25","shift":1,"dateTime":"2024-03-18T10:53:36.9252124Z","sensorValues":{"DateTime":null,"String":null,"Double":null,"Integer":{"sensor1":232,"sensor2":232,"sensor3":207,"sensor4":238,"sensor5":273},"Bool":null}},"4":{"productionDate":"3/18/2024 12:00:00 AM","productionTime":"12.04:51:25","shift":2,"dateTime":"2024-03-18T10:53:36.9252124Z","sensorValues":{"DateTime":null,"String":null,"Double":null,"Integer":{"sensor1":285,"sensor2":241,"sensor3":259,"sensor4":220,"sensor5":269},"Bool":null}}}

Table Structure:

Could you please assist me in crafting a Kusto Query Language (KQL) query tailored to the provided JSON structure and normalized table schema? Your help in beautifying this process would be greatly appreciated.

Thank you for your support and expertise.

Answer 1

Hello @SaravananGanesan-3177,

Finding a solution for this question, we first need to create a table containing the source data:

.create table testinput
(
    data: dynamic  
)

.create table testinput ingestion json mapping "JsonMapping"
'['
'    { "column" : "data", "datatype" : "dynamic", "Properties":{"Path":"$"}}'
']'

.ingest inline into table testinput with (format = "json", ingestionMappingReference = "JsonMapping") <|
{"modelNo":"","category":"","productSerialNo":"VIN-17107","sequenceNumber":971354452,"metadata":{"unit":{"sensor1":"℃","sensor2":"℃","sensor3":"V","sensor4":"V","sensor5":"V"}},"value":{"0":{"productionDate":"3/18/2024 12:00:00 AM","productionTime":"12.04:51:25","shift":1,"dateTime":"2024-03-18T10:52:36.9252124Z","sensorValues":{"DateTime":null,"String":null,"Double":null,"Integer":{"sensor1":268,"sensor2":228,"sensor3":211,"sensor4":229,"sensor5":237},"Bool":null}},"1":{"productionDate":"3/18/2024 12:00:00 AM","productionTime":"12.04:51:25","shift":1,"dateTime":"2024-03-18T10:53:36.9252124Z","sensorValues":{"DateTime":null,"String":null,"Double":null,"Integer":{"sensor1":255,"sensor2":264,"sensor3":200,"sensor4":297,"sensor5":222},"Bool":null}},"2":{"productionDate":"3/18/2024 12:00:00 AM","productionTime":"12.04:51:25","shift":2,"dateTime":"2024-03-18T10:53:36.9252124Z","sensorValues":{"DateTime":null,"String":null,"Double":null,"Integer":{"sensor1":292,"sensor2":255,"sensor3":210,"sensor4":240,"sensor5":232},"Bool":null}},"3":{"productionDate":"3/18/2024 12:00:00 AM","productionTime":"12.04:51:25","shift":1,"dateTime":"2024-03-18T10:53:36.9252124Z","sensorValues":{"DateTime":null,"String":null,"Double":null,"Integer":{"sensor1":232,"sensor2":232,"sensor3":207,"sensor4":238,"sensor5":273},"Bool":null}},"4":{"productionDate":"3/18/2024 12:00:00 AM","productionTime":"12.04:51:25","shift":2,"dateTime":"2024-03-18T10:53:36.9252124Z","sensorValues":{"DateTime":null,"String":null,"Double":null,"Integer":{"sensor1":285,"sensor2":241,"sensor3":259,"sensor4":220,"sensor5":269},"Bool":null}}}}

testinput

Notice I needed to add an extra '}' to make the JSON valid.

This results in one table with one dynamic column with one row:

From there, I created this query:

testinput
| extend modelNo = tostring(data.modelNo)
| extend category = tostring(data.category)
| extend productSerialNo = tostring(data.productSerialNo)
| extend sequenceNumber = toint(data.sequenceNumber)
| extend Unit = bag_pack("sensor1", tostring(data.metadata.unit.sensor1), "sensor2", tostring(data.metadata.unit.sensor2), "sensor3", tostring(data.metadata.unit.sensor3) , "sensor4", tostring(data.metadata.unit.sensor4), "sensor5", tostring(data.metadata.unit.sensor5))
| mv-expand values = data.value 
| extend a = tostring(extract_json("$.['0']", dynamic_to_json(values) , typeof(dynamic)))
| extend b = tostring(extract_json("$.['1']", dynamic_to_json(values) , typeof(dynamic))) 
| extend c = tostring(extract_json("$.['2']", dynamic_to_json(values) , typeof(dynamic)))
| extend d = tostring(extract_json("$.['3']", dynamic_to_json(values) , typeof(dynamic))) 
| extend e = tostring(extract_json("$.['4']", dynamic_to_json(values) , typeof(dynamic)))
| extend value = todynamic(strcat(a, b, c, d, e))
| project-away data, values, a, b, c, d, e
| extend productionDate = value.productionDate
| extend productionTime = totimespan(value.productionTime)
| extend shift = toint(value.shift)
| extend sensorValuesInteger = dynamic_to_json(value.sensorValues.Integer)
| extend sensorValuesBool = dynamic_to_json(value.sensorValues.Bool)
| extend sensorValuesString = dynamic_to_json(value.sensorValues.String)
| extend sensorValuesDateTime = dynamic_to_json(value.sensorValues.DateTime)
| extend sensorValuesDouble = dynamic_to_json(value.sensorValues.Double)
| project-away value

This results in a table that resembles a lot of what you were asking:

See how bag_pack is used to construct a new json message based on 5 sensor units.

With mv_expand together with the extend/extract_json, the five values are split into five columns. With strcat the five columns are merged to one because every time, four columns are empty.

After that, getting access to the value columns is easy.

Please check out how it works and try to optimize it. Perhaps you need to check the column types too.

---

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar issues will benefit by doing so. Your contribution is highly appreciated.

Answer 2

Here you go:

// Define a function to parse and flatten the JSON
.create function FlattenJson(json: dynamic) {
    json
    | extend modelNo = tostring(json.modelNo),
             category = tostring(json.category),
             productSerialNo = tostring(json.productSerialNo),
             sequenceNumber = toint(json.sequenceNumber),
             values = toscalar(json.value)
    | mv-expand value = values
    | extend productionDate = todatetime(value.productionDate),
             productionTime = value.productionTime,
             shift = toint(value.shift),
             dateTime = todatetime(value.dateTime),
             sensor1 = toint(value.sensorValues.Integer.sensor1),
             sensor2 = toint(value.sensorValues.Integer.sensor2),
             sensor3 = toint(value.sensorValues.Integer.sensor3),
             sensor4 = toint(value.sensorValues.Integer.sensor4),
             sensor5 = toint(value.sensorValues.Integer.sensor5)
    | project-away value
}

// Call the function with your JSON data
let jsonInput = dynamic('{"modelNo":"","category":"","productSerialNo":"VIN-17107","sequenceNumber":971354452,"metadata":{"unit":{"sensor1":"℃","sensor2":"℃","sensor3":"V","sensor4":"V","sensor5":"V"}},"value":{"0":{"productionDate":"3/18/2024 12:00:00 AM","productionTime":"12.04:51:25","shift":1,"dateTime":"2024-03-18T10:52:36.9252124Z","sensorValues":{"DateTime":null,"String":null,"Double":null,"Integer":{"sensor1":268,"sensor2":228,"sensor3":211,"sensor4":229,"sensor5":237},"Bool":null}},"1":{"productionDate":"3/18/2024 12:00:00 AM","productionTime":"12.04:51:25","shift":1,"dateTime":"2024-03-18T10:53:36.9252124Z","sensorValues":{"DateTime":null,"String":null,"Double":null,"Integer":{"sensor1":255,"sensor2":264,"sensor3":200,"sensor4":297,"sensor5":222},"Bool":null}},"2":{"productionDate":"3/18/2024 12:00:00 AM","productionTime":"12.04:51:25","shift":2,"dateTime":"2024-03-18T10:53:36.9252124Z","sensorValues":{"DateTime":null,"String":null,"Double":null,"Integer":{"sensor1":292,"sensor2":255,"sensor3":210,"sensor4":240,"sensor5":232},"Bool":null}},"3":{"productionDate":"3/18/2024 12:00:00 AM","productionTime":"12.04:51:25","shift":1,"dateTime":"2024-03-18T10:53:36.9252124Z","sensorValues":{"DateTime":null,"String":null,"Double":null,"Integer":{"sensor1":232,"sensor2":232,"sensor3":207,"sensor4":238,"sensor5":273},"Bool":null}},"4":{"productionDate":"3/18/2024 12:00:00 AM","productionTime":"12.04:51:25","shift":2,"dateTime":"2024-03-18T10:53:36.9252124Z","sensorValues":{"DateTime":null,"String":null,"Double":null,"Integer":{"sensor1":285,"sensor2":241,"sensor3":259,"sensor4":220,"sensor5":269},"Bool":null}}}');
FlattenJson(jsonInput)
| into MyTable

---

hth

Marcin

Answer 3

Hi Marcin ,

Thank you for your quick response , but i am getting the below error when i am trying to execute the Flattened Json function . Can you please help me in this .

Error : Operator source expression should be table or column