How to convert a json string into jsonb type using postgres in c#

Question

I'm working with .NET core 3.1

I have a table on my postgres database with 2 columns

+---------+--------+
| id      | bigint |
+---------+--------+
| content | jsonb  |
+---------+--------+

And i am trying to place on the column content json strings like this:

"{\"Id\":247764684382208,\"Created\":\"2020-06-08T00:00:00Z\",\"Content\":\"Ad in consequat duis qui sit eu nulla magna consectetur id anim\",\"ReceivedTimestamp\":\"0001-01-01T00:00:00\",\"MessageEventId\":0}"

In my code i do the following:

the variable result is an IColletion with a (key=long,value=string) dictionary, which will have values like the one i posted above

string connString = "Host=localhost;Port=5432;Username=postgres;Password=postgres;Database=testdb";

NpgsqlConnection conn = new NpgsqlConnection(connString);

conn.Open();

var valuesTableSql = string.Join(",", Enumerable.Range(0, result.Count).Select(i => $"(@p1{i}, @p2{i})"));

using (var cmd = new NpgsqlCommand($"INSERT INTO \"table\" (\"id\", \"content\") VALUES {valuesTableSql};", conn)) 
{
 for (int i = 0; i < result.Messages.Count; ++i)
  {
   cmd.Parameters.AddWithValue($"p1{i}", result.ElementAt(i).Key);
   cmd.Parameters.AddWithValue($"p2{i}", result.Messages.ElementAt(i).Value);
 }
}

When i run the code i will get an exception like this:

Unhandled exception. Npgsql.PostgresException (0x80004005): 42804: column "content" is of type jsonb but expression is of type text

I'm new to postgres, and in my requirements i can't use Entity framework. What do i need to do there to convert the Json string into that jsonb type of postgres?

GMB · Accepted Answer · 2020-06-08 18:19:45Z

2

Postgres generally avoids implicit conversion: from its perspective, you are attempting to insert a string in a jsonb column.

I think that you that you need an explicit cast in your query. Changing the following line of code should be good enough:

var valuesTableSql = string.Join(",", Enumerable.Range(0, result.Count).Select(i => $"(@p1{i}, @p2{i})::jsonb"));

answered Jun 8, 2020 at 18:19

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nmaster88 Over a year ago

I only changed the place of jsonb on your line. var valuesTableSql = string.Join(",", Enumerable.Range(0, result.Count).Select(i => $"(@p1{i}, @p2{i} :: json )")); and it seems to work. thanks.

Shay Rojansky · Accepted Answer · 2020-06-09 18:04:43Z

2

As @GMB wrote, PostgreSQL won't implicitly convert a string to a jsonb. However, rather than applying a cast inside SQL, you can also instruct Npgsql to send the parameter typed as jsonb:

cmd.Parameters.AddWithValue("p1", NpgsqlDbType.Jsonb, "{....}");

answered Jun 9, 2020 at 18:04

Shay Rojansky

16.9k3 gold badges44 silver badges73 bronze badges

Collectives™ on Stack Overflow

How to convert a json string into jsonb type using postgres in c#

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related