© 2026 Hedgehog Software, LLC

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

Replacing hardcoded text value in `jsonb`

Hi everyone

I have this data in my
jsonb
jsonb
column of my Postgres database:
[
  {
    "insuranceGTACode": "groupama",
    "excludedPostcodes": [],
    "supportedPostcodes": [],
    "excludedDepartments": [],
    "supportedDepartments": ["75","78","92","95","60"]
  }
]
[
  {
    "insuranceGTACode": "groupama",
    "excludedPostcodes": [],
    "supportedPostcodes": [],
    "excludedDepartments": [],
    "supportedDepartments": ["75","78","92","95","60"]
  }
]


And I have this current query that I search for a row based on a condition that depends on the json value above.
This query works fine and returns what is expected:

return db
    .select({
      email: wrecker.contactEmail,
    })
    .from(wrecker)
    .where(
      and(
        sql`${wrecker.zones} @> '[{"insuranceGTACode": "groupama"}]'::jsonb`,
        sql`${wrecker.zones} @> '[{"supportedDepartments": ["75"]}]'::jsonb`,
      ),
    );
return db
    .select({
      email: wrecker.contactEmail,
    })
    .from(wrecker)
    .where(
      and(
        sql`${wrecker.zones} @> '[{"insuranceGTACode": "groupama"}]'::jsonb`,
        sql`${wrecker.zones} @> '[{"supportedDepartments": ["75"]}]'::jsonb`,
      ),
    );


However when I try to replace "75" with a dynamic value I get an error of
PostgresError: invalid input syntax for type json
PostgresError: invalid input syntax for type json

Here is the code that I'm trying to work with:

const department = "75";

return db
    .select({
      email: wrecker.contactEmail,
    })
    .from(wrecker)
    .where(
      and(
        sql`${wrecker.zones} @> '[{"insuranceGTACode": "groupama"}]'::jsonb`,
        sql`${wrecker.zones} @> '[{"supportedDepartments": [${department}]}]'::jsonb`,
      ),
    );
const department = "75";

return db
    .select({
      email: wrecker.contactEmail,
    })
    .from(wrecker)
    .where(
      and(
        sql`${wrecker.zones} @> '[{"insuranceGTACode": "groupama"}]'::jsonb`,
        sql`${wrecker.zones} @> '[{"supportedDepartments": [${department}]}]'::jsonb`,
      ),
    );


I would appreciate any help and solution for this problem
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

Full-text search on jsonb column
Drizzle TeamDTDrizzle Team / help
3y ago
Non hardcoded strings in sql()
Drizzle TeamDTDrizzle Team / help
9mo ago
JsonB and $type
Drizzle TeamDTDrizzle Team / help
11mo ago
jsonb field definition.
Drizzle TeamDTDrizzle Team / help
3y ago