Doing this will allow the data analysts and scientists to run the calculation, aggregation, and other queries more effectively without having to worry about data type conversion at each stage. After the join, we divided the value column into 3 columns having distinct data types Integer, Boolean, and String. Step 7: Join the two tables created in Step 3 (Keys) and Step 6 (Values) to retrieve a complete table containing key-value pairs that can be easily queried. However, in the case of multiple rows, you will get the desired output for each row as shown below. In the case of a single row, these statements generate the same output as Step 5. | project Level3_Id, key_value = kpi_values Step 6: We will use the mv-apply operator to execute some of the statements in step 5 on each row that can be present in the 'kpi_data' JSON object.Įxtend Level3_Id = tostring(bag_keys(kpi_values))
| extend Level3_Id = tostring(bag_keys(kpi_values))
Since GUID key can be different for each value, we will use the bag_keys() function to transform this JSON structure into a column of keys and values. Step 5: In this JSON document, the value (in this case, 18806s) is referenced using a GUID key. Step 4: Use the mv-expand operator again to expand the array inside the 'kpi_data' JSON object The output of executing these statements is: | project-away kpi_structure, new_kpi_structure
| extend Level3_data_type = new_kpi_structure.data_type, Level3_Id = tostring(new_kpi_structure.id), Level3_name = new_kpi_structure.name, Level3_Unit = new_kpi_structure.unit | extend Level2_data_type = kpi_structure.data_type, Level2_Id = kpi_structure.id, Level2_kpi_type = kpi_structure.kpi_type, Level2_name = kpi_structure.name, new_kpi_structure=kpi_structure.kpi_structure We're also using the ' extend' operator to extract columns from the expanded JSON arrays. Step 3: We will apply the mv-expand operator again to expand the next array. Upon executing these KQL statements, we see the following output: | project Timestamp = from, Level1_id = structure.id, Level1_Name=structure.name, Level1_kpi_type=structure.kpi_type, kpi_structure=structure.kpi_structure We will first use the ' project' operator to select the columns of interest and then apply mv-expand on the column containing nested arrays. Step 2: Since there are 2 nested JSON arrays, we will use mv-expand operator to expand these dynamic arrays. However, in the example above, this will not work and hence we will write KQL in step 2. With multi-level JSON, you can extract multiple objects within the JSON document. Step 1: Use ADX Ingestion Hub (also called One Click Ingestion), to upload sample data and let ADX understand the schema of the JSON document. This JSON has keys and values in two different arrays. To convert this JSON document into the relational schema as shown below, we will use the approach of extracting the 'structure' object into one table and the 'kpi_data' object into another table and then join the two tables using the GUID. You can access the JSON file from this GitHub page to try the steps below. In this blog, we're taking an example of a complex JSON file as shown in the screenshot below. Azure Data Explorer provides some useful features to run meaningful, fast, and interactive analytics on such heterogenous data structures and formats. In the world of IoT devices, industrial historians, infrastructure and application logs, and metrics, machine-generated or software-generated telemetry, there are often scenarios where the upstream data producer produces data in non-standard schemas, formats, and structures that often make it difficult to analyze the data contained in these at scale. Author: a Principal Program Manager for Data Explorer in the Synapse Customer Success Engineering (CSE) team.
0 kommentar(er)
