DataConnector | ComponentOne
ADO.NET provider for JSON / JSON Data Models
In This Topic
    JSON Data Models
    In This Topic

    The JSON provider supports three types of data models and allows you to model object arrays as tables. It parses the JSON document and identifies the object arrays or it defines JsonPath to specify the object arrays to model as tables. The data models are as follows:

    Let us explore more about these data models in the following sections.

    Top level Document Model

    In the top-level document model, the JSON provider gets default access to the top-level elements using the top-level document view of the JSON data. This allows the JSON provider to return nested elements in aggregate, as single columns.

    For this model, you need to specify "Document" as a data model for the JSON provider using the DataModel property as showcased in the following code. This allows the provider to scan a single object array which, is by default the top-level object array, and return nested elements in the aggregate, as single columns. In the following example, raw JSON data is used from the json_bookstore.json file. Furthermore, if you want to specify an object array rather than the top-level, you can set it using the JsonPath property.

    C#
    Copy Code 
    static void ReadTopLevelDocument()
{
    //Create connection string
    string documentConnectionString = $"Data Model=Document;Uri='json_bookstore.json';Json Path='$.bookstore.books'";

    Console.WriteLine("Query all Accounts...");
    //Fetch data
    using(var con = new C1JsonConnection(documentConnectionString))
    {
        con.Open();
        var table = con.GetSchema("columns", new string[] { "books" });
        ShowDataTable(table);

        var cmd = con.CreateCommand();
        //Provide command
        cmd.CommandText = "Select * From books";
        var reader = cmd.ExecuteReader();
    }
}

//Display Table
static void ShowDataTable(DataTable table, int length = 25)
{
    foreach (DataColumn col in table.Columns)
    {
        Console.Write("{0,-" + length + "}", col.ColumnName);
    }
    Console.WriteLine();

    foreach (DataRow row in table.Rows)
    {
        foreach (DataColumn col in table.Columns)
        {
            if (col.DataType.Equals(typeof(DateTime)))
                Console.Write("{0,-" + length + ":d}", row[col]);
            else if (col.DataType.Equals(typeof(decimal)))
                Console.Write("{0,-" + length + ":C}", row[col]);
            else
                Console.Write("{0,-" + length + "}", row[col]);
        }
       Console.WriteLine();
    }
}

    Here is the output of the above code:

    top level model

    Flattened Model

    In the Flattened model, data is flattened into a single table making it the best option to choose when it comes to accessing the complete JSON data. Here, the JSON provider uses streaming and parses the data once per query.

    For this model, you need to specify "FlattenedDocuments" as a data model for the JSON provider using the DataModel property as showcased in the following code. In the following example, raw JSON data is used from the json_bookstore.json file. Due to this, the JSONPath values act similarly to SQL JOIN. Hence, the nested sibling JSONPath values are considered as SQL CROSS JOIN.

    C#
    Copy Code 
    static void ReadFlattened()
{
    string flattenedConnectionString = $"Data Model=FlattenedDocuments;Uri='json_bookstore.json';Json Path='$.bookstore.books;$.bookstore.books.readers'";

    Console.WriteLine("Query all Accounts...");
    using (var con = new C1JsonConnection(flattenedConnectionString))
    {
        con.Open();

        var table = con.GetSchema("columns", new string[] { "books" });
        ShowDataTable(table);

        var cmd = con.CreateCommand();
        cmd.CommandText = "Select * From books";
        var reader = cmd.ExecuteReader();
    }
}      
//Display Table
static void ShowDataTable(DataTable table, int length = 25)
{
    foreach (DataColumn col in table.Columns)
    {
        Console.Write("{0,-" + length + "}", col.ColumnName);
    }
    Console.WriteLine();

    foreach (DataRow row in table.Rows)
    {
        foreach (DataColumn col in table.Columns)
        {
            if (col.DataType.Equals(typeof(DateTime)))
                Console.Write("{0,-" + length + ":d}", row[col]);
            else if (col.DataType.Equals(typeof(decimal)))
                Console.Write("{0,-" + length + ":C}", row[col]);
            else
                Console.Write("{0,-" + length + "}", row[col]);
        }
       Console.WriteLine();
    }
}

    Here is the output of the above code:

    flattened model

    Relational Model

    Based on your requirements, you can configure ADO.NET Provider for JSON to create a relational model of the data. This can be useful when you need to work with your JSON data in existing reporting, BI, and ETL tools that expect a relational data model.

    For this model, you need to specify "Relational" as a data model for the JSON provider using the DataModel property as showcased in the following code. In the following example, raw JSON data is used from the json_bookstore.json file. This allows the provider to control any JOINs by the query. Hence, whenever you perform a JOIN query, the source or the JSON file is queried for the nested array included in the query.

    C#
    Copy Code 
    //Relational Model
static void ReadRelational()
{
    string relationConnectionString = $"Data Model=Relational;Uri='json_bookstore.json';Json Path='$.bookstore.books;$.bookstore.books.readers'";
      
    Console.WriteLine("Query all Accounts...");
    using (var con = new C1JsonConnection(relationConnectionString))
    {
        con.Open();
        var table = con.GetSchema("columns", new string[] { "books" });
        ShowDataTable(table);

        var cmd = con.CreateCommand();
        cmd.CommandText = "Select * From books";
        var reader = cmd.ExecuteReader();
    }
}       
//Display Table
static void ShowDataTable(DataTable table, int length = 25)
{
    foreach (DataColumn col in table.Columns)
    {
        Console.Write("{0,-" + length + "}", col.ColumnName);
    }
    Console.WriteLine();

    foreach (DataRow row in table.Rows)
    {
        foreach (DataColumn col in table.Columns)
        {
            if (col.DataType.Equals(typeof(DateTime)))
                Console.Write("{0,-" + length + ":d}", row[col]);
            else if (col.DataType.Equals(typeof(decimal)))
                Console.Write("{0,-" + length + ":C}", row[col]);
            else
                Console.Write("{0,-" + length + "}", row[col]);
        }
       Console.WriteLine();
    }
}

    Here is the output of the above code:

    relational model