Search code examples
azure-cosmosdbazure-data-lakeu-sql

How to handle inner Json when using JsonOutputter

I'm converting some csv files into Json using the JsonOutputter. In the csv files I have a field containing Json like this (pipe character is delimiter): ...|{ "type":"Point", "coordinates":[ 18.7726, 74.5091 ] }|...

When it's output to Json, the result looks like this: "Location": "{ \"type\":\"Point\", \"coordinates\":[ 18.7726, 74.5091 ] }"

I would like to get rid of the outer quotes to make the Json look like this: "Location": { "type":"Point", "coordinates":[ 18.7726, 74.5091 ] }

What is the best way to accomplish this? The output Json will be stored in Cosmos DB, so I guess the "cleaning up" of the Json could be done either in U-SQL or in Cosmos DB?

Solution

  • The sample outputter is only generating flat JSON. Since we do not have a JSON datatype, any string value has to be escaped to be a string value.

    You can write your own custom Outputter that for example takes SqlMap instances for nested values and output them as nested JSON, or - if you know that some strings in the rowsets are really JSON and not just strings, serialize them without the quotes.