ETLBox ETLBox ETLBox Buy Try Buy

JSON

The ETLBox JSON Connector makes it easy to read and write JSON data from files, web services, and cloud storage. Whether you're working with structured objects or dynamic data, it gives you the flexibility to map nested JSON, handle arrays, and customize how your data is processed.

Overview

The ETLBox.Json package provides JsonSource and JsonDestination to work with the JSON format. Additionally, it includes the JsonPathConverter and ExpandoJsonPathConverter, which allow mapping nested JSON structures into flat objects using JSONPath expressions.

ETLBox uses Json.NET by Newtonsoft   , a powerful library for handling JSON data.

Shared Features

Common functionalities such as resource types (files, HTTP, Azure Blob), streaming, and row modifications are shared across all streaming connectors. See Shared Features for details.

For detailed configuration options, refer to:

JsonSource

JsonSource reads JSON data from files, web services (HTTP), or cloud storage and converts it into structured objects or dynamic objects.

Example: Reading a Simple JSON Response

Assume the following JSON response from http://test.com:

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

This ETLBox code reads the JSON and maps it to MySimpleRow objects:

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

JsonSource<MyRow> source = new JsonSource<MyRow>("http://test.com/", ResourceType.Http);
MemoryDestination<MyRow> dest = new MemoryDestination<MyRow>();

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

This reads the JSON array, deserializes each entry into MyRow, and sends it into MemoryDestination.

Reading Nested JSON Arrays

By default, ETLBox starts reading JSON after detecting the first array ([ symbol). If the array is nested within an object, it is automatically extracted.

Example JSON with a nested array:

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

ETLBox automatically detects the array and reads its content. If multiple arrays exist, you can specify which one to process:

JsonSource<MySimpleRow> source = new JsonSource<MySimpleRow>("data.json", ResourceType.File);
source.PropertyNameOfDataArray = "array";

This ensures that only the specified array is processed.

Using JsonPathConverter

Sometimes, JSON contains nested properties, requiring custom mappings. The JsonPathConverter allows mapping nested JSON fields into POCOs using JSONPath expressions.

Example JSON input:

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

POCO class using JsonPathConverter:

[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>("data.json", ResourceType.File);

Here, "Column2.Value" is extracted from the JSON and mapped to Col2 in MySimpleRow.

Working with Dynamic Objects

ETLBox supports dynamic object processing using ExpandoObject.

Example: Transforming JSON dynamically into a csv file

JsonSource source = new JsonSource() {
  Uri = "source.json",
  ResourceType = ResourceType.File
};

RowTransformation<ExpandoObject> trans = new RowTransformation<ExpandoObject>(
    row => {
        dynamic r = row as ExpandoObject;
        r.Col1 = r.Value1;
        r.Col2 = r.Value2;
        return r;
    }
);

CsvDestination dest = new CsvDestination("converted.csv");

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

This allows dynamic JSON processing, enabling ETLBox to handle unknown schemas.

Using ExpandoJsonPathConverter

For dynamic JSON structures, the ExpandoJsonPathConverter allows defining JSONPath expressions for ExpandoObject properties.

Example JSON input:

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

Using ExpandoJsonPathConverter:

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

List<JsonProperty2JsonPath> pathLookups = new List<JsonProperty2JsonPath>() {
    new JsonProperty2JsonPath() {
        SearchPropertyName = "Column2",
        JsonPath = "$.Value",
        OutputPropertyName = "Col2"
    },
    new JsonProperty2JsonPath() {
        SearchPropertyName = "Column2",
        JsonPath = "$['Id']",
        OutputPropertyName = "Id"
    }
};

source.JsonSerializer.Converters.Add(new ExpandoJsonPathConverter(pathLookups));

This dynamically extracts Column2.Value and Column2.Id into Col2 and Id properties.

JsonDestination

JsonDestination writes data into JSON files or sends JSON to web services.

Example: Writing Data to JSON

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

var source = new MemorySource<MyRow>();
source.Data = new List<MyRow>() {
    new MyRow() { Col1 = 1, Col2="Test1"},
    new MyRow() { Col1 = 2, Col2="Test2"},
    new MyRow() { Col1 = 3, Col2="Test3"},
};
JsonDestination<MySimpleRow> dest = new JsonDestination<MySimpleRow>("test.json", ResourceType.File);
source.LinkTo(dest);
Network.Execute(source);

This generates the following JSON output:

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

Customizing JSON Serialization

Like JsonSource, JsonDestination exposes the JsonSerializer, allowing advanced customization:

dest.JsonSerializer.NullValueHandling = NullValueHandling.Ignore;
dest.JsonSerializer.Formatting = Formatting.Indented;

This ensures better formatting and excludes null values from the output.

Edit this page on GitHub
Prev
Excel
Next
Parquet