JSON to datatable ignore arrays

Question

I'm trying to write a generic method that will take a JSON string and convert it to a DataTable (the best it can).

It works fine until it comes across a JSON object that has an array as one of the properties.

What I would like to do, is ignore the properties that are arrays and convert as best it can.

I have an example fiddle that explains what I am trying to do: https://dotnetfiddle.net/cgkNov

public static void Main()
{
    const string JsonObject = @"[{Id:""672af604-495b-4dc0-933e-6c55f56bef82"",""Name"":""1"",""MyArray"":[]}]";
    const string EndResultJsonObject = @"[{Id:""672af604-495b-4dc0-933e-6c55f56bef82"",""Name"":""1""}]";
    var settings = new JsonSerializerSettings
    {
        Error = (obj, args) =>
        {
            var context = args.ErrorContext;

            context.Handled = true;
        }
    };
    var a = JsonConvert.DeserializeObject<DataTable>(JsonObject, settings);

    Console.WriteLine(JsonConvert.SerializeObject(a));

    a = JsonConvert.DeserializeObject<DataTable>(EndResultJsonObject, settings);

    Console.WriteLine(JsonConvert.SerializeObject(a));
}

Thanks, Alex

Possible duplicate of [Convert JSON to DataTable](https://stackoverflow.com/questions/11981282/convert-json-to-datatable) — aloisdg, Jul 27 '18 at 14:19

score 2 · Accepted Answer · answered Jul 27 '18 at 15:09

If you want to skip array, you can use something like this. It is just an example, and probably you should improve it. Add custom converter:

public class SkipArrayConverter : JsonConverter
{
    public override bool CanConvert(Type objectType)
    {
        return !objectType.IsArray;
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        JArray array = JArray.Load(reader);
        DataTable dataTable = new DataTable();
        int i = 0;
        foreach (JToken token in array.Children())
        {
            var dataRow = dataTable.NewRow();
            if (token.Type != JTokenType.Object) continue;

            if (i == 0)
            {
                SetColumns(dataTable, token);
                dataRow = dataTable.NewRow();
                i++;
            }

            foreach (JToken jToken in token)
            {
                if (((JProperty)jToken).Value.Type != JTokenType.Array)
                {
                    string name = ((JProperty)jToken).Name;
                    object value = ((JValue)((JProperty)jToken).Value).Value;
                    dataRow[name] = value;
                }
            }

            dataTable.Rows.Add(dataRow);
        }
        return dataTable;
    }

    private void SetColumns(DataTable dt, JToken token)
    {
        foreach (JToken jToken in token)
        {
            if (((JProperty)jToken).Value.Type != JTokenType.Array)
            {
                string name = ((JProperty)jToken).Name;
                object value = ((JValue)((JProperty)jToken).Value).Value;
                Type valueType = value.GetType();
                dt.Columns.Add(name, valueType);
            }
        }
    }

    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        throw new NotImplementedException();
    }
}

After this you can use it:

JsonSerializerSettings settings = new JsonSerializerSettings
{
    Converters = new List<JsonConverter> { new SkipArrayConverter() }
};
const string JsonObject = @"[{Id:""672af604-495b-4dc0-933e-6c55f56bef82"",Name:""1"",MyArray:[{}]}]";
const string EndResultJsonObject = @"[{Id:""672af604-495b-4dc0-933e-6c55f56bef82"",""Name"":""1""}]";
var a = JsonConvert.DeserializeObject<DataTable>(JsonObject, settings);

Console.WriteLine(JsonConvert.SerializeObject(a));

a = JsonConvert.DeserializeObject<DataTable>(EndResultJsonObject, settings);

Console.WriteLine(JsonConvert.SerializeObject(a));

This was exactly what I was after! Thank you – Alex Driver Jul 30 '18 at 12:09 — Alex Driver, Jul 30 '18 at 12:09

score 0 · Answer 2 · answered Jul 27 '18 at 14:15

I noticed how you are using double " to wrap fields' keys and values. In your current sample code for JsonObject you are not wrapping the empty array [] in quotes. I tried changing it to

const string JsonObject = @"[{Id:""672af604-495b-4dc0-933e-6c55f56bef82"",""Name"":""1"",""MyArray"":""[]""}]";

That solves the issue with your current data atleast. Since you're using a Third party library to do the actual parsing of JSON, you have limited control over the process.

Maybe what you can try is creating a minified version of DataTable class and then use this new version to parse the JSON string into an object of the new MiniDT class. Let me know if you need some help with that.

Cheers.

score 0 · Answer 3 · answered Jul 27 '18 at 14:22

0

use this

const string JsonObject = @"[{Id:""672af604-495b-4dc0-933e-6c55f56bef82"",""Name"":""1"",""MyArray"":[{}]}]";

Array should be declared like this

MyArray:[{}]

answered Jul 27 '18 at 14:22

Ravi

1,157
1
9
19

score 0 · Answer 4 · answered Jul 27 '18 at 14:43

0

Alex,

I found this answer made by Brian Rogers: Removing empty array members from a JSON string

I think this is what you where searching for. Try it out and if it isn't then we can create a code using it as an inspiration.

answered Jul 27 '18 at 14:43

acarlstein

1,799
2
13
21

JSON to datatable ignore arrays

4 Answers4