How can I join 2 tables where 1 side is a JSON property in a JSON column?

I am trying this:

db.selectFrom('my_table')
    .selectAll(['my_table'])
    .innerJoin(
      'table_2',
      'table_2.id',
      sql`my_table.metadata->>'id'`,
    )
    .select(['table_2.name'])

db.selectFrom('my_table')
    .selectAll(['my_table'])
    .innerJoin(
      'table_2',
      'table_2.id',
      sql`my_table.metadata->>'id'`,
    )
    .select(['table_2.name'])

I am getting a type issue:

Argument of type 'RawBuilder<unknown>' is not assignable to parameter of type 'AnyJoinColumn<KyselyDB, "my_table", "table_2"> | AnyJoinColumnWithTable<KyselyDB, "my_table", "table_2">

Argument of type 'RawBuilder<unknown>' is not assignable to parameter of type 'AnyJoinColumn<KyselyDB, "my_table", "table_2"> | AnyJoinColumnWithTable<KyselyDB, "my_table", "table_2">

Solution

Hey

const rows = await db
  .selectFrom("my_table")
  .innerJoin("table_2", (jb) =>
    jb.on("table_2.id", "=", (eb) =>
      eb.ref("my_table.metadata", "->>").key("id"),
    ),
  )
  .execute();

const rows = await db
  .selectFrom("my_table")
  .innerJoin("table_2", (jb) =>
    jb.on("table_2.id", "=", (eb) =>
      eb.ref("my_table.metadata", "->>").key("id"),
    ),
  )
  .execute();

https://kyse.link/3ghOW

Jump to solution

Kysely•15mo ago•

5 replies

bombillazo

How can I join 2 tables where 1 side is a JSON property in a JSON column?

I am trying this:

db.selectFrom('my_table')
    .selectAll(['my_table'])
    .innerJoin(
      'table_2',
      'table_2.id',
      sql`my_table.metadata->>'id'`,
    )
    .select(['table_2.name'])

db.selectFrom('my_table')
    .selectAll(['my_table'])
    .innerJoin(
      'table_2',
      'table_2.id',
      sql`my_table.metadata->>'id'`,
    )
    .select(['table_2.name'])

I am getting a type issue:

Argument of type 'RawBuilder<unknown>' is not assignable to parameter of type 'AnyJoinColumn<KyselyDB, "my_table", "table_2"> | AnyJoinColumnWithTable<KyselyDB, "my_table", "table_2">

Argument of type 'RawBuilder<unknown>' is not assignable to parameter of type 'AnyJoinColumn<KyselyDB, "my_table", "table_2"> | AnyJoinColumnWithTable<KyselyDB, "my_table", "table_2">

Solution

Hey

const rows = await db
  .selectFrom("my_table")
  .innerJoin("table_2", (jb) =>
    jb.on("table_2.id", "=", (eb) =>
      eb.ref("my_table.metadata", "->>").key("id"),
    ),
  )
  .execute();

const rows = await db
  .selectFrom("my_table")
  .innerJoin("table_2", (jb) =>
    jb.on("table_2.id", "=", (eb) =>
      eb.ref("my_table.metadata", "->>").key("id"),
    ),
  )
  .execute();

https://kyse.link/3ghOW

Jump to solution

Continue the conversation

Join the Discord to ask follow-up questions and connect with the community

Kysely

First came Kysely, the type-safe query builder for TypeScript, then came people asking questions, then came the Discord server.Join if you like Kysely or have questions, or both.

1,992 Members

Join