Rust and PostgreSQL JSONB Columns Done Right With Serde
Store and query JSON in PostgreSQL JSONB from Rust: sqlx Json<T> typed columns, containment and path operators, partial updates, GIN indexes and when a column beats JSON.
JSONB gives PostgreSQL a document column with indexes and operators, and sqlx lets you read it as a typed struct. The skill is knowing what to put in it. Everything compiles on Rust 1.90 with edition 2024, sqlx 0.8 (features postgres, json, runtime-tokio), serde 1 and PostgreSQL 17.
The table and the type
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
kind TEXT NOT NULL,
payload JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX events_payload_gin ON events USING GIN (payload jsonb_path_ops);use serde::{Deserialize, Serialize};
use sqlx::{types::Json, FromRow, PgPool};
#[derive(Debug, Serialize, Deserialize)]
pub struct LoginPayload {
pub user_id: i64,
pub ip: String,
#[serde(default)]
pub mfa: bool,
}
#[derive(Debug, FromRow)]
pub struct Event {
pub id: i64,
pub kind: String,
pub payload: Json<LoginPayload>,
}
pub async fn record(pool: &PgPool, p: LoginPayload) -> sqlx::Result<i64> {
let row: (i64,) = sqlx::query_as(
"INSERT INTO events (kind, payload) VALUES ('login', $1) RETURNING id",
)
.bind(Json(p))
.fetch_one(pool)
.await?;
Ok(row.0)
}
pub async fn get(pool: &PgPool, id: i64) -> sqlx::Result<Event> {
sqlx::query_as::<_, Event>("SELECT id, kind, payload FROM events WHERE id = $1")
.bind(id)
.fetch_one(pool)
.await
}Json(p) wraps the value for binding; reading gives Json<LoginPayload>, which dereferences to the inner struct (event.payload.user_id). #[serde(default)] on mfa means rows written before the field existed still decode.
Querying inside the document
use sqlx::PgPool;
pub async fn logins_for_user(pool: &PgPool, user_id: i64) -> sqlx::Result<Vec<i64>> {
sqlx::query_scalar(
"SELECT id FROM events WHERE payload @> $1::jsonb ORDER BY id DESC LIMIT 50",
)
.bind(serde_json::json!({ "user_id": user_id }).to_string())
.fetch_all(pool)
.await
}
pub async fn from_ip(pool: &PgPool, ip: &str) -> sqlx::Result<i64> {
sqlx::query_scalar("SELECT count(*) FROM events WHERE payload ->> 'ip' = $1")
.bind(ip)
.fetch_one(pool)
.await
}@> (contains) uses the GIN index above. ->> returns text and cannot use it; for frequent lookups add an expression index (CREATE INDEX ON events ((payload ->> 'ip'))). Check with EXPLAIN that the plan uses the index you expect.
Partial updates
pub async fn mark_mfa(pool: &sqlx::PgPool, id: i64) -> sqlx::Result<u64> {
let done = sqlx::query(
"UPDATE events SET payload = jsonb_set(payload, '{mfa}', 'true'::jsonb) WHERE id = $1",
)
.bind(id)
.execute(pool)
.await?;
Ok(done.rows_affected())
}jsonb_set(target, path, new_value) changes one key atomically in SQL, avoiding a read-modify-write round trip that could lose a concurrent update. payload || '{"a":1}'::jsonb merges keys.
Choosing JSONB or columns
| Put it in | When |
|---|---|
| A column | filtered, sorted, joined, unique, or constrained |
| JSONB | optional, varying, or foreign shaped data you mostly read back whole |
| Both | a few hot fields as columns, the rest in JSONB |
A JSONB column cannot hold a foreign key or a CHECK on a field without extra work, and every query on a field in it needs an index you wrote. If you find yourself indexing five paths, the data wants columns (schema design).
Evolving the shape
Add fields with defaults, never repurpose a key's meaning, and keep an explicit version field for large changes. A deserialization failure on an old row is a runtime error in one request, so test the struct against a few real rows. Typed queries and decoding errors are covered in FromRow and query_as, and the Serde side in serde_json basics.
