Azure Log Analytics parse json

Question

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.

Slavik N Slavik N · Accepted Answer · 2020-09-21T06:55:39

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   |
|------|

Azure Log Analytics parse json

2 Answers