Skip to content
orm
ENRU

JSON and JSONB

Reading into a document, and why every reader comes back nullable.

The column#

A jsonb column maps to whatever Go type you declare for it — commonly a map, sometimes a struct:

go
//orm:table public.users
type User struct {
    ID       int64          `orm:"pk,identity"`
    Settings map[string]any `orm:"pgtype:jsonb"`
    Profile  *Profile       `orm:"pgtype:jsonb"`
}

jsonb is the one to use. json stores the original text including whitespace and duplicate keys; jsonb stores a parsed structure, which is what indexes and containment operators need.

Everything is a free function#

The readers and tests are free functions rather than methods, because either side can be a column or an expression. They take an Optional, so a non-nullable column is lifted with orm.Opt:

go
meta := orm.Opt(Users.Settings)

They produce Predicate[Composed] or an Expression, so they belong in a composed query.

Tests#

go
orm.JSONHasKey(meta, "plan")                    // ?
orm.JSONHasAnyKeys(meta, "plan", "tier")        // ?|
orm.JSONHasAllKeys(meta, "plan", "tier")        // ?&
orm.JSONContains(meta, orm.Val(v))              // @>
orm.JSONContainedBy(meta, orm.Val(v))           // <@
orm.JSONPathExists(meta, "$.billing.tier")      // @?
orm.JSONMatches(meta, "$.age > 18")             // @@

JSONPathExists and JSONMatches take SQL/JSON path syntax, which is the expressive one: $.items[*].price, filters, wildcards.

Readers#

go
orm.JSONGet(meta, "billing")            // ->   a key,   returns jsonb
orm.JSONText(meta, "plan")              // ->>  a key,   returns text
orm.JSONIndex(meta, 0)                  // ->   an index of an array
orm.JSONIndexText(meta, 0)              // ->>  an index
orm.JSONPathGet(meta, "billing", "tier")  // #>  a path, returns jsonb
orm.JSONPathText(meta, "billing", "tier") // #>> a path, returns text
orm.JSONArrayLength(meta)               // jsonb_array_length
orm.JSONTypeOf(meta)                    // jsonb_typeof

Every one of them is nullable, and that is not caution. -> over a key that is not there is NULL. ->> over a non-existent path is NULL. jsonb_typeof of a NULL document is NULL. A document is a shape nobody validated, so a reader that promised a non-null result would be lying about the common case.

That is why you cast rather than compare directly:

go
age := orm.CastNull(orm.JSONPathText(meta, "profile", "age"), orm.Integer)
// Expression[*int32, *int32]

Writers#

go
orm.JSONSet(Users.Settings, []string{"billing", "tier"}, v, true)
orm.JSONInsert(Users.Settings, []string{"tags", "0"}, v, false)
orm.JSONStripNulls(Users.Settings)

JSONSet's last argument is create_missing — whether to add the key when the path does not exist. JSONInsert's is insert_after. Both are booleans in PostgreSQL's own signature, and they are passed through rather than renamed, because a reader checking the manual should find the same argument.

They return a Value, so they belong in an update:

go
db.Users.Update().
    Set(Users.Settings.SetExpr(orm.JSONSet(Users.Settings, []string{"plan"}, newPlan, true))).
    Where(Users.ID.Eq(id)).
    Exec(ctx)

Indexing#

A containment query wants a GIN index:

go
//orm:index users_settings_gin_idx (Settings) using gin

jsonb_path_ops is smaller and faster for @> alone but supports fewer operators — declare it as an expression index when you want it.

Worked examples#

Feature flags on an account#

go
settings := orm.Opt(Accounts.Settings)

// Accounts that have opted into the beta.
orm.Compose(pool, shape).From(Accounts.Source()).
    Where(orm.JSONContains(settings, orm.Val(map[string]any{"beta": true})))

// Accounts where the key was never set at all — a different question.
orm.Compose(pool, shape).From(Accounts.Source()).
    Where(orm.Not(orm.JSONHasKey(settings, "beta")))

Contains asks about the value; HasKey asks whether anyone decided. A flag that is absent and a flag that is false are different states, and this is how you keep them apart.

An event payload#

Reading a nested value out and comparing it as a number:

go
payload := orm.Opt(Events.Payload)

amount := orm.CastNull(orm.JSONPathText(payload, "order", "total"), orm.Integer)

var big = orm.Project2(
    orm.Of(Events.ID), amount,
    func(id int64, total *int32) Big { return Big{id, total} },
)

orm.Compose(pool, big).From(Events.Source()).
    Where(orm.JSONPathExists(payload, "$.order.total")).
    All(ctx)

The cast is where the type is decided. ->> gives text whatever the document holds, and a comparison against a number has to say so.

A profile document, edited in place#

go
db.Profiles.Update().
    Set(Profiles.Doc.SetExpr(orm.JSONSet(Profiles.Doc, []string{"contact", "email"}, newEmail, true))).
    Where(Profiles.ID.Eq(id)).
    Exec(ctx)

true is create_missing: add contact.email when the path is not there. With false the update is a no-op on a document that never had it.

Shape questions#

go
orm.JSONTypeOf(orm.Opt(Events.Payload))       // "object", "array", "string"…
orm.JSONArrayLength(orm.Opt(Events.Items))    // *int32, NULL if not an array