Creating nested JSON with data from SQLDataReader

Question

I am new to .NET and trying to achieve what shape-json does in the Node world.

Copied from the shape-json npm page:

Given the following array of objects:

var input = [
  {pid: 1, contributor: 'jdalton', projectID: 1, projectName: 'lodash'},
  {pid: 1, contributor: 'jdalton', projectID: 2, projectName: 'docdown'},
  {pid: 1, contributor: 'jdalton', projectID: 3, projectName: 'lodash-cli'},
  {pid: 2, contributor: 'contra',  projectID: 4, projectName: 'gulp'},
  {pid: 3, contributor: 'phated',  projectID: 4, projectName: 'gulp'},
]

You can apply a schema like this:

var scheme = {
  "$group[contributors](pid)": {
    "id": "pid",
    "name": "contributor",
    "$group[projects](projectID)": {
      "id": "projectID",
      "name": "projectName"
    }
  }
};

To get a nested JSON like this:

{
  "contributors": [
    {
      "id": 1,
      "name": "jdalton",
      "projects": [
        {
          "id": 1,
          "name": "lodash"
        },
        {
          "id": 2,
          "name": "docdown"
        },
        {
          "id": 3,
          "name": "lodash-cli"
        }
      ]
    },
    {
      "id": 2,
      "name": "contra",
      "projects": [
        {
          "id": 4,
          "name": "gulp"
        }
      ]
    },
    {
      "id": 3,
      "name": "phated",
      "projects": [
        {
          "id": 4,
          "name": "gulp"
        }
      ]
    }
  ]
}

In my case, I have a product hierarchy (product class -> product subclass -> product) which I am trying to represent as a nested JSON.

Background:

I am getting the data from a MySQL table and reading it into a MySqlDataReader.
Following this SO question, I have been able to serialize the data into a JSON

I am clueless on how to create a nested JSON like the example above. Please advise.

dbc · Accepted Answer · 2019-10-18T01:58:35.297

MySqlDataReader implements the IDataReader interface, so we can use a LINQ lambda expression to iterate through its rows and transform them into an appropriate .Net data model. Then afterwards the data model can be serialized to JSON using json.net.

First, grab the following extension method from this answer by Joel Coehoorn:

public static class DataReaderExtensions
{
    // Adapted from this answer https://stackoverflow.com/a/1202973
    // To https://stackoverflow.com/questions/1202935/convert-rows-from-a-data-reader-into-typed-results
    // By https://stackoverflow.com/users/3043/joel-coehoorn
    public static IEnumerable<T> SelectRows<T>(this IDataReader reader, Func<IDataRecord, T> select)
    {
        while (reader.Read())
        {
            yield return select(reader);
        }
    }
}

And then you can generate your required JSON as follows:

using (IDataReader reader = cmd.ExecuteReader())
{
    var query = reader
        .SelectRows(r => 
                    new
                    {
                        id = r.GetInt64(r.GetOrdinal("pid")),
                        name = r["contributor"].ToString(),
                        project = new {id = r.GetInt64(r.GetOrdinal("projectID")), name = r["projectName"].ToString() },
                    }
                   )
        .GroupBy(r => new { r.id, r.name })
        .Select(g => new { g.Key.id, g.Key.name, projects = g.Select(i => i.project) });

    var json = JsonConvert.SerializeObject(new { contributors = query }, Formatting.Indented);

    Console.WriteLine(json);
}

Notes:

I am converting the data reader's rows to a data model comprised of anonymous type objects, but you could use explicitly typed objects for your final results, if you prefer.
GroupBy is used to group all contributions by contributor id and name.
Select is used to project elements of an enumerable into a new form.
This approach completely skips the intermediate steps of serializing the raw query results to JSON, then parsing and restructuring that initial JSON.
The query itself is lazy so be sure to evaluate it before disposing of the data reader. In the code above Json.NET actually performs the evaluation during serialization, inside the call to JsonConvert.SerializeObject.
I wasn't sure whether the id columns were 32-bit or 64-bit integers, so I assumed the larger, for safety.
The code assumes none of the record values are null.

Demo fiddle here that mocks up the above using a DataTable and DataTableReader.

This is beyond helpful! Thanks a lot. – KalC Oct 18 '19 at 02:55 — KalC, Oct 18 '19 at 02:55

Creating nested JSON with data from SQLDataReader

1 Answers1