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

SaravananGanesan-3177 1,660 Reputation points
2024-03-25T02:47:15.0166667+00:00

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:

User's image

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.

Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
479 questions
0 comments No comments
{count} votes

Accepted answer
  1. Sander van de Velde 28,086 Reputation points MVP
    2024-03-26T00:02:20.6466667+00:00

    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:

    enter image description here

    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:

    enter image description here

    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.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Marcin Policht 8,415 Reputation points MVP
    2024-03-25T03:30:56.14+00:00

    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


  2. SaravananGanesan-3177 1,660 Reputation points
    2024-03-25T15:57:29.0233333+00:00

    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

    0 comments No comments