SSIS Script Task: Split a JSON array into a list

Peter Bishop 181 Reputation points
2025-03-11T14:41:08.5666667+00:00

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.

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
11,339 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.