Drizzle Team•17mo ago

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(),
});

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

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

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!!

12 Replies

Angelelz•17mo ago

You can only use .select() if your from clause is a table, a subquery or a view. It's the only way drizzle can infer your selection What was the problem when you selected the columns?

VRNOP•17mo ago

if I select a col:

.select({ id: channels.id })

.select({ id: channels.id })

I get the error

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

Angelelz•17mo ago

I don't think you'll be able to construct your query this way unfortunately. The from method doesn't support more than one parameter Oh wait, try this:

.select({ id: sql<string>`${channels.id}` }

.select({ id: sql<string>`${channels.id}` }

VRNOP•17mo ago

If i do this:

.select({id: sql<string>`${channels.id}`,})

.select({id: sql<string>`${channels.id}`,})

I get the following error:

Query: select "id", "channelId" from channels, json_each(channels.participants) where ("channels"."ownerId" = ? or json_each.value LIKE '?') -- params: ["user1", "user1"]
 6 |     rawCode;
 7 |     constructor(message, code, rawCode, cause) {
 8 |         if (code !== undefined) {
 9 |             message = `${code}: ${message}`;
10 |         }
11 |         super(message, { cause });
            ^
LibsqlError: SQLITE_ERROR: ambiguous column name: id
 code: "SQLITE_ERROR"

Query: select "id", "channelId" from channels, json_each(channels.participants) where ("channels"."ownerId" = ? or json_each.value LIKE '?') -- params: ["user1", "user1"]
 6 |     rawCode;
 7 |     constructor(message, code, rawCode, cause) {
 8 |         if (code !== undefined) {
 9 |             message = `${code}: ${message}`;
10 |         }
11 |         super(message, { cause });
            ^
LibsqlError: SQLITE_ERROR: ambiguous column name: id
 code: "SQLITE_ERROR"

but with

.select({channelId: sql<string>`${channels.channelId}`,})

.select({channelId: sql<string>`${channels.channelId}`,})

no error it goes thru, but no results either

Angelelz•17mo ago

Maybe like this?

.select({channelId: sql.raw<string>("channels"."channelId"))

.select({channelId: sql.raw<string>("channels"."channelId"))

I can't test this right now, so I'm just throwing out ideas

VRNOP•16mo ago

Ok I got it to work like this:

db
  .select({ id: sql<string>`channels.id`,
            channelId:sql<string>`${channels.channelId}`,
      })
    .from(sql`channels, json_each(channels.participants) ch`)
    .where(or(
        eq(channels.ownerId, userId),
        sql`ch.value LIKE ${userId}`
    ));

db
  .select({ id: sql<string>`channels.id`,
            channelId:sql<string>`${channels.channelId}`,
      })
    .from(sql`channels, json_each(channels.participants) ch`)
    .where(or(
        eq(channels.ownerId, userId),
        sql`ch.value LIKE ${userId}`
    ));

Still cannot use id: channels.id or id: sql<string>'${channels.id}' wierdly. Only throwing ambiguous id error only for id field. Thanks for all your help @Angelelz ! Hi Team, a new issue regarding this type of query. It seems the query params are not being passed in properly. With the following query:

await db.selectDistinct({jval: sql<string>`ch.value`})
      .from(sql<string>`${channels}, json_each(${channels.participants}) ch`)
.where(sql`ch.value like '%${userId}%`)

await db.selectDistinct({jval: sql<string>`ch.value`})
      .from(sql<string>`${channels}, json_each(${channels.participants}) ch`)
.where(sql`ch.value like '%${userId}%`)

The sql generated is:

Query: select "id", "createdAt", "updatedAt", "name", "domain", "databaseName", "databaseAuthToken" from "organizations" where "organizations"."domain" = ? -- params: ["hookzapp.com"]
sql {
  sql: "select distinct ch.value from \"channels\", json_each(\"channels\".\"participants\") ch where ch.value like '%?%'",
  params: [ "some-user-id-1" ]
}

Query: select "id", "createdAt", "updatedAt", "name", "domain", "databaseName", "databaseAuthToken" from "organizations" where "organizations"."domain" = ? -- params: ["hookzapp.com"]
sql {
  sql: "select distinct ch.value from \"channels\", json_each(\"channels\".\"participants\") ch where ch.value like '%?%'",
  params: [ "some-user-id-1" ]
}

This query is returning an empty result [] But if I manually substitute the string in the query, it works:

await db.selectDistinct({jval: sql<string>`ch.value`})
      .from(sql<string>`${channels}, json_each(${channels.participants}) ch`)
.where(sql`ch.value like '%some-user-id-1%`)

await db.selectDistinct({jval: sql<string>`ch.value`})
      .from(sql<string>`${channels}, json_each(${channels.participants}) ch`)
.where(sql`ch.value like '%some-user-id-1%`)

Generated SQL

Query: select distinct ch.value from "channels", json_each("channels"."participants") ch where ch.value like '%some-user-id-1%'
sql {
  sql: "select distinct ch.value from \"channels\", json_each(\"channels\".\"participants\") ch where ch.value like '%some-user-id-1%'",
  params: []
}

Query: select distinct ch.value from "channels", json_each("channels"."participants") ch where ch.value like '%some-user-id-1%'
sql {
  sql: "select distinct ch.value from \"channels\", json_each(\"channels\".\"participants\") ch where ch.value like '%some-user-id-1%'",
  params: []
}

Result is:

[
  {
    jval: "{\"id\":\"some-user-id-1\",\"name\":\"User 2\"}"
  }
]

[
  {
    jval: "{\"id\":\"some-user-id-1\",\"name\":\"User 2\"}"
  }
]

Is this a bug or is there something I am doing wrong with respect to params?

Angelelz•16mo ago

Your problem is that you need to pass the parameter with the % already in

.where(sql`ch.value like '${'%' + userId + '%'}`)

.where(sql`ch.value like '${'%' + userId + '%'}`)

VRNOP•16mo ago

oh interesting. let me try

Angelelz•16mo ago

.where(sql`ch.value like ${'%' + userId + '%'}`)

.where(sql`ch.value like ${'%' + userId + '%'}`)

VRNOP•16mo ago

Query: select distinct ch.value from "channels", json_each("channels"."participants") ch where ch.value like '?' -- params: ["%some-id%"]

Still giving me empty result sorry missed out no single quotes It works now. thanks! Although isn't this approach a bit inorganic?

Angelelz•16mo ago

There is no way for drizzle to detect that you have % around your parameters I mean, there is, but it would add a lot of complexity For now it has to be done in user land. Maybe later we could offer some nice helpers

VRNOP•16mo ago

thanks @Angelelz

Gaming

Programming

SQLite json_each

Did you find this page helpful?