How to handle inner Json when using JsonOutputter

Question

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?

Michael Rys · Accepted Answer · 2017-06-29 06:15:27Z

1

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.

answered Jun 29, 2017 at 6:15

Michael Rys

6,68417 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Magnus Johannesson Over a year ago

Thanks. I updated the sample JsonOutputter and serialized the JSON column as raw JSON.

Tom Sun · Accepted Answer · 2017-06-29 07:19:50Z

If JsonOutputter is not the only choice to that ,we could covert csv file to Json with our custom code. I test it with following csv file.

number|Location
1|{ "type":"Point", "coordinates":[ 13.7726, 73.5091 ] }
2|{ "type":"Point", "coordinates":[ 14.7726, 74.5091 ] }

Please have a try to use the following code, it works correctly on my side.

 var lines = File.ReadAllText(@"C:\Tom\tomtest.csv").Replace("\r", "").Split('\n');
            var csv = lines.Select(l => l.Split('|')).ToList();

            var headers = csv[0];
            var dicts = csv.Skip(1).Select(row => headers.Zip(row, Tuple.Create).ToDictionary(p => p.Item1, p => p.Item2)).ToArray().Select(x=>new
            {
                number = x["number"],
                location = JObject.Parse(x["Location"])
            });

            string json = JsonConvert.SerializeObject(dicts);
            Console.WriteLine(json);

Test result:

Collectives™ on Stack Overflow

How to handle inner Json when using JsonOutputter

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related