'Remove escape characters from Azure Data Explorer JSON object in Azure Data Factory
I trying to export the results of an ADX query into a JSON file using ADF. I have tried using the copy activity to map the data directly from the query to the blob storage dataset. Two of the columns that are being returned are 'dynamic' type in ADX, and such should be output as a JSON object in the file.
The input for the query looks as required, with no additional characters:
The below image is the top of the properties column (column 5), showing the opening of the object bracket.
The output file only contains additional escape and line return characters within the properties object data that is returned. The Build, TypeName and other key and value pair columns are not affected.
I need to be able to return this data in JSON format without any of the additional characters. Any ideas on how I could achieve this within ADF?
UPDATE: Having tested with Azure Data Studio and exporting the data directly to JSON from the application, it seems that the KQL is the issue. When I use parse_json(Properties) I receive the same /r/n and /" characters as before. When I use extractjson("$",tostring(Properties)) I see a slight improvement, but the " are still escaped \ "
Solution 1:[1]
You can use as below query:
SETEL_Objects | project ID, FullName, parse_json(properties) | take 50
Refer official MS docs for more details: todynamic(), parse_json()
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | KarthikBhyresh-MT |