SSIS Script Task: Split a JSON array into a list

Question

I have a JSON array of the form:

"data": [
	{"tag1": 1,"tag2": 2,"section1": {"tag3": "Dummy"},"tag4": 3,"array1": ["dummy","dummy"]},
	{"tag1": 4,"tag2": 5,"section1": {"tag3": "Dummy"},"tag4": 6,"array1": ["dummy","dummy"]},
	{"tag1": 7,"tag2": 8,"section1": {"tag3": "Dummy"},"tag4": 9,"array1": ["dummy","dummy"]}
]

and I need to split this into a list containing:

Entry1: "{\"tag1\": 1,\"tag2\": 2,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 3,\"array1\": [\"dummy\",\"dummy\"]}"
Entry2: "{\"tag1\": 4,\"tag2\": 5,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 6,\"array1\": [\"dummy\",\"dummy\"]}"
Entry3: "{\"tag1\": 7,\"tag2\": 8,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 9,\"array1\": [\"dummy\",\"dummy\"]}"

I've written a little loop of code to achieve this:

string json = "\"data\": [{\"tag1\": 1,\"tag2\": 2,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 3,\"array1\": [\"dummy\",\"dummy\"]},{\"tag1\": 4,\"tag2\": 5,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 6,\"array1\": [\"dummy\",\"dummy\"]},{\"tag1\": 7,\"tag2\": 8,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 9,\"array1\": [\"dummy\",\"dummy\"]}]"

List<string> addresses = new List<string>();

for (int loop = 0, openSection = 0, openQuote = 0, start = 0; loop < json.Length; loop++)
{
	if (loop != (json.Length - 1))
	{
		// Skip the special characters
		switch (json.Substring(loop, 2))
		{
			case "\\\"":    // quotation mark
			case "\\\\":    // reverse solidus
			case "\\/":     // solidus
			case "\\b":     // backspace
			case "\\f":     // formfeed
			case "\\n":     // newline
			case "\\r":     // carriage return
			case "\\t":     // horizontal tab
				loop += 2;
				break;
			case "\\u":     // 4 hexadecimal digits
				loop += 6;
				break;
		}
	}
	if (json.Substring(loop, 1) == "\"")
	{
		if (openQuote == 0)
			openQuote++;
		else
			openQuote--;
	}
	if (json.Substring(loop, 1) == "{" && openQuote != 1)
	{
		if (openSection == 0)
			start = loop;
		openSection++;
	}
	if (json.Substring(loop, 1) == "}" && openQuote != 1)
	{
		openSection--;
		if (openSection == 0)
			addresses.Add(json.Substring(start, loop - start + 1));
	}
}

Although this works, is there a better way? I know there are JSON handling modules out there (Newtonsoft?) but I'm restricted to what can be installed on the target platform and I'm also not looking to actually manipulate the JSON data - just split the array.

Many thanks.

Answer 1

Hi @Peter Bishop , Welcome to Microsoft Q&A,

You can use System.Text.Json to simplify your code. Here is a .Net 8.o Console project:

using System.Text.Json;

string json = "{ \"data\": [{\"tag1\": 1,\"tag2\": 2,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 3,\"array1\": [\"dummy\",\"dummy\"]}," +
                      "{\"tag1\": 4,\"tag2\": 5,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 6,\"array1\": [\"dummy\",\"dummy\"]}," +
                      "{\"tag1\": 7,\"tag2\": 8,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 9,\"array1\": [\"dummy\",\"dummy\"]}]}";

using JsonDocument doc = JsonDocument.Parse(json);
JsonElement root = doc.RootElement;

if (root.TryGetProperty("data", out JsonElement dataArray) && dataArray.ValueKind == JsonValueKind.Array)
{
    List<string> addresses = new();
    foreach (JsonElement element in dataArray.EnumerateArray())
    {
        addresses.Add(element.GetRawText()); 
    }

    for (int i = 0; i < addresses.Count; i++)
    {
        Console.WriteLine($"Entry{i + 1}: {addresses[i]}");
    }
}
Console.ReadLine();

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.