CODEWITHSUNDEEP
azure-data-explorerkql
springathing
springathing

Reputation: 465

Parsing nested JSON data within a Kusto column

After parsing the JSON data in a column within my Kusto Cluster using parse_json, I'm noticing there is still more data in JSON format nested within the resulting projected value. I need to access that information and make every piece of the JSON data its own column.

I've attempted to follow the answer from this SO post (Parsing json in kusto query) but haven't been successful in getting the syntax correct.

myTable 
| project 
Time, 
myColumnParsedJSON = parse_json(column) 
| project myColumnParsedNestedJSON = parse_json(myColumnParsedJSON.nestedJSONDataKey)

I expect the results to be projected columns, each named as each of the keys, with their respective values displayed in one row record.

Upvotes: 5

Views: 18836

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

please see the note at the bottom of this doc:

It is somewhat common to have a JSON string describing a property bag in which one of the "slots" is another JSON string. In such cases, it is not only necessary to invoke parse_json twice, but also to make sure that in the second call, tostring will be used. Otherwise, the second call to parse_json will simply pass-on the input to the output as-is, because its declared type is dynamic

once you're able to get parse_json to properly have your payload parsed, you could use the bag_unpack plugin (doc) in order to achieve this requirement you mentioned:

I expect the results to be projected columns, each named as each of the keys, with their respective values displayed in one row record.

Upvotes: 14

Related Questions