How to project JSON output( array form) into tabular form through kusto query

Question

In my azure monitor log output, data is in array format, I want to use kusto query and fetch that array data in tabular format. Could you please help how to do this, without pasting JSON array manually in KQL.

JSON Output array :

{
    "count": 6,
    "value": [
        {
            "Total_Record_Count": 6,
            "date": "2021-02-01"
        },
        {
            "Total_Record_Count": 4,
            "date": "2020-11-02"
        },
        {
            "Total_Record_Count": 6,
            "date": "2020-10-01"
        },
        {
            "Total_Record_Count": 1,
            "date": "2020-09-01"
        },
        {
            "Total_Record_Count": 3,
            "date": "2020-08-03"
        },
        {
            "Total_Record_Count": 18,
            "date": "2020-07-01"
        }   
    ]
}

I want Kusto query output like :

Total_Record_Count        Date
6                         2021-03-01
4                         2021-02-01
6                         2021-01-01
1                         2020-12-01
3                         2020-11-01
18                        2020-10-01

Slavik N · Accepted Answer · 2021-03-02 20:29:41Z

1

Here you go:

Table
| project d.value
| mv-expand d_value
| project RecordCount = d_value['Total_Record_Count'], Date = d_value['date']

Explanation:

First you have to extract the value by doing YourDynamicColumnName.value, or YourDynamicColumnName['value'] - these are the same.
The next step is to use mv-expand in order to split the array into multiple records (see doc for more info).
As a last step you'll need to extract the two values from every cell, again, by using the following syntax: ColumnName.Key or ColumnName['Key'] (the two are identical).

edited Mar 2, 2021 at 20:29

answered Mar 2, 2021 at 12:46

Slavik N

5,43820 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Neha Over a year ago

Here we need to paste JSON Array every time in the query? Cant we do it in automatic way, mentioning ADFPipelinename and ADFActivityName then parse_json(Output).Total_Record_Count. something like this..

Slavik N Over a year ago

No, you don't need to paste the json. I just put it here as an example. The actual query that you need is the last 4 lines of what I wrote. I'll update the answer by deleting the sample json, in order not to confuse the others.

Neha Over a year ago

I dont have table here.. data is in JSON array format.. I searched and everywhere solution is given: pasting json code and using mv-expand in query..

Slavik N Over a year ago

You wrote "data is in JSON array format" - but where does this data reside? If it's in a table, then you'll indeed need mv-expand, and then use the solution I provided.

Neha Over a year ago

I am fetching data from Azure monitor, In Log analytics using Log management functions like : ADFPipeline, Pipelinename, ADFActivity, ActivittyName, etc.

Collectives™ on Stack Overflow

How to project JSON output( array form) into tabular form through kusto query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related