Drizzle Team•13mo ago

How to include array literals in raw sql?

I have an input array used for an order by using the array_position function - rather than passing the array as a single param, the call to:

.orderBy(sql`array_position(${ingredientNames}, ${entity.name})`);

.orderBy(sql`array_position(${ingredientNames}, ${entity.name})`);

generates a param list for each item in the input array. This generates a record rather than an array literal in Postgres. Trying raw sql with:

`{${array.map((item) => `'${item}'`).join(",")}}`

`{${array.map((item) => `'${item}'`).join(",")}}`

doesn't work either.

2 Replies

francis•13mo ago

update: got it working with:

export function arrayToSqlArray(array: unknown[]) {
  return `{${array.map((item) => `"${item}"`).join(",")}}`;
}

export function arrayToSqlArray(array: unknown[]) {
  return `{${array.map((item) => `"${item}"`).join(",")}}`;
}

and:

    .orderBy(sql`array_position(${arrayToSqlArray(ingredientNames)}, ${ingredient.name})`)

    .orderBy(sql`array_position(${arrayToSqlArray(ingredientNames)}, ${ingredient.name})`)

dandadan•6mo ago

ive got it working with the following

const sanitizeArray = (arr: string[]) => {
  const sanitizedArray: SQL<unknown>[] = [sql`[`];
  const rawArray: SQL<unknown>[] = [];

  for (const element of arr) {
    rawArray.push(sql`${element}`);
  }
  sanitizedArray.push(sql.join(rawArray, sql`, `));
  sanitizedArray.push(sql`]`);

  const sanitizedQuery = sql.join(sanitizedArray);
  return sanitizedQuery;
};

const tokens = ["a", "b"];
const sanitizedTokens = sanitizeArray(tokens);
const x = sql`SELECT unnest(ARRAY${sanitizedTokens}) AS word`;

const sanitizeArray = (arr: string[]) => {
  const sanitizedArray: SQL<unknown>[] = [sql`[`];
  const rawArray: SQL<unknown>[] = [];

  for (const element of arr) {
    rawArray.push(sql`${element}`);
  }
  sanitizedArray.push(sql.join(rawArray, sql`, `));
  sanitizedArray.push(sql`]`);

  const sanitizedQuery = sql.join(sanitizedArray);
  return sanitizedQuery;
};

const tokens = ["a", "b"];
const sanitizedTokens = sanitizeArray(tokens);
const x = sql`SELECT unnest(ARRAY${sanitizedTokens}) AS word`;

im not even sure where i picked up the snippet of code from im leaving this here in case someone has a better solution

Gaming

Programming

How to include array literals in raw sql?