Can't insert into JSONB column from string

ServiceStack.OrmLite PostgreSQL
1

I have the following classs defined:

public class JobTask
{
    [AutoId]
    public Guid Id { get; set; }
    public int Type { get; set; }
    [PgSqlJsonB]
    public string Config { get; set; }
}

[Route("/api/tasks", "POST")]
public class CreateJobTask
{
    public int Type { get; set; }
    public string Config { get; set; }
}

Posting from POSTMAN:

{
    "type": 1,
    "config": {
        "name": "test"
    }
}

Service:

    public async Task<object> Post(CreateJobTask request)
    {
        try
        {
            var jobTask = request.ConvertTo<JobTask>();
            await Db.InsertAsync(jobTask);
            return jobTask.ConvertTo<CreateJobTaskResponse>();
        }
        catch (Exception ex)
        {

            throw;
        }
    }

Error:

22P02: invalid input syntax for type json

What am I missing?

2

Your subject is the answer to your own issue, [PgsqlJson*] is for serializing objects serialized as JSON, not escaped serialized strings.

Please read the view the usage of PgsqlJson in the docs and look at the actual string value you’re trying to insert, it’s for JSON serialized complex POCO types in a JSON field not for a serialized string that needs to be persisted as an escaped string which none of PostgreSQL’s JSON functions are going to support.

Use the actual complex type that matches the JSON you’re trying to insert:

    [PgSqlJsonB]
    public Config Config { get; set; }

//...

public class Config 
{
     public string Name { get; set; }
}

Or if you don’t want to use a typed POCO, use an untyped Dictionary<string,string> instead.

Otherwise remove the [PgSqlJsonB] attribute, as you’re only inserting an escaped serialized string.