How would I get all the query results from a SELECT using Npgsql

Question

I need to get the results of a query as JSON but I've been having issue if a column is a JSONarray: it gets converted to string

The current code I'm using:

var result = new List<Dictionary<string, object>>();

await using (var cmd = new NpgsqlCommand("SELECT * FROM global", conn))
await using (var reader = await cmd.ExecuteReaderAsync())
{
    while (await reader.ReadAsync())
    {
        var row = new Dictionary<string, object>();
        for (int i = 0; i < reader.FieldCount; i++)
        {
            row[reader.GetName(i)] = reader.GetValue(i);
        }
        result.Add(row);
    }
}


var jsonResult = JsonConvert.SerializeObject(result, Newtonsoft.Json.Formatting.Indented);
Console.WriteLine(jsonResult);

The output:

[
  {
    "id": 1,
    "banned": false,
    "strikes": "[]"
  },
  {
    "id": 2,
    "banned": false,
    "strikes": "[\"nooob\"]"
  }
]

The EXCEPTED output:

[
  {
    "id": 1,
    "banned": false,
    "strikes": []
  },
  {
    "id": 2,
    "banned": false,
    "strikes": ["nooob"]
  }
]

Answer 1

It seems that by default Npgsql will map JSON column to string, so you will need to parse it to JSON so serializer will treat it correctly (otherwise it you will get the value "double" encoded as you experience). Something along these lines (not tested):

for (int i = 0; i < reader.FieldCount; i++)
{
    var name = reader.GetName(i);
    var value = reader.GetValue(i);
    value = name == "strikes" ? JArray.Parse(value) : value;
    row[name] = value;
}

Note that according to docs you can change the default mapping. For example by using NpgsqlJsonNetExtensions (since you are using the Newtonsoft Json.NET serializer), so you don't need to manually do this:

To use Json.NET, add the Npgsql.Json.NET package to your project, and enable the plugin.

var dataSourceBuilder = new NpgsqlDataSourceBuilder(...);
dataSourceBuilder.UseJsonNet();
await using var dataSource = dataSourceBuilder.Build();

or

NpgsqlConnection.GlobalTypeMapper.UseJsonNet();

Or you can consider providing mapping to some defined type - see the POCO mapping docs.