Drizzle TeamDT
Drizzle Team•3y ago•
22 replies
VRN

SQLite json_each

Hi. I wandted to use the json_each function in sqlite, but I dont think the query is building correctly, could someone please guide me in the right direction?

My table:
export const channels = sqliteTable('channels', {
    id: text('id').primaryKey().notNull(),
    channelId: text('channelId').notNull(),
    ownerId: text('ownerId').notNull(),
    participants: text('participants', {mode: 'json'}).$type<string[]>().$default(() => []),
    channelName: text('channelName').notNull(),
    lastMessage: text('lastMessage').notNull(),
    lastMessageTimestamp: integer('lastMessageTimestamp', { mode: 'timestamp_ms' }).notNull(),
});


My query:
const r = await db
  .select()
  .from(sql`channels, json_each(channels.participants)`)
  .where(or(
    eq(channels.ownerId, userId),
    sql`json_each.value LIKE '${userId}'`
  ));
return r as Channel[] // <-- loosing any type defs


The sql that ends up running is:

Query: select  from channels, json_each(channels.participants) where ("channels"."ownerId" = ? or json_each.value LIKE '?') -- params: ["user1", "user1"]
2023-11-06T18:01:27.996962Z ERROR sqlite3Parser: near FROM, "Token(None)": syntax error

There doesnt seem to be any columns selected (I tried defining the cols manually as well but didnt work)

Any help would be much appricated!!
Was this page helpful?