JSON columns — DB — Socigy.OpenSource

JSON columns

Store structured data in PostgreSQL JSONB columns using [RawJsonColumn] or [JsonColumn].

updated 3 May 20261 min readv0.1.82

Two column kinds

Attribute	Storage	Use when
`[RawJsonColumn]`	Raw JSON string	You serialise/deserialise manually, or you only need to read the text
`[JsonColumn(typeof(TContext))]`	Typed serialisation via `JsonSerializerContext`	You want strongly-typed reads/writes with AOT compatibility

Both produce a JSONB column in PostgreSQL.

`[RawJsonColumn]`

The property type must be string?. The value is stored and read back as a plain JSON string.

[Table("events")]
public partial class Event
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [RawJsonColumn]
    public string? Metadata { get; set; }
}

var ev = new Event
{
    Metadata = """{"source": "web", "ip": "127.0.0.1"}""",
};
await ev.Insert().WithConnection(conn).ExcludeAutoFields().ExecuteAsync();

`[JsonColumn]`

[JsonColumn(typeof(TContext))] takes a JsonSerializerContext subclass. The source generator emits calls to JsonSerializer.Serialize and JsonSerializer.Deserialize using the given context, making the code AOT-safe.

Define the context

[JsonSerializable(typeof(UserProfile))]
public partial class MyDbJsonContext : JsonSerializerContext { }

Annotate the property

[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [JsonColumn(typeof(MyDbJsonContext))]
    public UserProfile? Profile { get; set; }
}

public class UserProfile
{
    public string? AvatarUrl { get; set; }
    public string? Bio { get; set; }
}

Read and write

var user = new User
{
    Profile = new UserProfile { AvatarUrl = "https://...", Bio = "Hello" },
};
await user.Insert().WithConnection(conn).ExcludeAutoFields().ExecuteAsync();

await foreach (var u in User.Query().WithConnection(conn).ExecuteAsync())
{
    Console.WriteLine(u.Profile?.Bio);
}

Migration DDL

Both attributes produce the same DDL:

"metadata" JSONB

If the property is not declared nullable (T?), the column becomes JSONB NOT NULL.

NOTE

The migration tool does not emit a DEFAULT for JSON columns unless you also add [Default]. A non-nullable JSON column without a default will reject INSERT statements that omit the column.