How can I store enums in "adjacently-tagged" style?

[inklesspen]

Serde offers the adjacently-tagged representation for enums. As an example:

#[derive(Debug, Serialize, Deserialize)]
#[serde(tag = "t", content = "c")]
pub enum ThingKind {
    Animal { friendly: bool, name: String },
    Vegetable,
    Mineral { hardness: u8 },
}

let dog = ThingKind::Animal {
    friendly: true,
    name: "Rover".to_string(),
};
let cabbage = ThingKind::Vegetable;
let diamond = ThingKind::Mineral { hardness: 10 };

serde-json serializes these three instances as:

{"t":"Animal","c":{"friendly":true,"name":"Rover"}}
{"t":"Vegetable"}
{"t":"Mineral","c":{"hardness":10}}

What I would like to do is to split off the tag and content: that is, I would like to store an enum like this as a pair of varchar and jsonb columns. (Let's say that the majority of the time, I only care about whether something is an animal, vegetable, or mineral, and not what the thing's friendliness or hardness may be. But sometimes I do need the full enum variant.)

pub struct Agent {
    username: String,
    favorite_thing: ThingKind,
}

CREATE TABLE agents (
    username VARCHAR PRIMARY KEY,
    favorite_thing VARCHAR NOT NULL,
    favorite_thing_payload JSONB
);
INSERT INTO agents (username, favorite_thing, favorite_thing_payload)
    VALUES ('alice', 'Animal', '{"friendly":true,"name":"Rover"}'::jsonb);
INSERT INTO agents (username, favorite_thing, favorite_thing_payload)
    VALUES ('bob', 'Vegetable', NULL);
INSERT INTO agents (username, favorite_thing, favorite_thing_payload)
    VALUES ('carol', 'Mineral', '{"hardness":10}'::jsonb);

I know that I could query this using an intermediate struct that has separate fields for the enum variant name and the "payload", and then I could use serde_json::Value to put those fields together and deserialize them into a ThingKind instance. But that is a lot of stuff to do by hand.

(Note that I want to use the query macros, so the FromRow trait is not going to be helpful here at all.)

[sunnypatell]

@inklesspen sqlx's Decode trait operates on a single column, so there's no way to map two columns into one Rust type directly. the query_as! macro generates struct literals (MyStruct { col1, col2, ... }) so every column must map 1:1 to a field.

cleanest approach: query!() + .map() with a reassembly helper

since your enum already has serde #[serde(tag = "t", content = "c")], you can roundtrip through serde to reconstruct it from the two columns:

fn reassemble_thing(tag: &str, payload: Option<serde_json::Value>) -> Result<ThingKind, Error> {
    let json = match payload {
        Some(c) => serde_json::json!({"t": tag, "c": c}),
        None => serde_json::json!({"t": tag}),
    };
    serde_json::from_value(json).map_err(|e| e.into())
}

fn split_thing(thing: &ThingKind) -> (String, Option<serde_json::Value>) {
    let v = serde_json::to_value(thing).unwrap();
    let tag = v["t"].as_str().unwrap().to_string();
    let content = v.get("c").cloned();
    (tag, content)
}

query:

let agent = sqlx::query!(
    "SELECT username, favorite_thing, favorite_thing_payload FROM agents WHERE username = $1",
    username
)
.fetch_one(&pool)
.await?
.map(|row| Agent {
    username: row.username,
    favorite_thing: reassemble_thing(&row.favorite_thing, row.favorite_thing_payload).unwrap(),
})

insert:

let (tag, payload) = split_thing(&agent.favorite_thing);
sqlx::query!(
    "INSERT INTO agents (username, favorite_thing, favorite_thing_payload) VALUES ($1, $2, $3)",
    agent.username, tag, payload
)
.execute(&pool)
.await?;

this keeps compile-time SQL checking from the query! macro while the reassemble_thing helper is ~5 lines and reuses serde's own adjacently-tagged deserialization. no per-variant match arms needed.

ref: query! macro docs | serde adjacently-tagged | sqlx #1089 (maintainer recommends .map() pattern)

[inklesspen]

Thanks! I ended up using Postgres's JSON support to do it in SQL, though.

[sunnypatell]

@inklesspen nice, that is a solid approach. doing the JSON assembly/disassembly in SQL via JSONB_BUILD_OBJECT and JSONB_STRIP_NULLS keeps the Rust side clean since you just pass the raw JSON and let Postgres handle the tag/payload split. both approaches work well, yours has the advantage of keeping all the serialization logic in one place (the queries) rather than needing Rust helpers.

[inklesspen]

I ended up doing this on the Postgres side, using Postgres to disassemble and reassemble the JSON.

INSERT INTO agents (username, favorite_thing, favorite_thing_payload)
SELECT 
    username,
    favorite_thing_json ->> 'tag' AS favorite_thing,
    favorite_thing_json -> 'payload' AS favorite_thing_payload
FROM (SELECT $1 AS username, $2::JSONB as favorite_thing_json);

SELECT
    username
    JSONB_STRIP_NULLS(
        JSONB_BUILD_OBJECT(
            'tag', favorite_thing,
            'payload', favorite_thing_payload)) AS "favorite_thing!: Json<ThingKind>"
FROM
    agents;

And that works great with what serde expects.