Plain string to JSON conversion challenges

Some time ago I've got a task that seemed to be very simple. In few words:

• there is a database table.
• one of columns contain configuration section name.
• another column contains string representation of JSON object used to configure some external entity.
• the data for that table is entered manually or by 3rd parties but needs to be read by our API. So, the task is to select records for the particular projectId (another column), compose JSON object from those pieces and output it as 'application/json' content in API response. The structure of the output must be like this:

    [
      "sectionName1" : {
        -- JSON value 1 from db table --
      },
      "sectionName2" : {
        -- JSON value 2 from db table --
      }
      ... and so on
    ]

The title image for this article shows example actual source data and result for the task.

Initially the task did not look as something difficult. But started working on it I faced weird circumstance with the fact that dotnet escapes double-quote symbols from the string. Quick straightforward idea of how to do the conversion was to read records into Dictionary<string, string> and then just let the API to output this. But the result was a bit of unusable:

{
  "areaConfiguration": "{\"enabledAreas\": [\"stakeholders\",\"engagement\",\"reports\",\"land_referencing\",\"land_access\"]}",
  "programmeMapping": "{\"layerToProgramme\": {\"75612\": 1,\"75613\": 2,\"75614\": 3}}"
}

And this is quite understandable, because json deserializer has no knowledge about those values apart from those are the strings.

Next approach to solve this was to replace all internal double quotes with the single quotes. But that did not work either:

{
  "areaConfiguration": "{'enabledAreas': ['stakeholders','engagement','reports','land_referencing','land_access']}",
  "programmeMapping": "{'layerToProgramme': {'75612': 1,'75613': 2,'75614': 3}}"
}

So, the problem came — how to deserialize a string of json into some c# object, to let serializer understand it properly at the moment of output and provide usable json on API response? All the recommendations that I found from the web were about strongly typed deserealization, but in my case the format of the json in the source string is unknown in advance. It can even be not flat, and properties can have values of any type — number, bool or string. Two options to experiment with came to my mind: anonymous objects or dynamics. I started with the latter and eventually came up with the working solution.

So how it works in my case? Three steps are there.

Step 1 (repository) — reading from the db into the collection of strongly typed objects:

public async Task<RepositoryResultData<IEnumerable<GetSettings>>> GetAllSettingsAsync()
{
	const string sql = "SELECT Name, Configuration FROM dbo.ProjectSetting";

	using var dataAccessor = await _dataAccessorFactory.CreateDataAccessorAsync();

	var configuration = await dataAccessor.QueryAsync<GetSettings>(sql);

	return RepositoryResultData<IEnumerable<GetSettings>>.Succeed(configuration);
}

public class GetSettings
{
	public string Name { get; set; }

	public string Configuration { get; set; }
}

Step 2 (service) — some string manipulation with every record from the collection:

public async Task<ServiceResultResponse<string>> GetAllSettingsAsync()
{
	var result = await _settingsRepository.GetAllSettingsAsync();

	var join = string.Join(", ", result.Data.Select(x => $"\"{x.Name}\": {x.Configuration}"));

	return ServiceResultResponse<string>.Succeed("{" + join + "}");
}

Step 3 (controller) — deserealizing into the dynamic and make ContentResult of it:

[HttpGet]
[Produces("application/json")]
public async Task<IActionResult> GetSettings()
{
	var result = await _settingsService.GetAllSettingsAsync();

	var d = JsonConvert.DeserializeObject<dynamic>(result.Response);

	return new ContentResult { Content = d.ToString(), ContentType = "application/json; charset=utf-8", StatusCode = 200 };
}

And that did the trick!

I'm interested if you can share your experience on this? Could it be done differently?

Thanks for reading!