Question 306178 in PostgreSQL Question posted in
The official documentation can be found here.

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

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"]
  }
]
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    The issue is due to the JSON array being read as a string. To fix this, you need to check if the data type of a column is a JSON array and then parse it accordingly.

    for (int i = 0; i < reader.FieldCount; i++)
{
    if (reader.GetFieldType(i) == typeof(string) && reader.GetString(i).StartsWith("["))
    {
        row[reader.GetName(i)] = JsonConvert.DeserializeObject(reader.GetString(i));
    }
    else
    {
        row[reader.GetName(i)] = reader.GetValue(i);
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search