I'm new to C#, REST API's and JSON stuff so please bear with me. I've searched hundreds of pages and am trying to figure out how this all works. For context, I'm pulling issues from JIRA REST API using a search like http://mylink.com/rest/api/2/search?jql=project%3DBULK and getting a list of issues. The JSON my program is pulling in is structured like below (this has been simplified from the actual JSON):
EDIT (04/04/2021): Updated JSON below with new use case.
[
{
"key": "FAKE-5402",
"fields": {
"summary": "I need access to blah",
"customfield_18302": [{
"self": "fake.url.com",
"value": "I need this"
}, {
"self": "fake.url2.com",
"value": "I need this also"
}]
}
},
{
"key": "FAKE-5450",
"fields": {
"summary": "Example number 2",
"customfield_18302": [{
"self": "fake.url3.com",
"value": "I need this"
}, {
"self": "fake.url4.com",
"value": "I need this also"
}]
}
}
]
I'm currently working on a method that takes this JSON data, finds the keys the user is searching for and prints it out in CSV format to later be written to a file. Here is my formatAsCSV() function:
public void formatAsCSV()
{
try
{
var dynObj = JsonConvert.DeserializeObject<dynamic>(this.jsonData);
String issues = dynObj["issues"].ToString();
JArray issuesArray = JArray.Parse(issues);
List<String> columnNames = new List<String>()
{"key", "summary"};
String headerRow = "";
foreach (String columnName in columnNames)
{
headerRow += columnName + ", ";
}
headerRow = headerRow.TrimEnd(' ');
headerRow = headerRow.TrimEnd(',');
headerRow += "\n";
String dataRows = "";
foreach (var record in issuesArray)
{
String thisRecord = "";
foreach (String columnName in columnNames)
{
thisRecord += record[columnNames] + ", ";
}
thisRecord = thisRecord.TrimEnd(' ');
thisRecord = thisRecord.TrimEnd(',');
thisRecord += "\n";
dataRows += thisRecord;
}
this.csvData = headerRow + dataRows;
Console.WriteLine("\ncsvData: ");
Console.WriteLine(this.csvData);
}
catch (Exception ex)
{
Console.WriteLine("Error in formatAsCSV: " + ex);
this.errorsOccurred = true;
}
}
The issue I'm having with the above code (and I've tried for days to figure this out...) is that it pulls in all of the issues as separate objects. So when the code loops through, I am able to get the "key", "expand", "id", "self" because they aren't nested. The same code will not grab stuff that's nested under "fields" when I need things like the "summary" and "issuetype" -> "name". I have no idea where to start with getting this information.
Here is the output I would get from something like this (notice the summary is missing after the first comma):
csvData:
key, summary
BULK-62,
BULK-47,
If anyone has any ideas, let me know because I'm running out of things to try. Most issues on here start issues as a JObject but I keep getting errors and "issues" is the main thing I pull which is an array, not an object, so not sure how to handle this. I know it can work SOMEHOW since JIRA allows CSV pulls through a manual process. I'm hoping there's a way to keep the process I have (it's from following a YT tutorial) so I don't have to rewrite everything I've already done but I understand if it comes down to that. Thanks in advance!
There is a similar question I recently answered where the asker wanted to extract all properties from a nested JObject and have those values become the CSV columns. In your case you want to pick and choose properties at different levels from the object and its children.
The key to solving this effectively is using the SelectToken() method to your advantage. This method accepts a JsonPath expression, which lets you query a JToken to find one of its descendants. In its simplest form, this is just a dot-delimited string, such as fields.summary.
Borrowing a bit from the other question, I would make two helper methods. The first method would accept a list of JObjects representing row items along with a Dictionary<string, string> which maps the column names to the corresponding paths that will be used to retrieve the values from each item. The second method would accept a list of values and turn that into a CSV row.
public static class JsonHelper
{
public static string ToCsv(this IEnumerable<JObject> items, Dictionary<string, string> columnPathMappings)
{
if (items == null || columnPathMappings == null)
throw new ArgumentNullException();
var rows = new List<string>();
rows.Add(columnPathMappings.Keys.ToCsv());
foreach (JObject item in items)
{
rows.Add(columnPathMappings.Values.Select(path => item.SelectToken(path)).ToCsv());
}
return string.Join(Environment.NewLine, rows);
}
public static string ToCsv(this IEnumerable<object> values)
{
const string quote = "\"";
const string doubleQuote = "\"\"";
return string.Join(",", values.Select(v =>
v != null ? string.Concat(quote, v.ToString().Replace(quote, doubleQuote), quote) : string.Empty
));
}
}
Armed with these methods, all you would need to do to create the CSV string is set up your mappings, then parse the JSON, extract the items array from it and call the helper to do the rest:
var columnPathMappings = new Dictionary<string, string>
{
{ "key", "key" },
{ "summary", "fields.summary" }
};
string csv = JArray.Parse(json).Cast<JObject>().ToCsv(columnPathMappings);
Here is a working demo: https://dotnetfiddle.net/zzBpbT
If your JSON has an inner array, then that implies that there will be multiple values possible for a particular column in a single row. Given that CSV is a flat structure, multiple values need to be joined together into a newline-delimited string before being inserted into the CSV. You can modify the above code to do that like this:
SelectToken to SelectTokens. This will allow it to extract multiple values for a single path instead of just one.IEnumerable<object> values is itself an IEnumerable<Jtoken>. In that case, join the JTokens together into a newline-delimited string and then handle that string as before.The modified code would look like this:
public static class JsonHelper
{
public static string ToCsv(this IEnumerable<JObject> items, Dictionary<string, string> columnPathMappings)
{
if (items == null || columnPathMappings == null)
throw new ArgumentNullException();
var rows = new List<string>();
rows.Add(columnPathMappings.Keys.ToCsv());
foreach (JObject item in items)
{
rows.Add(columnPathMappings.Values.Select(path => item.SelectTokens(path)).ToCsv());
}
return string.Join(Environment.NewLine, rows);
}
public static string ToCsv(this IEnumerable<object> values)
{
const string quote = "\"";
const string doubleQuote = "\"\"";
return string.Join(",", values.Select(v =>
{
if (v != null)
{
if (v is IEnumerable<JToken> e)
{
v = string.Join(Environment.NewLine, e.Select(t => t.ToString()));
}
return string.Concat(quote, v.ToString().Replace(quote, doubleQuote), quote);
}
return string.Empty;
}));
}
}
The last step is that you need to specify a path which will get the child values in the array. To do that, you can use a wildcard * in the path for the array index. So your mappings would look like this:
var columnPathMappings = new Dictionary<string, string>
{
{ "key", "key" },
{ "summary", "fields.summary" },
{ "customfield_18302", "fields.customfield_18302[*].value" },
};
Working demo here: https://dotnetfiddle.net/IfB8sw