I have a table wherein i uploaded the file with a column converted to json. but when im tryin to upack it using KQL. it does not work.
Id Query Name Workitem Id Logged Date Details
0 Bug Stats 111 2022-06-08T02:26:43.111196Z {'AssignedTo': 'me', 'ClosedDate': None, 'CreatedDate': '2022-03-08T19:28:15.673Z', 'StartDate': None, 'State': 'For Review', 'Tags': 'tags', 'Title': 'Title', 'WorkItemType': 'Bug'}
Returns nothing
Datatable
|extend Details = parse_json(Details) //i tried todynamic() as well but same response
|evaluate bag_unpack(Details)
Returns Error:
Datatable
|evaluate bag_unpack(Details)
ERROR: Semantic error: evaluate bag_unpack(): the following error(s) occurred while evaluating the output schema: evaluate bag_unpack(): argument #1 expected to be a reference to a dynamic column. Query: 'DevopsQueriesTest |evaluate bag_unpack(Details) '
you do need to first invoke parse_json()
on Details
, for creating a dynamic
value out of a string
value.
see: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/parsejsonfunction
the input string you have isn't a valid JSON payload - it uses single quotes instead of double quotes.
translate()
function, for example.
None
in your payload is invalid too - it needs to be encapsulated with double quotes for the JSON payload to become valid.
replace_string()
function to fix that at query runtime.print s = "{'AssignedTo': 'me', 'ClosedDate': None, 'CreatedDate': '2022-03-08T19:28:15.673Z', 'StartDate': None, 'State': 'For Review', 'Tags': 'tags', 'Title': 'Title', 'WorkItemType': 'Bug'}"
| project s = parse_json(replace_string(translate("'", '"', s), "None", '"None"'))
| evaluate bag_unpack(s)
AssignedTo | ClosedDate | CreatedDate | StartDate | State | Tags | Title | WorkItemType |
---|---|---|---|---|---|---|---|
me | None | 2022-03-08 19:28:15.6730000 | None | For Review | tags | Title | Bug |