CODEWITHSUNDEEP
azureazure-log-analyticskql
Manjunath P N
Manjunath P N

Reputation: 13

Azure Log Analytics parse json

I have a query which results in a few columns but one of the columns, I am parsing JSON to retrieve the object value but there are multiple entries in it I want each entry in JSON to retrieve in a loop and display.

Below is the query,

let forEach_table = AzureDiagnostics
| where Parameters_LOAD_GROUP_s contains 'LOAD(AUTO)';
let ParentPlId = '';
let ParentPlName = '';
let commonKey = '';
forEach_table
| where Category == 'PipelineRuns'
| extend pplId = parse_json(Predecessors_s)[0].PipelineRunId, pplName = parse_json(Predecessors_s)[0].PipelineName
| extend dbMapName = tostring(parse_json(Parameters_getMetadataList_s)[0].dbMapName)
| summarize count(runId_g) by Resource, Status = status_s, Name=pipelineName_s, Loadgroup = Parameters_LOAD_GROUP_s, dbMapName, Parameters_LOAD_GROUP_s, Parameters_getMetadataList_s, pipelineName_s, Category, CorrelationId, start_t, end_t, TimeGenerated
| project ParentPL_ID = ParentPlId, ParentPL_Name = ParentPlName, LoadGroup_Name = Loadgroup, Map_Name = dbMapName, Status,Metadata = Parameters_getMetadataList_s, Category,  CorrelationId, start_t, end_t
| project-away ParentPL_ID, ParentPL_Name, Category, CorrelationId

here in the above code,

extend dbMapName = tostring(parse_json(Parameters_getMetadataList_s)[0].dbMapName)

I am retrieving 0th element as default but I would like to retrieve all elements in sequence can somebody suggest me how to achieve this.

Upvotes: 0

Views: 2477

Answers (2)

Manjunath P N
Manjunath P N

Reputation: 13

extend and mv-expand methods help in resolving this kind of scenario.

Solution: extend rows = parse_json(Parameters_getMetadataList_s) | mv-expand rows | project Parameters_LOAD_GROUP_s,rows

Upvotes: 0

Slavik N
Slavik N

Reputation: 5328

bag_keys() is just what you need.

For example, take a look at this query:

datatable(myjson: dynamic) [
   dynamic({"a": 123, "b": 234, "c": 345}),
   dynamic({"dd": 123, "ee": 234, "ff": 345})
]
| project keys = bag_keys(myjson)

Its output is:

|---------|
| keys    |
|---------|
| [       |
|   "a",  |
|   "b",  |
|   "c"   |
| ]       |
|---------|
| [       |
|   "dd", |
|   "ee", |
|   "ff"  |
| ]       |
|---------|

If you want to have every key in a separate row, use mv-expand, like this:

datatable(myjson: dynamic) [
   dynamic({"a": 123, "b": 234, "c": 345}),
   dynamic({"dd": 123, "ee": 234, "ff": 345})
]
| project keys = bag_keys(myjson)
| mv-expand keys

The output of this query will be:

|------|
| keys |
|------|
| a    |
| b    |
| c    |
| dd   |
| ee   |
| ff   |
|------|

Upvotes: 0

Related Questions