Why does the same query work using raw sql but not the select method?

[npearson72]

When I perform the query using the following syntax it works:

const statement = sql`select * from ${items}, json_each(${items.tags}) as "jsonTags"`
  .append(sql` where "jsonTags"."value" ->> '$.name' = 'hello'`)

await searchDB().all(statement);

However the same query using select throws an error:

const columns = getTableColumns(items);

await db
  .select(columns)
  .from(sql`${items}, json_each(${items.tags}) as "jsonTags"`)
  .where(sql` "jsonTags"."value" ->> '$.name' = 'hello'`)

The error:

Your "id" field references a column "items"."id", but the table "items" is not part of the query! Did you forget to join it?

Is this a bug or am I just not using select correctly?

PS: This is using SQLite with a json field.

[kokoc30]

The error comes from Drizzle’s selected-column check. getTableColumns(items) supplies column objects belonging to items, but passing an SQL template to .from(...) gives the builder an opaque SQL expression. Interpolating ${items} into that expression does not register it as the query’s base table. The raw .all(statement) path does not perform that selected-column check.

Keep .from(items) and add the table-valued function as a join:

import { getTableColumns, sql } from "drizzle-orm";

const name = "hello";
const query = db
  .select(getTableColumns(items))
  .from(items)
  .innerJoin(
    sql`json_each(${items.tags}) AS "jsonTags"`,
    sql`true`,
  )
  .where(sql`"jsonTags"."value" ->> '$.name' = ${name}`);

const rows = await query;

The join condition true preserves the expansion of your comma join: each item is paired with the rows from json_each(items.tags), then the WHERE condition filters the tag rows. Selecting getTableColumns(items) returns only item columns, rather than the additional json_each columns included by your original SELECT *.

If your intent is instead “return each item once if any tag matches,” use a correlated EXISTS filter:

const rows = await db
  .select()
  .from(items)
  .where(sql`exists (
    select 1
    from json_each(${items.tags}) AS "jsonTags"
    where "jsonTags"."value" ->> '$.name' = ${name}
  )`);

That deliberately avoids duplicate item rows when several tags match; the join version preserves those duplicates. ${name} is a bound value in both examples.

The SQLite dialect’s check explains the error. I also checked that the 0.44.2 join implementation accepts SQL expressions, alongside SQLite’s json_each documentation. I have not executed these queries against your schema.