Unable to read the dynamic array property using KQL

Question

I have below structure json object

{
 "records": [
   {
     "key": "be4kf29j",
     "value": {
       "channelId": "Connect"
     }
   }
 ]

}

I am trying to retrieve all the key values. Below is the KQL query i am using. but i am unbale retrieve the "key" field values.

   let js = '{"records":[{"key":"be4kf29j","value":{"channelId":"Connect"}}]}';
let recs = parse_json(js);
recs
| mv-expand dd = recs.records
| extend key = tostring(dd.key)

Can somebody guide me what i am missing.

Answer 1

The parse_json does take a bit of getting used to.

In your particular cases the following worked for me.

let js = '{"records":[{"key":"be4kf29j","value":{"channelId":"Connect"}}]}';
let recs = parse_json(js);
print recs = parse_json(js), parse_json(js)['records'], parse_json(tostring(parse_json(parse_json(js)['records'])))[0]['key']

recs	print_1	print_2
{"records":[{"key":"be4kf29j","value":{"channelId":"Connect"}}]}	[{"key":"be4kf29j","value":{"channelId":"Connect"}}]	be4kf29j

Of if you're using it from a table something like this should work.

let T = datatable (recs:string) [
'{"records":[{"key":"be4kf29j","value":{"channelId":"Connect"}}]}'
];
T
| extend jsonrecs = parse_json(recs)
| extend records = jsonrecs['records']
| extend keyvalue = parse_json(tostring(records))[0]['key']

recs	jsonrecs	records	keyvalue
{"records":[{"key":"be4kf29j","value":{"channelId":"Connect"}}]}	{"records":[{"key":"be4kf29j","value":{"channelId":"Connect"}}]}	[{"key":"be4kf29j","value":{"channelId":"Connect"}}]	be4kf29j