I am trying to work with a json file that was ingested into Data Explorer as a table with two columns: FileLineContent and LineNumber (there are 2 more but they are not interesting for my purpose). I use Kusto to get the content of the file and to manipulate it. The table looks like this:
LineNumber FileLineContent
1 [
2 {
3 "atr1": "value11",
4 "atr2": "value12",
5 "atr3": {
6 atr31": "value131",
7 atr32": "value132"
8 },
9 "atr4": {
10 "atr41": {
11 "atr411": [
12 "value14111",
13 "value14112"
14 ]
15 }
16 }
17 },
18 {
19 "atr1": "value21",
20 "atr2": "value22",
21 "atr3": {
22 atr31": "value231",
23 atr32": "value232"
24 },
25 "atr4": {
26 "atr41": {
27 "atr411": [
28 "value24111",
29 "value24112"
30 ]
31 }
32 }
33 }
34 ]
To summarize: we have an array of json elements with a deep hierarchy (up to 5) formatted in a "nice way" for the human eyes, not so much for kusto's eyes. I need to work with these elements and to display several values for several attributes. I am having a hard time with building the raw variable that I could parse as a json.
I tried to build manually a json with a similar structure and I noticed that I have to eliminate all spaces and all quotes inside the curly brackets have to be escaped. It has to look like this:
let json = '{"atr1":"value11","atr2":"value12","atr3":"{\\"atr31\\":\\"value131\\",\\"atr32\\":\\"value132\\",\\"atr33\\":\\"value133\\",\\"atr34\\":\\"value134\\",\\"atr35\\":\\"value135\\"}"}';
then I can use the parse_json() to display the values of the attributes.
print parse_json(tostring(parse_json(json).atr3)).atr31
But that is not an array of jsons, of course. I just built it to see how kusto expects it.
In conclusion my problem is: how can I transform the table above into something that I could parse as a json? I tried some of the aggregation functions, but nothing gives a satisfying result.
Thank you!
This is what I used to solve the problem. Adding it here in case someone else needs it. The query assumes that the data is stored in the JsonTable in the format expained above:
let RawDataFromTable =
['JsonTable']
| order by LineNumber asc
| summarize raw = strcat_array(make_list(FileLineContent), "\r\n") by FileName;
RawDataFromTable
| project json = parse_json(tostring(raw))
| mv-expand json
| evaluate bag_unpack(json)
| evaluate bag_unpack(atr3,'atr3_') //this line expands the attributes under atr3 under different columns called atr3_atr31, atr3_atr32.
The query can then be modified based on the data that is needed.