Xml

Json connector package

JsonSource

Json Source let you read a json.

Here an example for a json file:

[
  {
    "Col1": 1,
    "Col2": "Test1"
  },
  {
    "Col1": 2,
    "Col2": "Test2"
  },
  {
    "Col1": 3,
    "Col2": "Test3"
  }
]

Here is some code that would read that json (and deserialize it into the object type):

public class MySimpleRow
{
    public int Col1 { get; set; }
    public string Col2 { get; set; }
}

JsonSource<MySimpleRow> source = new JsonSource<Todo>("http://test.com/");

This code would then read the three entries from the source and post it into it's connected component.

Nested arrays

The array doesn't need to be the top node of your json - it could be nested in your json file. Like this:

{
    "data": {
        "array": [
            {
                "Col1": 1,
                "Col2": "Test1"
            },
            ...
        ]
    }
}

ETLBox automatically scans the incoming json file and starts reading (and deserializing) after the first occurrence of the begin of an array (which is the "[" symbol).

Working with JsonSerializer

Sometimes you have a more complex json structure. Here an example:

[
    {
        "Column1": 1,
        "Column2": {
            "Id": "A",
            "Value": "Test1"
        }
    },
    ...
]

If you defined your POCOs types to deserialize this json you would need to create objects like this:

public class MyRow
{
    public int Column1 { get; set; }
    public MyIdValueObject Column2 { get; set; }
}

public class MyIdValueObject
{
    public string Id { get; set; }
    public string Value { get; set; }
}

Sometimes you don't want to specify all objects that would map your json structure. To get around this the underlying JsonSerializer object that is used for deserialization is exposed by the JsonSource. JsonSerializer belongs to Newtonsoft.Json
You could add your own JsonConverter so that you could use JsonPath within your JsonProperty attributes. (Please note that the example JsonPathConverter is also part of ETLBox).

[JsonConverter(typeof(JsonPathConverter))]
public class MySimpleRow
{
    [JsonProperty("Column1")]
    public int Col1 { get; set; }
    [JsonProperty("Column2.Value")]
    public string Col2 { get; set; }
}

JsonSource<MySimpleRow> source = new JsonSource<MySimpleRow>("res/JsonSource/NestedData.json", ResourceType.File);

JsonDestination

The result of your pipeline can be written as json using a JsonDestination.

The following code:

 public class MySimpleRow
{
    public string Col2 { get; set; }
    public int Col1 { get; set; }
}

JsonDestination<MySimpleRow> dest = new JsonDestination<MySimpleRow>("test.json", ResourceType.File);

would result in the following json:

[
  {
    "Col1": 1,
    "Col2": "Test1"
  },
  {
    "Col1": 2,
    "Col2": "Test2"
  },
  {
    "Col1": 3,
    "Col2": "Test3"
  }
]

Like the JsonSource you can modify the exposed JsonSerializer to modify the serializing behavior. JsonSerializer belongs to Newtonsoft.Json

Using Json with arrays

If you don't want to use objects, you can use arrays with your JsonSource. Your code would look like this:

JsonSource<string[]> source = new JsonSource<string[]>("https://jsonplaceholder.typicode.com/todos");

Now you either have to override the JsonSerializer yourself in order to properly convert the json into a string[]. Or your incoming Json has to be in following format:

[
    [
        "1",
        "Test1"
    ],
    ...
]

Working with dynamic objects

JsonSource and destination support the usage of dynamic object. This allows you to use a dynamic ExpandoObject instead of a POCO.

JsonSource<ExpandoObject> source = new JsonSource<ExpandoObject>("res/JsonSource/TwoColumnsDifferentNames.json", ResourceType.File);

RowTransformation<ExpandoObject> trans = new RowTransformation<ExpandoObject>(
    row =>
    {
        dynamic r = row as ExpandoObject;
        r.Col1 = r.Value1;
        r.Col2 = r.Value2;
        return r;
    });
DbDestination<ExpandoObject> dest = new DbDestination<ExpandoObject>(Connection, "DynamicJson");

source.LinkTo(trans).LinkTo(dest);
Network.Execute(source);

3rd party libraries

It is based on the Newtonsoft.Json and the JsonSerializer