使用npgsql将字符串存储为jsonb列

CREATE TABLE my_table (
    id serial PRIMARY KEY,
    data jsonb
);

using Npgsql;

var connString = "Host=my_host;Username=my_user;Password=my_password;Database=my_database";
using var conn = new NpgsqlConnection(connString);
conn.Open();

var jsonData = new {
    name = "John",
    age = 30,
    hobbies = new[] { "reading", "gaming" }
};
var jsonString = JsonConvert.SerializeObject(jsonData);

var sql = "INSERT INTO my_table (data) VALUES (@jsonData)";
using var cmd = new NpgsqlCommand(sql, conn);
cmd.Parameters.AddWithValue("jsonData", jsonString);
cmd.ExecuteNonQuery();

var sql = "SELECT data FROM my_table WHERE id = @id";
using var cmd = new NpgsqlCommand(sql, conn);
cmd.Parameters.AddWithValue("id", 1);
using var reader = cmd.ExecuteReader();
if (reader.Read())
{
    var jsonData = reader.GetString(0);
    var jsonObject = JsonConvert.DeserializeObject(jsonData);
    // 处理查询到的JSON数据
}