K
Kysely•7d ago
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 👋 ```ts const rows = await db .selectFrom("my_table")...
Jump to solution
3 Replies
bombillazo
bombillazoOP•7d ago
The logic works, its TS that is giving the error but it is returning the expexted records Solved it! Had to do this: 
 .innerJoin('table_2', (join) =>
      join.onRef(
        'table_2.id',
        '=',
        sql`my_table.metadata->>'id'`,
      ),
    )
 .innerJoin('table_2', (join) =>
      join.onRef(
        'table_2.id',
        '=',
        sql`my_table.metadata->>'id'`,
      ),
    )
Solution
Igal
Igal•6d ago
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
bombillazo
bombillazoOP•6d ago
Hey @Igal thanks for the help, what would be the main differences between using sql'my_table.metadata->>'id' and eb.ref("my_table.metadata", "->>").key("id")??

Did you find this page helpful?