SSIS Script Task: Split a JSON array into a list
Peter Bishop
181
Reputation points
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.
Sign in to answer