Using KQL for JSON Operations in Flow

When it comes to functions for processing JSON data there is not a whole bunch of expressions available in Flow. I would have expected some function for JSONPath, but alas it is not there. So, this means lot of times you have to deal with JSON to XML and XPath gymnastics. In this post, I will demonstrate how you can use the Azure Data Explorer connector to offload these JSON processing to Kusto.

Scenario 1:

Assuming you can two JSON arrays you want to only return items in Array1 that have a match in Array 2 for column CountryName.

Array 1

[
  {
    "Id": 1,
    "Title": "Max Power",
    "CountryName": "USA"
  },
  {
    "Id": 1,
    "Title": "John Smith",
    "CountryName": "Australia"
  }
]

Array 2

[
  {
    "CountryName": "Australia",
    "Id": 1
  }
]

You can do this using the following KQL.

let obj1 = view () { print o = dynamic([JSON1])};
let obj2 = view () { print o = dynamic([JSON2])};
obj1
| mv-expand  o
| evaluate bag_unpack(o)
| join kind=inner (obj2
| mv-expand  o
| evaluate bag_unpack(o)) on $left.CountryName==$right.CountryName
| project-away CountryName1, Id1

When it comes to running this in Flow, you can use the Run KQL query action and point it to the help KQL cluster which is Microsoft’s free Kusto cluster. If you have an ADX Cluster on your tenant you can use that as well.

You can see that the KQL query returns the right JSON response for the JSON in Array1 and Array2.

Scenario 2:

Assuming you have the JSON below

[
  {
    "name": "A",
    "index": 1
  },
  {
    "name": "B",
    "index": 2
  },
  {
    "name": "C",
    "index": 3
  }
]

If you want to return an array with only names, there are two ways to do it.

Using JSONPath

let obj1 = view () { print o = dynamic([JSON]) };
obj1 
| mv-expand o
| extend name = extract_json("$.name", tostring(o), typeof(string))
| summarize names = make_list(name)

Without using JSONPath

let obj1 = view () { print o = dynamic([JSON]) };
obj1 
| mv-expand o
| evaluate bag_unpack(o)
| summarize names = make_list(name)

Both produce the same result.

JSONPath implemented by ADX is only a subset of JSONPath spec. So, not everything works.

ADX is so powerful, so it does feel like an overkill to use KQL just for JSON processing, but IMHO this feels so much simpler and elegant than loops and XPath.

There are whole bunch of KQL functions that you can use (see https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/), so you can use much more complex JSON processing using KQL.

If you are interested in learning KQL in a fun way, you can also participate in the Kusto Detective contest. Watch https://www.youtube.com/watch?v=ayV1r2vFYnI if you want get a small hint and start with the challenge.

Reference:

https://learn.microsoft.com/en-us/training/paths/data-analysis-data-explorer-kusto-query-language/

https://detective.kusto.io/