How to serialize json in newsofton on recurring datatable value in ASP.NET Core-CodePudding

So I've been trying to serialize a json that I have so that the defining value I get from my dataset is a "key" in my Json array and that all the values are within that key if that makes sense.

I'm new to this and I haven't found any other post that would help me with this so hopefully one of you knows the answer.

So the code that I'm using to try and achieve this is as following:

string query = @"
                  select Country.Name, City.Name from world.country 
                  inner join world.city on world.country.Code = world.city.CountryCode
                  where CountryCode = @CountryCode";

  DataSet dataSet = new DataSet("dataSet");
  dataSet.Namespace = "NetFrameWork";
  DataTable table = new DataTable();
  dataSet.Tables.Add(table);

  string sqlDataSource = _configuration.GetConnectionString("WorldAppConnection");
  MySqlDataReader myReader;
  using (MySqlConnection mySqlConnection = new MySqlConnection(sqlDataSource))
  {
    mySqlConnection.Open();
    using (MySqlCommand mySqlCommand = new MySqlCommand(query, mySqlConnection))
    {
      mySqlCommand.Parameters.AddWithValue("@CountryCode", CountryCode);
      myReader = mySqlCommand.ExecuteReader();
      table.Load(myReader);
      myReader.Close();
      mySqlConnection.Close();
    }
  }
  dataSet.AcceptChanges();
  string json = JsonConvert.SerializeObject(dataSet, Formatting.Indented);
  return json;
}

Now if I perform this query in SQL a small snippet of what the result is:

France | Paris
France | Marseille
France | Lyon
France | Toulouse
...

Now what this code results in as a Json is:

{
  "Table1": [
    {
    "Name": "France",
      "Name1": "Paris"
    },
    {
    "Name": "France",
      "Name1": "Marseille"
    },
    {
    "Name": "France",
      "Name1": "Lyon"
    },
    {
    "Name": "France",
      "Name1": "Toulouse"
    },
    {
    "Name": "France",
      "Name1": "Nice"
    },
    {
    "Name": "France",
      "Name1": "Nantes"
    },
    {
    "Name": "France",
      "Name1": "Strasbourg"
    }
  ]
}

As you can see it keeps on repeating the France field. However the result I would want to have is something like:

{
  "Table1": [ 
    {
     "France":[   
       {
         "Name1": "Paris"
       },
       {
         "Name1": "Marseille"
       },
       {
         "Name1": "Lyon"
       },
       {
         "Name1": "Toulouse"
       },
       {
         "Name1": "Nice"
       },
       {
         "Name1": "Nantes"
       },
       {
         "Name1": "Strasbourg"
       }
     ]
   }
 ]
}

Is there any way to achieve this in any way?

Thanks in advance.

CodePudding user response：

try this

var jsonParsed = JObject.Parse(json); 

var result = new
{
Table1 = jsonParsed["Table1"].GroupBy(x => x["Name"])
       .Select(x => new Dictionary<string, string[]> { { (string)x.Key, 
       x.Select(i => (string)i["Name1"]).ToArray() }})
};

result

{
  "Table1": [
    {
      "France": [
        "Paris",
        "Marseille",
        "Lyon",
        "Toulouse",
        "Nice",
        "Nantes",
        "Strasbourg"
      ]
    }
  ]
}

CodePudding user response：

Before serialize the object, you can use