Drizzle TeamDT
Drizzle Team•3y ago
Zetax

Updating jsonb objects with Drizzle?

Hey there, i'm currently working on migrating my database and code from MongoDB with Mongoose to Postgres with Drizzle and have encountered a question that I couldnt find answered on the docs.

I query JSON like this in my MongoDB database 
    Profiles.findOneAndUpdate({ accountId: user.accountId }, { $set: { "profiles.athena.items": allItems.items } }, { new: true }, (err, doc) => {
        if (err) console.log(err);

    }).lean();


Which updates the profiles.athena.items object in my database, now instead of having one big profiles blob, I created seperate columns for each profile in my drizzle schema and database. These are: 
export const profiles = pgTable(
    "profiles",
    {
        id: serial("id").primaryKey(),
        created: timestamp("created"),
        accountId: varchar("accountId", { length: 256 }),
        athena: jsonb("athena"),
        campaign: jsonb("campaign"),
        collectionBookPeople: jsonb("collection_book_people"),
        collectionBookSchematics: jsonb("collection_book_schematics"),
        collections: jsonb("collections"),
        commonCore: jsonb("common_core"),
        commonPublic: jsonb("common_public"),
        creative: jsonb("creative"),
        metadata: jsonb("metadata"),
        outpost: jsonb("outpost"),
        profilezero: jsonb("profilezero"),
        theater: jsonb("theater"),
    },
    (table) => {
        return {
            accountIdIdx: index("account_id_idx").on(table.accountId),
        };
    },
);


As I'm very new to drizzle and SQL in general, I'm not really sure how to approach querying this now. With MongoDB it was just a single findOneAndUpdate query, but in Drizzle i'm not sure how I can update just a single object inside my JSONB data. Could anyone give me an idea for how this could be done?
Was this page helpful?