Question: How to use JSONB columns (with PostgreSQL)

[jan]

In a PostgreSQL database with a JSONB column, how should we define this field in an entity class?

This works:

    [NpgsqlDbType(NpgsqlDbType.Jsonb)]
    public string Metadata { get; init; }

Is there any way to automatically get the structured data?

Or do we have to seriaize and deserialize this ourselves?

    public Dictionary<string, object?> MetadataDictionary {
        get {
            return JsonSerializer.Deserialize<Dictionary<string, object?>>(Metadata) ?? new Dictionary<string, object?>();
        }
        init {
            Metadata = Newtonsoft.Json.JsonConvert.SerializeObject(value);
        }
    }

For example, in Dapper you can define & register a type handler:

public class DapperDictionaryTypeHandler : SqlMapper.TypeHandler<Dictionary<string, object>> {
    public override void SetValue(IDbDataParameter parameter, Dictionary<string, object>? value) {
        parameter.Value = value == null ? null : Newtonsoft.Json.JsonConvert.SerializeObject(value);
    }

    public override Dictionary<string, object>? Parse(object? value) {
        return value == null ? null : JsonSerializer.Deserialize<Dictionary<string, object>>((string) value);
    }
}

And then you can define the entity field directly, without annotations, as

    public Dictionary<string, object?> Metadata { get; init; }

Would be great if you could add an example best practice to the docs.

Thank you!

[jan]

Also, it would be great to be able to achieve the equivalent of [NpgsqlDbType(NpgsqlDbType.Jsonb)] without an annotation, similar to how RepoDbAutoMapper works.

Context: in one of our projects we try to use a Clean Architecture project structure and we can't (well, it feels wrong to) use NpgsqlDbType inside of the Domain project.