SELECT * FROM table WHERE json_column->something ?

[dejoma]

I am looking to filter based on a JSON column sub field.

export const resources = mysqlTable('my_table', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  meta: json('meta').$type<Record<string, unknown>>()
});

..

// SQL: SELECT * FROM my_table WHERE meta->something = 2;

[dejoma]

I found a working solution, without the sql operator.
Column name meta, and meta looks like: { "motor_type": "V12", "make_year": 2014, "owner": "Michael Kors" }

const resource = await db.query.resources.findFirst({
      where: gte(resources.meta, { owner: "Michael Kors" })
    });

[stereobooster]

const resource = await db.select().from(usersTable).where(sql`${usersTable.meta}->something = ${id}`)

or

const resource = await db.select().from(usersTable).where(sql`json_extract(${usersTable.meta}, '$.something') = ${id}`)

[DenisBessa]

See #386

(it still does not work with version 0.28)