KUSTO QUERY LANGUAGE (KQL) - Cannot unpack the dictionary

Question

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) '

user_7861807 · Answer

you do need to first invoke parse_json() on Details, for creating a dynamic value out of a string value.

see: https://docs.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.
- it's best that you fix the component that generates the data, and ingest valid payloads instead of invalid ones.
- if, for whatever reason, you can't/won't do so - and you prefer paying a performance hit as part of your queries, you can use the translate() function, for example.
  - see: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/translatefunction
- Moreover, the None in your payload is invalid too - it needs to be encapsulated with double quotes for the JSON payload to become valid.
  - you can use the replace_string() function to fix that at query runtime.
  - see: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/replace-string-function

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

KUSTO QUERY LANGUAGE (KQL) - Cannot unpack the dictionary

1 Answers