DT
Drizzle TeamZetax

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?
Z
Zetax•301d 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
Want results from more Discord servers?
Add your server
More Posts
auto updated_atis there a way to automatically update the updatedAt column of a postgres table ?Just bringing attention to this potential issue: use of wrong schema name in drizzle kit outputhttps://github.com/drizzle-team/drizzle-orm/issues/890 Just wondering if this is intended behavior oFulltext index MySQLHow can I define a fulltext index with MySQL? there is no fulltextIndex available in mysql-core.Anyone who can give a example of this