Drizzle TeamDT
Drizzle Team•16mo ago
Dush

Replacing hardcoded text value in `jsonb`

Hi everyone

I have this data in my jsonb column of my Postgres database:
[
  {
    "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`,
      ),
    );


However when I try to replace "75" with a dynamic value I get an error of 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`,
      ),
    );


I would appreciate any help and solution for this problem
Was this page helpful?