How to parse json array in kusto query language

Question

How to parse json array in kusto query language. I have an output column which is having value in JSON array format as shown below. I Need to parse it to get values in form of two columns.

{"count": 14
"value": [
        {
            "Total_Record_Count": 16608,
            "date": "2021-03-01T00:00:00Z"
        },
        {
            "Total_Record_Count": 27254,
            "date": "2021-02-24T00:00:00Z"
        },
        {
            "Total_Record_Count": 6,
            "date": "2021-02-01T00:00:00Z"
        },
        {
            "Total_Record_Count": 26964,
            "date": "2021-01-15T00:00:00Z"
        },
        {
            "Total_Record_Count": 134516,
            "date": "2020-12-18T00:00:00Z"
        },
        {
            "Total_Record_Count": 27345,
            "date": "2020-12-16T00:00:00Z"
        },
        {
            "Total_Record_Count": 521,
            "date": "2020-12-01T00:00:00Z"
        },
        {
            "Total_Record_Count": 4,
            "date": "2020-11-02T00:00:00Z"
        },
        {
            "Total_Record_Count": 6,
            "date": "2020-10-01T00:00:00Z"
        },
        {
            "Total_Record_Count": 1,
            "date": "2020-09-01T00:00:00Z"
        },
        {
            "Total_Record_Count": 3,
            "date": "2020-08-03T00:00:00Z"
        },
        {
            "Total_Record_Count": 18,
            "date": "2020-07-01T00:00:00Z"
        },
        {
            "Total_Record_Count": 18754,
            "date": "2020-06-16T00:00:00Z"
        },
        {
            "Total_Record_Count": 4451898,
            "date": "2020-06-08T00:00:00Z"
        }
    ]}

How can I achieve it using output column name instead of using full json array.

Alexander Sloutsky · Accepted Answer · 2021-03-10 15:36:01Z

10

Please see example below that uses mv-expand operator for breaking array into rows. https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/mvexpandoperator

print d = dynamic(
{"count": 14,
"value": [
        {
            "Total_Record_Count": 16608,
            "date": "2021-03-01T00:00:00Z"
        },
        {
            "Total_Record_Count": 27254,
            "date": "2021-02-24T00:00:00Z"
        },
        {
            "Total_Record_Count": 6,
            "date": "2021-02-01T00:00:00Z"
        },
        {
            "Total_Record_Count": 26964,
            "date": "2021-01-15T00:00:00Z"
        },
        {
            "Total_Record_Count": 134516,
            "date": "2020-12-18T00:00:00Z"
        },
        {
            "Total_Record_Count": 27345,
            "date": "2020-12-16T00:00:00Z"
        },
        {
            "Total_Record_Count": 521,
            "date": "2020-12-01T00:00:00Z"
        },
        {
            "Total_Record_Count": 4,
            "date": "2020-11-02T00:00:00Z"
        },
        {
            "Total_Record_Count": 6,
            "date": "2020-10-01T00:00:00Z"
        },
        {
            "Total_Record_Count": 1,
            "date": "2020-09-01T00:00:00Z"
        },
        {
            "Total_Record_Count": 3,
            "date": "2020-08-03T00:00:00Z"
        },
        {
            "Total_Record_Count": 18,
            "date": "2020-07-01T00:00:00Z"
        },
        {
            "Total_Record_Count": 18754,
            "date": "2020-06-16T00:00:00Z"
        },
        {
            "Total_Record_Count": 4451898,
            "date": "2020-06-08T00:00:00Z"
        }
    ]})
| project Value = d.['value'] 
| mv-expand Value
| project Count = tolong(Value.['Total_Record_Count']), Date = todatetime(Value.['date'])

Count	Date
4451898	2020-06-08 00:00:00.0000000
18754	2020-06-16 00:00:00.0000000
18	2020-07-01 00:00:00.0000000
3	2020-08-03 00:00:00.0000000
1	2020-09-01 00:00:00.0000000
6	2020-10-01 00:00:00.0000000
4	2020-11-02 00:00:00.0000000
521	2020-12-01 00:00:00.0000000
27345	2020-12-16 00:00:00.0000000
134516	2020-12-18 00:00:00.0000000
26964	2021-01-15 00:00:00.0000000
6	2021-02-01 00:00:00.0000000
27254	2021-02-24 00:00:00.0000000
16608	2021-03-01 00:00:00.0000000

answered Mar 10, 2021 at 15:36

Alexander Sloutsky

3,04712 silver badges15 bronze badges

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

3 Comments

Neha Over a year ago

Thanks Alexander. Its working when I use complete json array in print d = dynamic(pasting json array here). But I am trying to use column name where I am getting this JSON array value as an output. I could not find the way to do that.

Alexander Sloutsky Over a year ago

It should work the same. If the column type is string and not dynamic - you can use parse_json(ColumnName) function to convert it to dynamic column

pixelWorld Over a year ago

How to modify the query if we want to sum all the 'Total_Record_Count'

Collectives™ on Stack Overflow

How to parse json array in kusto query language

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related