Search code examples
azure-data-explorer

Kusto query loop over json array


I want to loop over a JSON array like this:

[
  {
    "id": 1,
    "count" : 30
  },
  {
    "id": 2,
    "count" : 10
  },
  {
    "id": 3,
    "count" : 5
  },
  {
    "id": 4,
    "count" : 15
  }
]

So I would like to have a query to project a TotalCount which would basically go over the json array and sum all the count values(30+10+5+15) and display as a new column


Solution

  • You can use mv-apply to do so.

    For example:

    datatable(d: dynamic) [
        dynamic([
        {
            "id": 1,
            "count": 30
            },
            {
            "id": 2,
            "count": 10
            },
            {
            "id": 3,
            "count": 5
            },
            {
            "id": 4,
            "count": 15
            }
        ]),
        dynamic([
            {
            "id": 1,
            "count": 3
            },
            {
            "id": 2,
            "count": 1
            },
            {
            "id": 3,
            "count": 50
            },
            {
            "id": 4,
            "count": 1
            }
        ]),
    ]
    | mv-apply d on (
        summarize result = sum(tolong(d['count']))
    )
    
    result
    60
    55