DT
Drizzle Team•2y 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();
    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),
        };
    },
);
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?
1 Reply
Zetax
ZetaxOP•2y ago
Addition: I realise I can just get the "profile" and then replace values of it with nodejs and then update it again, but that seems very ineffecient

Did you find this page helpful?