Working with JSON and data Structures in Azure Monitor log queries

Note

You should complete Get started with Azure Monitor Log Analytics and Getting started with Azure Monitor log queries before completing this lesson.

Note

You can work through this exercise in your own Log Analytics environment, or you can use our Demo environment, which includes plenty of sample data.

Nested objects are objects that contain other objects in an array or a map of key-value pairs. These objects are represented as JSON strings. This article describes how JSON is used to retrieve data and analyze nested objects.

Working with JSON strings

Use extractjson to access a specific JSON element in a known path. This function requires a path expression that uses the following conventions.

  • $ to refer to the root folder
  • Use the bracket or dot notation to refer to indexes and elements as illustrated in the following examples.

Use brackets for indexes and dots to separate elements:

let hosts_report='{"hosts": [{"location":"North_DC", "status":"running", "rate":5},{"location":"South_DC", "status":"stopped", "rate":3}]}';
print hosts_report
| extend status = extractjson("$.hosts[0].status", hosts_report)

This is the same result using only the brackets notation:

let hosts_report='{"hosts": [{"location":"North_DC", "status":"running", "rate":5},{"location":"South_DC", "status":"stopped", "rate":3}]}';
print hosts_report 
| extend status = extractjson("$['hosts'][0]['status']", hosts_report)

If there is only one element, you can use only the dot notation:

let hosts_report='{"location":"North_DC", "status":"running", "rate":5}';
print hosts_report 
| extend status = hosts_report.status

Working with objects

parsejson

To access multiple elements in your json structure, it's easier to access it as a dynamic object. Use parsejson to cast text data to a dynamic object. Once converted to a dynamic type, additional functions can be used to analyze the data.

let hosts_object = parsejson('{"hosts": [{"location":"North_DC", "status":"running", "rate":5},{"location":"South_DC", "status":"stopped", "rate":3}]}');
print hosts_object 
| extend status0=hosts_object.hosts[0].status, rate1=hosts_object.hosts[1].rate

arraylength

Use arraylength to count the number of elements in an array:

let hosts_object = parsejson('{"hosts": [{"location":"North_DC", "status":"running", "rate":5},{"location":"South_DC", "status":"stopped", "rate":3}]}');
print hosts_object 
| extend hosts_num=arraylength(hosts_object.hosts)

mvexpand

Use mvexpand to break the properties of an object into separate rows.

let hosts_object = parsejson('{"hosts": [{"location":"North_DC", "status":"running", "rate":5},{"location":"South_DC", "status":"stopped", "rate":3}]}');
print hosts_object 
| mvexpand hosts_object.hosts[0]

mvexpand

buildschema

Use buildschema to get the schema that admits all values of an object:

let hosts_object = parsejson('{"hosts": [{"location":"North_DC", "status":"running", "rate":5},{"location":"South_DC", "status":"stopped", "rate":3}]}');
print hosts_object 
| summarize buildschema(hosts_object)

The output is a schema in JSON format:

{
    "hosts":
    {
        "indexer":
        {
            "location": "string",
            "rate": "int",
            "status": "string"
        }
    }
}

This output describes the names of the object fields and their matching data types.

Nested objects may have different schemas such as in the following example:

let hosts_object = parsejson('{"hosts": [{"location":"North_DC", "status":"running", "rate":5},{"status":"stopped", "rate":"3", "range":100}]}');
print hosts_object 
| summarize buildschema(hosts_object)

Build schema

Next steps

See other lessons for using log queries in Azure Monitor: