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

Gaming

Programming

Replacing hardcoded text value in `jsonb`