CAST(<json column> with LIKE seems to be buggin

Hey! Having some issues with Drizzle and Planetscale while trying to search through a json column.

    return query.where(sql`CAST(${logs.metadata} AS CHAR) LIKE '%${metadata}%'`);

    return query.where(sql`CAST(${logs.metadata} AS CHAR) LIKE '%${metadata}%'`);

I 've got something like this, althought when running it it seems to fail on me (see error below):

Query: select `id`, `userId`, `teamId`, `level`, `severity`, `message`, `timestamp`, `metadata`, `resource` from `logs` where (`logs`.`teamId` = ? or CAST(`logs`.`metadata` AS CHAR) LIKE '?') order by `logs`.`timestamp` DESC limit ? -- params: ["UzuZFCpJcZRDB2fCtfJbk", "checking", 100]

web:dev:  ⨯ ../node_modules/.pnpm/@[email protected]/node_modules/@planetscale/database/dist/index.js (78:18) @ Connection.execute
web:dev:  ⨯ DatabaseError: syntax error at position 213 near 'checking'

Query: select `id`, `userId`, `teamId`, `level`, `severity`, `message`, `timestamp`, `metadata`, `resource` from `logs` where (`logs`.`teamId` = ? or CAST(`logs`.`metadata` AS CHAR) LIKE '?') order by `logs`.`timestamp` DESC limit ? -- params: ["UzuZFCpJcZRDB2fCtfJbk", "checking", 100]

web:dev:  ⨯ ../node_modules/.pnpm/@[email protected]/node_modules/@planetscale/database/dist/index.js (78:18) @ Connection.execute
web:dev:  ⨯ DatabaseError: syntax error at position 213 near 'checking'

Is this a drizzle or planetscale thing, and does anyone know if I'm doing some terribly wrong? 😄

1 Reply

chipOP•11mo ago

This query works just fine when running it in my db query console,fyi Figured it out. Solution:

  return query.where(
    and(
      eq(logs.teamId, teamId),
      or(
        ...metadata.map((meta) => {
          const likePattern = `%${meta}%`;
          return sql`CAST(${sql.raw('logs.metadata')} AS CHAR) LIKE ${likePattern}`;
        }),
      ),
    ),
  );

  return query.where(
    and(
      eq(logs.teamId, teamId),
      or(
        ...metadata.map((meta) => {
          const likePattern = `%${meta}%`;
          return sql`CAST(${sql.raw('logs.metadata')} AS CHAR) LIKE ${likePattern}`;
        }),
      ),
    ),
  );

Gaming

Programming

CAST(<json column> with LIKE seems to be buggin