Handling a Dictionary of primitive types in a Postgres JSONB column in ASP.NET Core

Question

I have a JSONB column in Postgres that contains a Dictionary with string keys and values that are either strings or numbers. So one example would be the following:

{
    "test": "string",
    "test2": 123,
    "test3": 7.2
}

If I put this as a Dictionary<string, object> on the EF Core Model, I can save this without issues in the database, everything works automatically. But if I retrieve it, Newtonsoft JSON by default doesn't serialize this into something useful, I only get some ValueKind properties and the actual values are missing:

  {
    "test": {
      "valueKind": 3
    },
    "test2": {
      "valueKind": 4
    },
    "test3": {
      "valueKind": 4
    }
  }

I suspect I have to write a custom serializer for this, but I would have thought something like this is common enough in Web APIs that there would be a more convenient way. But so far I was unable to find any setting that would fix this conversion.

Is there a way to get ASP.NET and Newtonsoft to just serialize that JSONB column back to the kind of format in which I put this in? The information on which keys are numbers and which are strings is still in the JSON, so it should not need help from me to output something reasonable here.

Alternatively, is there a better way to handle this kind of data than Dictionary<string, object>?

Answer 1

This issue seems to be caused by mixing JSON libraries. EF Core uses system.text.json by default, so if you enable Newtonsoft in ASP.NET Core the data in this case gets passed through two different libraries.

Switching this to only system.text.json fixes the issue, and a Dictionary<string, object> gets automatically serialized as you would expect.