© 2026 Hedgehog Software, LLC

TwitterGitHubDiscord
More
CommunitiesDocsAboutTermsPrivacy
Search
Star
Setup for Free
Drizzle TeamDT
Drizzle Team•2y ago
xmnlz

error while trying to update jsonb via jsonb_set

export const guilds = pgTable('guilds', {
  id: text('id').primaryKey().notNull(),
  isEnabled: boolean('is_enabled').default(false).notNull(),
  preferredLanguage: text('preferred_language')
    .default('en-US')
    .notNull()
    .$type<SupportedLanguages>(),
  protoSettings: jsonb('proto_settings')
    .notNull()
    .$type<GuildProtoSettings>()
    .default({
      general: {
        minimumWeeklyQuota: 300,
      },
      roles: {
        adminRoleId: null,
        moderatorRoleId: null,
        curatorRoleId: null,
        coachRoleId: null,
        eventsmodeRoleId: null,
      },
      channels: {
        eventsmodeCategoryId: null,
        announceEventChannelId: null,
        startedEventCategoryId: null,
      },
    }),
});
export const guilds = pgTable('guilds', {
  id: text('id').primaryKey().notNull(),
  isEnabled: boolean('is_enabled').default(false).notNull(),
  preferredLanguage: text('preferred_language')
    .default('en-US')
    .notNull()
    .$type<SupportedLanguages>(),
  protoSettings: jsonb('proto_settings')
    .notNull()
    .$type<GuildProtoSettings>()
    .default({
      general: {
        minimumWeeklyQuota: 300,
      },
      roles: {
        adminRoleId: null,
        moderatorRoleId: null,
        curatorRoleId: null,
        coachRoleId: null,
        eventsmodeRoleId: null,
      },
      channels: {
        eventsmodeCategoryId: null,
        announceEventChannelId: null,
        startedEventCategoryId: null,
      },
    }),
});

query
 await db
      .update(guilds)
      .set({
        protoSettings: sql`jsonb_set(${guilds.protoSettings}, '$.roles', jsonb('{"adminRoleId": ${adminRoleId}, "moderatorRoleId": ${moderatorRoleId}, "curatorRoleId": ${curatorRoleId}, "coachRoleId": ${coachRoleId}, "eventsmodeRoleId": ${eventsmodeRoleId}}'))`,
      })
      .where(eq(guilds.id, ctx.guild.id));
 await db
      .update(guilds)
      .set({
        protoSettings: sql`jsonb_set(${guilds.protoSettings}, '$.roles', jsonb('{"adminRoleId": ${adminRoleId}, "moderatorRoleId": ${moderatorRoleId}, "curatorRoleId": ${curatorRoleId}, "coachRoleId": ${coachRoleId}, "eventsmodeRoleId": ${eventsmodeRoleId}}'))`,
      })
      .where(eq(guilds.id, ctx.guild.id));

error:
41 |     rej = reject
42 |   }).catch((err) => {
43 |     // replace the stack trace that leads to `TCP.onStreamRead` with one that leads back to the
44 |     // application that created the query
45 |     Error.captureStackTrace(err)
46 |     throw err
       ^
error: invalid input syntax for type json
 code: "22P02"

      at D:\js-projects\vpitashka\node_modules\pg-pool\index.js:46:3
      at processTicksAndRejections (native:1:1)
41 |     rej = reject
42 |   }).catch((err) => {
43 |     // replace the stack trace that leads to `TCP.onStreamRead` with one that leads back to the
44 |     // application that created the query
45 |     Error.captureStackTrace(err)
46 |     throw err
       ^
error: invalid input syntax for type json
 code: "22P02"

      at D:\js-projects\vpitashka\node_modules\pg-pool\index.js:46:3
      at processTicksAndRejections (native:1:1)

using bun
Drizzle TeamJoin
The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!
11,879Members
Resources

Similar Threads

Was this page helpful?
Recent Announcements

Similar Threads

update a nested jsonb field
Drizzle TeamDTDrizzle Team / help
3y ago
Error while push:sqlite after update
Drizzle TeamDTDrizzle Team / help
3y ago
typescript error when trying to update a column
Drizzle TeamDTDrizzle Team / help
11mo ago
How to set a GIN index on the JSONB column?
Drizzle TeamDTDrizzle Team / help
2y ago