Error in merging same JSON child keys and converting to datatable

[manishkumar457]

Sample JSON: @"[ { "Players": [ { "player": "a" }, { "player": "b" }, { "player": "c" }, { "player": "d" }, { "player": "e" } ], "Team": "Title" } ]"

Expected JSON: [ { "Players": [ { "player": "a,b,c,d,e" } ], "Team": "Title" } ]

Output: "[{ "Players_0_player": "a", "Players_1_player": "b", "Players_2_player": "c", "Players_3_player": "d", "Players_4_player": "e", "Team": "Title" }]"

[Cinchoo]

Here is how you can extract the json to datatable as expected

string json = @"[
  {
    ""Players"": [
      {
        ""player"": ""a""
      },
      {
        ""player"": ""b""
      },
      {
        ""player"": ""c""
      },
      {
        ""player"": ""d""
      },
      {
        ""player"": ""e""
      }
    ],
    ""Team"": ""Title""
  }
]";
using (var r = ChoJSONReader.LoadText(json)
    .WithField("Players", valueConverter: o => String.Join(",", (IEnumerable<object>)o), jsonPath: "$..player")
    .WithField("Team")
    )
{
    var dt = r.AsDataTable();
    Console.WriteLine(dt.Dump());
}

[manishkumar457]

Wow. Thanks a lot. It worked as expected. But while on another use case, there was a parent node as a substitute with the player as child nodes and the program created an array with substitute player in Players too.

Input JSON: [ { "Players": [ { "player": "a" }, { "player": "b" }, { "player": "c" }, { "player": "d" }, { "player": "e" } ], "Substitute": [ { "player": "f" }, { "player": "g" }, { "player": "h" }, { "player": "i" }, { "player": "j" } ], "Team": "Title" } ] Expected JSON: [ { "Players": [ { "player": "a,b,c,d,e" } ], "Substitute": [ { "player": "f,g,h,i,j" } ], "Team": "Title" } ]

Output: "Players_player": "a,b,c,d,e,f,g,h,i,j" "Substitute_player":"a,b,c,d,e,f,g,h,i,j" Team:"Title"

Update: I did get the proper output after changing jsonpath to $.Players[*].player.

Is there any way to have a looping within the ".WithField" which the data can be accessed through a string array. Eg. string[] a = {"Players","Substitute"}; string[] b = {"player","player"}; // How to loop these .methods()? Is the below one the right way as it didn't work only a[0] using (var r = ChoJSONReader.LoadText(json)

)

{ for(var i=0;I<a.Length;i++){ r = r .WithField("a[i]", valueConverter: o => String.Join(",", (IEnumerable

[Cinchoo]

Well, u need JSONPath to select nodes for each field. Here is one link for your reference

https://support.smartbear.com/alertsite/docs/monitors/api/endpoint/jsonpath.html

using (var r = ChoJSONReader.LoadText(json)
    .WithField("Players", valueConverter: o => String.Join(",", (IEnumerable<object>)o), jsonPath: "$..Players[*].player")
    .WithField("Substitute", valueConverter: o => String.Join(",", (IEnumerable<object>)o), jsonPath: "$..Substitute[*].player")
    .WithField("Team")
    )
{
    var dt = r.AsDataTable();
    Console.WriteLine(dt.Dump());
}

[manishkumar457]

Hi, Recently experienced one more error:

When the first data from JSON doesn't have property then all other subsequent data is termed null.

Eg.

JSON: [
  {
    "Players": [
      {
        "player": "a"
      },
      {
        "player": "b"
      }
    ],
    "Team": "Title"
  },
  {
    "Players": [
      {
        "player": "a"
      },
      {
        "player": "b"
      },
      {
        "player": "c"
      }
    ],
    "Substitute": [
      {
        "player": "d"
      },
      {
        "player": "e"
      }    
    ],
    "Team": "Title"
  }
]

The result will make the Substitute null even if there are data in subsequent rows. Tried setting MaxScanRows as 2 and it didn't work.

[Cinchoo]

There is bug in dump() method. But the datatable has all the expected values.

Here is the sample code

            using (var r = new ChoJSONReader("issue147.json")
                .WithField("Players", valueConverter: o => String.Join(",", (IEnumerable<object>)o), jsonPath: "$..Players[*].player")
                .WithField("Substitute", valueConverter: o => o != null ? String.Join(",", (IEnumerable<object>)o) : null, jsonPath: "$..Substitute[*].player")
                .WithField("Team")
                )
            {
                foreach (var rec in r)
                {
                    Console.WriteLine(rec.Dump());
                }
            }

[manishkumar457]

Fixed it. There was another exception where the data retrieved were less than expected when the JSON data is empty.

Eg. JSON: [ { "Extra": "", "Players": [ { "player": "a" }, { "player": "b" }, { "player": "c" } ], "Substitute": [ { "player": "d" }, { "player": "e" }
], "Team": "Title" } ]

Output: Only Extra is shown. Tried IgnoreFieldValueMode and it didn't work.

[Cinchoo]

I'm not able to reproduce it, this is code I tried to extract values, it works

            using (var r = new ChoJSONReader("issue147.json")
                .WithField("Extra")
                .WithField("Players", valueConverter: o => String.Join(",", (IEnumerable<object>)o), jsonPath: "$..Players[*].player")
                .WithField("Substitute", valueConverter: o => o != null ? String.Join(",", (IEnumerable<object>)o) : null, jsonPath: "$..Substitute[*].player")
                .WithField("Team")
                )
            {
                foreach (var rec in r)
                {
                    Console.WriteLine(rec.Dump());
                }
            }

Post your sample code if u can