Json Source

This article contains example code that shows the usage of the JsonSource component.

The JsonDource let you load data in json format from various sources - either from a file, a web service or from an Azure blob. Internally, it uses the Newtonsoft Json library .

Shared code

The following method is used in the subsequent examples, and prints the content of a json file on the console output.

private void PrintFile(string sourceFile) {
    Console.WriteLine($"Content of file '{Path.GetFileName(sourceFile)}'");
    Console.WriteLine("---");
    Console.WriteLine(File.ReadAllText(sourceFile));
    Console.WriteLine("---");
}

Read json file

With POCO

Let’s start with reading a simple json file. We can read this into a memory destination using a POCO (Plain old component object).

public class Record
{
    public SubRecord Inner { get; set; }
    public int Id { get; set; }
}

public class SubRecord
{
    public string Value { get; set; }
    public decimal Number { get; set; }
}

string sourceFile = "res/Examples/Records.json";
PrintFile(sourceFile);

var source = new JsonSource<Record>() {
    ResourceType = ResourceType.File,
    Uri = sourceFile
};
var dest = new MemoryDestination<Record>();

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

foreach (var row in dest.Data)
    Console.WriteLine($"Received Id: {row.Id}, Value1: {row.Inner.Value}, Value2: {row.Inner.Number}");

/* Output:
Content of file 'Records.json'
---
{
    "CreationDate": "2022-01-01",
    "Content": [
    {
        "Id": 1,
        "Inner": {
        "Value": "Test1",
        "Number": 1.1
        }
    },
    {
        "Id": 2,
        "Inner": {
        "Number": 1.2
        }
    },
    {
        "Id": 3,
        "Inner": {
        "Value": "Test3",
        "Number": 1.3
        }
    }
    ],
    "Confidential": false
}
---
Received Id: 1, Value1: Test1, Value2: 1.1
Received Id: 2, Value1: , Value2: 1.2
Received Id: 3, Value1: Test3, Value2: 1.3
*/

With json attributes

We can use the Json attributes provided by the Newtonsoft Json library to control the deserialization of the incoming data into our objects.

public class RecordC
{
    [JsonProperty("Inner")]
    public SubRecordC InnerElement { get; set; }
    [JsonProperty("Id")]
    public int IdAttr { get; set; }
}

public class SubRecordC
{
    public string Value { get; set; }
    [JsonRequired]
    public decimal Number { get; set; }
}

string sourceFile = "res/Examples/ThreeArrays.json";
PrintFile(sourceFile);

var source = new JsonSource<RecordC>() {
    ResourceType = ResourceType.File,
    Uri = sourceFile
};
source.PropertyNameOfDataArray = "ContentArray";

var dest = new MemoryDestination<RecordC>();

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

foreach (var row in dest.Data)
    Console.WriteLine($"Received Id: {row.IdAttr}, Value1: {row.InnerElement.Value}, Value2: {row.InnerElement.Number}");

/* Output:
Content of file 'ThreeArrays.json'
---
{
"CreationDate": "2022-01-01",
"Array1": [ 1, 2, 3 ],
"Array2": [
    {
    "Id": 0,
    "Inner": {
        "Value": "X"
    }
    }
],
"ContentArray": [
    {
    "Id": 1,
    "Inner": {
        "Value": "Test1",
        "Number": 1.1
    }
    },
    {
    "Id": 2,
    "Inner": {
        "Number": 1.2
    }
    },
    {
    "Id": 3,
    "Inner": {
        "Value": "Test3",
        "Number": 1.3
    }
    }
],
"Confidential": false
}
---
Received Id: 1, Value1: Test1, Value2: 1.1
Received Id: 2, Value1: , Value2: 1.2
Received Id: 3, Value1: Test3, Value2: 1.3
*/

JSON Path in json properties

The ETLBox.Json package provide a JsonPathConverter which allows to use JSON Path expression in the JsonProperty attributes.

[JsonConverter(typeof(JsonPathConverter))]
public class RecordFlatten
{
    [JsonProperty("$.Id")]
    public int Id { get; set; }
    [JsonProperty("Inner.Value")]
    public string Value1 { get; set; }
    [JsonProperty("Inner.Number")]
    public decimal Value2 { get; set; }
}

[Fact]
public void UsingJsonPathConverter() {
    string sourceFile = "res/Examples/Records.json";
    PrintFile(sourceFile);

    var source = new JsonSource<RecordFlatten>() {
        ResourceType = ResourceType.File,
        Uri = sourceFile
    };

    var dest = new MemoryDestination<RecordFlatten>();

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

    foreach (var row in dest.Data)
        Console.WriteLine($"Received Id: {row.Id}, Value1: {row.Value1}, Value2: {row.Value2}");

    /* Output:
    Content of file 'Records.json'
    ---
    {
        "CreationDate": "2022-01-01",
        "Content": [
        {
            "Id": 1,
            "Inner": {
            "Value": "Test1",
            "Number": 1.1
            }
        },
        {
            "Id": 2,
            "Inner": {
            "Number": 1.2
            }
        },
        {
            "Id": 3,
            "Inner": {
            "Value": "Test3",
            "Number": 1.3
            }
        }
        ],
        "Confidential": false
    }
    ---
    Received Id: 1, Value1: Test1, Value2: 1.1
    Received Id: 2, Value1: , Value2: 1.2
    Received Id: 3, Value1: Test3, Value2: 1.3
    */
}

Reading into dynamic

We can read again the json file in the previous example, this time without using a POCO but a dynamic ExpandoObject.

string sourceFile = "res/Examples/Records.json";
PrintFile(sourceFile);

var source = new JsonSource() {
    ResourceType = ResourceType.File,
    Uri = sourceFile
};
source.RowModificationAction = (row,smd) => {
    var r = (row as dynamic).Inner as IDictionary<string, object>;
    if (!r.ContainsKey("Value"))
        r.Add("Value", "");
};
var dest = new MemoryDestination();

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

foreach (dynamic row in dest.Data)
    Console.WriteLine($"Received Id: {row.Id}, Value1: {row.Inner.Value}, Value2: {row.Inner.Number}");

/* Output:
Content of file 'Records.json'
---
{
    "CreationDate": "2022-01-01",
    "Content": [
    {
        "Id": 1,
        "Inner": {
        "Value": "Test1",
        "Number": 1.1
        }
    },
    {
        "Id": 2,
        "Inner": {
        "Number": 1.2
        }
    },
    {
        "Id": 3,
        "Inner": {
        "Value": "Test3",
        "Number": 1.3
        }
    }
    ],
    "Confidential": false
}
---
Received Id: 1, Value1: Test1, Value2: 1.1
Received Id: 2, Value1: , Value2: 1.2
Received Id: 3, Value1: Test3, Value2: 1.3
*/

JSON Path with dynamic

The JSON Path syntax can also be used in combination with dynamic ExpandoObject. You need to add the ExpandoJsonPathConverter to the JsonSerializer converters.

string sourceFile = "res/Examples/Records.json";
PrintFile(sourceFile);

var source = new JsonSource() {
    ResourceType = ResourceType.File,
    Uri = sourceFile
};
var dest = new MemoryDestination();
List<JsonProperty2JsonPath> pathLookups = new List<JsonProperty2JsonPath>()
{
    new JsonProperty2JsonPath()
    {
        SearchPropertyName = "Inner",
        JsonPath = "$.Value",
        OutputPropertyName = "Value1",

        },
    new JsonProperty2JsonPath() {
        SearchPropertyName = "Inner",
        JsonPath = "$.Number",
        OutputPropertyName = "Value2"
        }
};
source.JsonSerializer.Converters.Add(new ExpandoJsonPathConverter(pathLookups));
source.RowModificationAction = (row, smd) => {
    var r = (row as dynamic) as IDictionary<string, object>;
    if (!r.ContainsKey("Value1"))
        r.Add("Value1", "");
};

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

foreach (dynamic row in dest.Data)
    Console.WriteLine($"Received Id: {row.Id}, Value1: {row.Value1}, Value2: {row.Value2}");

/* Output:
Content of file 'Records.json'
---
{
    "CreationDate": "2022-01-01",
    "Content": [
    {
        "Id": 1,
        "Inner": {
        "Value": "Test1",
        "Number": 1.1
        }
    },
    {
        "Id": 2,
        "Inner": {
        "Number": 1.2
        }
    },
    {
        "Id": 3,
        "Inner": {
        "Value": "Test3",
        "Number": 1.3
        }
    }
    ],
    "Confidential": false
}
---
Received Id: 1, Value1: Test1, Value2: 1.1
Received Id: 2, Value1: , Value2: 1.2
Received Id: 3, Value1: Test3, Value2: 1.3
*/

Redirecting errors

We can use the error linking if we want to ignore flawed data in the source.

public class Record
{
    public SubRecord Inner { get; set; }
    public int Id { get; set; }
}

public class SubRecord
{
    public string Value { get; set; }
    public decimal Number { get; set; }
}

string sourceFile = "res/Examples/RecordsWithErrors.json";
PrintFile(sourceFile);

var source = new JsonSource<Record>() {
    ResourceType = ResourceType.File,
    Uri = sourceFile
};
var dest = new MemoryDestination<Record>();
var errorDest = new MemoryDestination<ETLBoxError>();

source.LinkTo(dest);
source.LinkErrorTo(errorDest);
Network.Execute(source);

foreach (var row in dest.Data)
    Console.WriteLine($"Received Id: {row.Id}, Value1: {row.Inner.Value}, Value2: {row.Inner.Number}");

foreach (var err in errorDest.Data)
    Console.WriteLine($"Error record: {err.ErrorText} " +
        Environment.NewLine +
        $"{err.RecordAsJson}");

/* Output:
Content of file 'RecordsWithErrors.json'
---
{
    "Content": [
    {
        "Id": 1,
        "Inner": {
        "Value": "ERR",
        "Number": null
        }
    },
    {
        "Id": 2,
        "Inner": {
        "Number": 1.2,
        "Value": "OK"
        }
    },
    {
        "Id": 3,
        "Inner": {
        "Value": "OK",
        "Number": 1.3
        }
    },
    {
        "Id": X,
        "Inner": {
        "Value": "ERR",
        "Number": 1.4
        }
    }
    ]
}
---
Received Id: 2, Value1: OK, Value2: 1.2
Received Id: 3, Value1: OK, Value2: 1.3
Error record: Error converting value {null} to type 'System.Decimal'. Path 'Content[0].Inner.Number', line 7, position 22.
Error converting value {null} to type 'System.Decimal'. Path 'Content[0].Inner.Number', line 7, position 22.
Error record: Unexpected character encountered while parsing value: X. Path 'Content[3].Id', line 25, position 13.
Unexpected character encountered while parsing value: X. Path 'Content[3].Id', line 25, position 13.
*/

Reading multiple Uris

You can use the GetNextUri/HasNextUri pattern (provided on all streaming connectors) to go through a set a files, web service endpoints or blobs.

The following example shows the usage with files - change the ResourceType e.g. to Http if you want to read json formatted data from more than one endpoint.

public class Record
{
    public SubRecord Inner { get; set; }
    public int Id { get; set; }
}

public class SubRecord
{
    public string Value { get; set; }
    public decimal Number { get; set; }
}

string pattern = @"res/Examples/File";
PrintFile("res/Examples/File1.json");
PrintFile("res/Examples/File2.json");
PrintFile("res/Examples/File3.json");

var source = new JsonSource<Record>();
source.ResourceType = ResourceType.File;
source.GetNextUri = meta => pattern + (meta.RequestCount + 1) + ".json";
source.HasNextUri = meta => meta.RequestCount < 3;
var dest = new MemoryDestination<Record>();

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

foreach (var row in dest.Data)
    Console.WriteLine($"Received Id: {row.Id}, Value1: {row.Inner.Value}");

/* Output:
Content of file 'File1.json'
---
[
    {
    "Id": 1,
    "Inner": {
        "Value": "Test1",
        "Number": 1.1
    }
    },
    {
    "Inner": {
        "Number": 1.2,
        "Value": "Test2"
    },
    "Id": 2
    }
]
---
Content of file 'File2.json'
---
[
    {
    "Id": 3,
    "Inner": {
        "Value": "Test3",
        "Number": 1.3
    }
    },
    {
    "Id": 4,
    "Inner": {
        "Value": "Test4",
        "Number": 1.4
    }
    }
]
---
Content of file 'File3.json'
---
[
    {
    "Id": 5,
    "Inner": {
        "Value": "Test5",
        "Number": 1.5
    }
    }
]
---
Received Id: 1, Value1: Test1
Received Id: 2, Value1: Test2
Received Id: 3, Value1: Test3
Received Id: 4, Value1: Test4
Received Id: 5, Value1: Test5
*/

Different resource types

Reading from Azure blob storage

All streaming connectors support reading data from Azure blob storage instead of a file. Here is an example for the JsonSource.

public class Record
{
    public SubRecord Inner { get; set; }
    public int Id { get; set; }
}

public class SubRecord
{
    public string Value { get; set; }
    public decimal Number { get; set; }
}

//Upload source file into Azure blob container
string sourceFile = @"res/Examples/Records.json";
PrintFile(sourceFile);
string connectionString = AzureStorageHelper.RetrieveConnectionString();
AzureStorageHelper.DeleteAndCreateContainer(connectionString, "json-test");
BlobContainerClient containerClient = new BlobContainerClient(connectionString, "json-test");
using (var reader = new FileStream(sourceFile, FileMode.Open)) {
    var resp = containerClient.GetBlobClient("Records.json").Upload(reader);
}

//Read uploaded file with CsvSource
var source = new JsonSource<Record>("Records.json");
source.ResourceType = ResourceType.AzureBlob;
source.AzureBlobStorage.ConnectionString = connectionString;
source.AzureBlobStorage.ContainerName = "json-test";
var dest = new MemoryDestination<Record>();

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

foreach (var row in dest.Data)
    Console.WriteLine($"Received Id: {row.Id}, Value1: {row.Inner.Value}, Value2: {row.Inner.Number}");

/* Output:
Content of file 'Records.json'
---
{
    "CreationDate": "2022-01-01",
    "Content": [
    {
        "Id": 1,
        "Inner": {
        "Value": "Test1",
        "Number": 1.1
        }
    },
    {
        "Id": 2,
        "Inner": {
        "Number": 1.2
        }
    },
    {
        "Id": 3,
        "Inner": {
        "Value": "Test3",
        "Number": 1.3
        }
    }
    ],
    "Confidential": false
}
---
Received Id: 1, Value1: Test1, Value2: 1.1
Received Id: 2, Value1: , Value2: 1.2
Received Id: 3, Value1: Test3, Value2: 1.3
*/

Reading from web service

All streaming connectors support reading data from any http endpoint. Here is an example for reading data from a mocked WebServer using a POST request.

public class Record
{
    public SubRecord Inner { get; set; }
    public int Id { get; set; }
}

public class SubRecord
{
    public string Value { get; set; }
    public decimal Number { get; set; }
}

string sourceFile = "res/Examples/Records.json";
PrintFile(sourceFile);
var server = WireMockServer.Start();
server
    .Given(
        Request.Create()
        .WithPath("/test")
        .UsingPost())
    .RespondWith(
        Response.Create()
            .WithStatusCode(200)
            .WithHeader("Content-Type", "text/json")
            .WithBody(File.ReadAllText(sourceFile))
    );

var source = new JsonSource<Record>() {
    ResourceType = ResourceType.Http,
    Uri = @$"http://localhost:{server.Port}/test"
};
source.HttpRequestMessage.Method = HttpMethod.Post;
source.HttpRequestMessage.Headers.AcceptEncoding.Add(
    new System.Net.Http.Headers.StringWithQualityHeaderValue("*"));
source.HttpRequestMessage.Properties.Add("Content-Type", "text/json");
var dest = new MemoryDestination<Record>();

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

Console.WriteLine("Request finished with status code:" + source.HttpResponseMessage.StatusCode.ToString());
foreach (var row in dest.Data)
    Console.WriteLine($"Received Id: {row.Id}, Value1: {row.Inner.Value}, Value2: {row.Inner.Number}");

/* Output:
Content of file 'Records.json'
---
{
    "CreationDate": "2022-01-01",
    "Content": [
    {
        "Id": 1,
        "Inner": {
        "Value": "Test1",
        "Number": 1.1
        }
    },
    {
        "Id": 2,
        "Inner": {
        "Number": 1.2
        }
    },
    {
        "Id": 3,
        "Inner": {
        "Value": "Test3",
        "Number": 1.3
        }
    }
    ],
    "Confidential": false
}
---
Request finished with status code:OK
Received Id: 1, Value1: Test1, Value2: 1.1
Received Id: 2, Value1: , Value2: 1.2
Received Id: 3, Value1: Test3, Value2: 1.3
*/

Reading a simple String Array

This example reads data from a simple json that holds only an array of strings and converts them into a dynamic object for further processing.

PrintFile("res/Examples/StringArray.json");

var source = new JsonSource<string>();
source.Uri = "res/Examples/StringArray.json";
var trans = new RowTransformation<string, ExpandoObject>();
trans.TransformationFunc = s => {
    dynamic r = new ExpandoObject();
    r.Text = s;
    return r;
};
var dest = new MemoryDestination();

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

Network.Execute(source);

foreach (dynamic row in dest.Data) {
    Console.WriteLine(row.Text);
}

/* Output:

Content of file 'StringArray.json'
---
[ "Hansen", "Jensen", "Olsen", "Petersen" ]
---
Hansen
Jensen
Olsen
Petersen
*/

Edit this page on GitHub

Excel Source

Memory Source

Recipes

ETLBox

Title here

Json Source

Shared code

Read json file

With POCO

With json attributes

JSON Path in json properties

Reading into dynamic

JSON Path with dynamic

Redirecting errors

Reading multiple Uris

Different resource types

Reading from Azure blob storage

Reading from web service

Reading a simple String Array

Json Source

Shared code#

Read json file#

With POCO#

With json attributes#

JSON Path in json properties#

Reading into dynamic#

JSON Path with dynamic#

Redirecting errors#

Reading multiple Uris#

Different resource types#

Reading from Azure blob storage#

Reading from web service#

Reading a simple String Array#

Shared code

Read json file

With POCO

With json attributes

JSON Path in json properties

Reading into dynamic

JSON Path with dynamic

Redirecting errors

Reading multiple Uris

Different resource types

Reading from Azure blob storage

Reading from web service

Reading a simple String Array