jsonb_set function using Dapper

Question

in PostgreSQL if I write this query, everything works fine:

UPDATE states
SET data = jsonb_set(data, '{test}', '"test"')
WHERE object_id = 'string'
RETURNING state_type, object_id, state_id, data

But if I want to write similar query in .NET using Dapper, then I get problem:

Npgsql.PostgresException (0x80004005): 42883: function jsonb_set(jsonb, text, jsonb) does not exist

Here is my C# code:

var sql = @"UPDATE states
        SET data = jsonb_set(data, @key, CAST(@data as jsonb))
        WHERE object_id = @objectId";
        
var result = await dbConnection.QueryFirstAsync<State>(sql, new
{
    objectId = "string",
    key = "{key}",
    data = "test"
});

Answer 1

Function jsonb_set accepts arguments as (jsonb, text[], jsonb) not (jsonb, text, jsonb). So I added ::text[] to @key:

var sql = @"UPDATE states
    SET data = jsonb_set(data, @key::text[], CAST(@data as jsonb))
    WHERE object_id = @objectId";