Nested JSON data to datatable dynamically C#

Question

{
    "STATUS": "OK",
    "projects": [
        {
            "startDate": "",
            "last-changed-on": "2019-01-03T11:46:14Z",
            "logo": "",
            "created-on": "2018-12-12T10:04:47Z",
            "privacyEnabled": false,
            "status": "active",
            "boardData": {},
            "replyByEmailEnabled": true,
            "harvest-timers-enabled": false,
            "description": "",
            "category": {
                "color": "",
                "id": "",
                "name": ""
            },
            "id": "322852",
            "overview-start-page": "default",
            "start-page": "projectoverview",
            "integrations": {
                "xero": {
                    "basecurrency": "",
                    "countrycode": "",
                    "enabled": false,
                    "connected": "NO",
                    "organisation": ""
                },
                "sharepoint": {
                    "account": "",
                    "foldername": "root",
                    "enabled": false,
                    "folder": "root"
                },
                "microsoftConnectors": {
                    "enabled": false
                },
                "onedrivebusiness": {
                    "account": "",
                    "foldername": "root",
                    "enabled": false,
                    "folder": "root"
                }
            },
            "defaults": {
                "privacy": ""
            },
            "notifyeveryone": false,
            "filesAutoNewVersion": false,
            "defaultPrivacy": "open",
            "tasks-start-page": "default",
            "starred": false,
            "announcementHTML": "",
            "isProjectAdmin": true,
            "name": "Project 2",
            "company": {
                "is-owner": "1",
                "id": "78494",
                "name": "MCG Company"
            },
            "endDate": "",
            "announcement": "",
            "show-announcement": false,
            "subStatus": "current",
            "tags": []
        },
        {
            "startDate": "",
            "last-changed-on": "2018-12-11T17:52:57Z",
            "logo": "",
            "created-on": "2018-11-26T11:11:00Z",
            "privacyEnabled": false,
            "status": "active",
            "boardData": {},
            "replyByEmailEnabled": true,
            "harvest-timers-enabled": false,
            "description": "",
            "category": {
                "color": "",
                "id": "",
                "name": ""
            },
            "id": "321041",
            "overview-start-page": "default",
            "portfolioBoards": [
                {
                    "card": {
                        "id": "4771"
                    },
                    "board": {
                        "id": "544",
                        "name": "Project Implementations",
                        "color": "#F39C12"
                    },
                    "column": {
                        "id": "1573",
                        "name": "Go Live",
                        "color": "#F1C40F"
                    }
                }
            ],
            "start-page": "projectoverview",
            "integrations": {
                "xero": {
                    "basecurrency": "",
                    "countrycode": "",
                    "enabled": false,
                    "connected": "NO",
                    "organisation": ""
                },
                "sharepoint": {
                    "account": "",
                    "foldername": "root",
                    "enabled": false,
                    "folder": "root"
                },
                "microsoftConnectors": {
                    "enabled": false
                },
                "onedrivebusiness": {
                    "account": "",
                    "foldername": "root",
                    "enabled": false,
                    "folder": "root"
                }
            },
            "defaults": {
                "privacy": ""
            },
            "notifyeveryone": false,
            "filesAutoNewVersion": false,
            "defaultPrivacy": "open",
            "tasks-start-page": "default",
            "starred": false,
            "announcementHTML": "",
            "isProjectAdmin": true,
            "name": "Project One",
            "company": {
                "is-owner": "1",
                "id": "78494",
                "name": "MCG Company"
            },
            "endDate": "",
            "announcement": "",
            "show-announcement": false,
            "subStatus": "current",
            "tags": []
        }
    ]
}

This is the JSON response that I'm getting from an app, and there are a lot of other API gets that are returning the same kind of response (nested), so this has to be done dynamically as the user is adding API calls from a config file, so I cannot make pre-made classes with gets and sets. My goal is to transform this data into a datatable to be inserted into a database When I see a nested column, my goal is to have the parent column name attached to it with an "_" ex: category_id = "" or integrations_xero_basecurrency = "", etc..

This is the code that I used to tabulate the data, but in the code it's only taking the column if it's a JValue (key and value), and I'm not able for the life of me to create a proper loop that will do the trick.

    public DataTable Tabulate(string jsonContent)
    {
        var jsonLinq = JObject.Parse(jsonContent);

        // Find the first array using Linq
        var srcArray = jsonLinq.Descendants().Where(d => d is JArray).First();
        //Console.WriteLine("extarcted data:" + srcArray);
        var trgArray = new JArray();
        foreach (JObject row in srcArray.Children<JObject>())
        {
            var cleanRow = new JObject();
            foreach (JProperty column in row.Properties())
            {
                // Only include JValue types
                if (column.Value is JValue)
                {
                    cleanRow.Add(column.Name, column.Value);
                }
            }

            trgArray.Add(cleanRow);
        }

        DataTable dt = JsonConvert.DeserializeObject<DataTable>(trgArray.ToString());            

        return dt;
    }

Brian Rogers · Accepted Answer · 2020-07-09 08:16:46Z

4

How about something like this:

public DataTable Tabulate(string jsonContent)
{
    var jsonLinq = JObject.Parse(jsonContent);

    // Find the first array using Linq
    var arrayProp = jsonLinq.Properties().First(p => p.Value is JArray);
    var srcArray = (JArray)arrayProp.Value;

    // Set up a regex consisting of the array property name and subscript 
    // (e.g. "projects[0]."), which we will strip off
    var regex = new Regex($@"^{arrayProp.Name}\[\d+\]\.");

    // Flatten each object of the original array 
    // into new objects and put them in a new array 
    var trgArray = new JArray(
        srcArray.Children<JObject>()
                .Select(row => new JObject(
                     row.Descendants()
                        .OfType<JProperty>()
                        .Where(p => p.Value is JValue)
                        .Select(p => new JProperty(
                            regex.Replace(p.Value.Path, "").Replace(".", "_"), 
                            p.Value
                        ))
                ))
        );

    // Convert the new array to a DataTable
    DataTable dt = trgArray.ToObject<DataTable>();

    return dt;
}

Working demo: https://dotnetfiddle.net/yrmcSQ

edited Jul 9, 2020 at 8:16

answered Jul 9, 2020 at 8:09

Brian Rogers

130k31 gold badges315 silver badges315 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

George Over a year ago

It worked perfectly for the most part, some of the columns came like this though portfolioBoards[0]_board_color with the [0] and [1]

George Over a year ago

I think this is happening for the empty ones that have no data "portfolioBoards":[]

Brian Rogers Over a year ago

I was wondering about whether your JSON would have any arrays in it, and if so how you would want to handle those. The sample you posted did not have a portfolioBoards array.

George Over a year ago

Excuse my ignorance, I'm new to this. Your question is valid and I didn't think about how to deal with arrays. As I can see it, I have two options, first would be to ignore them (ignore if the node is of type array), or deal with them the same way as in adding all the children in the column name, which would create a lot of problems: column name would be huge, repetition of nodes in an array, etc.. Would love to hear your thoughts about it.. I have added the portfolioBoards array to the sample above..

Brian Rogers Over a year ago

Ignoring them would be the simplest option, but it depends on whether you actually need that data or not. If you do need it, then you'd have to decide how you want to capture it. One idea would be to store child DataTables in the columns that have arrays. So for example, the main column would be portfolioBoards and it would contain a DataTable having columns card_id, board_id, board_name, board_color, etc. for each row. But if your goal is to insert everything into a database, then this won't help, because you won't be able to store a table within a table in the DB.

|

Collectives™ on Stack Overflow

Nested JSON data to datatable dynamically C#

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related