DT
Drizzle Team•4mo 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"]
  }
]
[
  {
    "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 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
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?