How to transform a JSON array of objects to a Kusto table?

Question

I have a JSON schema that I get from the server and I need to transform this JSON into a log analytics query language table and use that table to make a join with another table.

The JSON has the following schema:

[{
   "X": "xyz",
   "Y": "xyz",
   "Z": "xyz",
   "prop1": "value1",
   "prop2": "value2",
   "prop3": "value3"
}, {
     "X": "xyz",
     "Y": "xyz",
     "Z": "xyz",
     "prop1": "value1",
     "prop2": "value2",
     "prop3": "value3"
}]

I tried this :

let table = todynamic('[{
  "X": "xyz",
  "Y": "xyz",
  "Z": "xyz",
  "prop1": "value1",
  "prop2": "value2", 
  "prop3": "value3"
}, {
  "X": "xyz",
  "Y": "xyz",
  "Z": "xyz",
  "prop1": "value1",
  "prop2": "value2",
  "prop3": "value3"
]');

But this does not convert the JSON into something that can be used in a join with other tables.

Any help will be very appreciated.

@MurrayFoxcroft, I want to parse an array here an have a row for each object in the new table so it's not the same as the question that you are pointing to. — , Feb 18 '19 at 11:37
have you tried [parse_json](https://learn.microsoft.com/en-us/azure/kusto/query/parsejsonfunction) function? you might need to use `string` first — 4c74356b41, Feb 18 '19 at 11:56
@4c74356b41 it returns dynamic which cannot be joined with other tables. I need the transformation to take the JSON string and make a datatable from it. — , Feb 18 '19 at 13:32
what if you combine it with [extend](https://learn.microsoft.com/en-us/azure/kusto/query/extendoperator) and [datatable](https://learn.microsoft.com/en-us/azure/kusto/query/datatableoperator) — 4c74356b41, Feb 18 '19 at 13:40

Yoni L. · Accepted Answer · 2019-02-19T17:42:29.597

11

try using print and dynamic:

print myDynamicValue = dynamic([{
   "X": "xyz",
   "Y": "xyz",
   "Z": "xyz",
   "prop1": "value1",
   "prop2": "value2",
   "prop3": "value3"
}, {
   "X": "xyz",
   "Y": "xyz",
   "Z": "xyz",
   "prop1": "value1",
   "prop2": "value2",
   "prop3": "value3"
}])
| mvexpand myDynamicValue // this line is just an example

Update (based on question in comments):

let result = 
print myDynamicValue = dynamic(
[
    { "X": "xyz", "Y": "xyz", "Z": "xyz", "prop1": "value1", "prop2": "value2", "prop3": "value3" }, 
    { "X": "xyz", "Y": "xyz", "Z": "xyz", "prop1": "value1", "prop2": "value2", "prop3": "value3" }
]) 
| mvexpand myDynamicValue 
| evaluate bag_unpack(myDynamicValue);
result

edited Feb 19 '19 at 17:42

answered Feb 18 '19 at 16:38

Yoni L.

22,627
2
29
48

1

but I need each property to become a column. Is there any way to do this ? – Feb 19 '19 at 09:35
1

Try using `bag_unpack()`: https://learn.microsoft.com/en-us/azure/kusto/query/bag-unpackplugin – Yoni L. Feb 19 '19 at 13:16
why can't we do something like: let myDynamicValue = dynamic([{ "X": "xyz", "Y": "xyz", "Z": "xyz", "prop1": "value1", "prop2": "value2", "prop3": "value3" }, { "X": "xyz", "Y": "xyz", "Z": "xyz", "prop1": "value1", "prop2": "value2", "prop3": "value3" }]) | mvexpand myDynamicValue | evaluate bag_unpack(myDynamicValue) // this line is just an example – Feb 19 '19 at 14:21
Sorry, I should not be posting code in the comment section, if you want I can open another question . – Feb 19 '19 at 14:29
I've updated my original answer with another example based on your comment. the query in your comment is invalid as you don't have a tabular expression when you try to invoke mvepand. the print operator allows you to do so – Yoni L. Feb 19 '19 at 17:41
This gets me so close to what I need, but I have a slight twist unfortunately, sometimes the child items are arrays. Can the arrays expanded too? Here's a sample: ``` let result = print myDynamicValue = dynamic( [ {"item": { "prop1": "value1", "prop2": "value2" } }, {"item": [ { "prop1": "value1", "prop2": "value2" } ]}, {"item": [ { "prop1": "value1", "prop2": "value2" }, { "prop1": "value1", "prop2": "value2" } ]} ]) | mvexpand myDynamicValue | evaluate bag_unpack(myDynamicValue); result ``` – Craig - MSFT May 14 '20 at 23:09
2

arrays can be `mv-expand`ed too, yes. for example: ```print myDynamicValue = dynamic( [{ "item": { "prop1": "value1", "prop2": "value2" } }, { "item": [{ "prop1": "value1", "prop2": "value2" }] }, { "item": [{ "prop1": "value1", "prop2": "value2" }, { "prop1": "value1", "prop2": "value2" }] }]) | mvexpand myDynamicValue | mv-expand myDynamicValue.item | evaluate bag_unpack(myDynamicValue_item) | project-away myDynamicValue``` – Yoni L. May 15 '20 at 03:08
Thanks Yoni, I wish that was simpler, or that it even worked :-| The query produces a result that is close to what is expected (4 rows) but instead has 5 rows. – Craig - MSFT May 16 '20 at 16:06
you may want to open a separate thread for your issue. it's not clear (to me) what the query is, what the input is, and what the expected result is (and trying to understand if from short comments isn't proving itself) – Yoni L. May 16 '20 at 22:32

How to transform a JSON array of objects to a Kusto table?

1 Answers1

Linked