K
Kysely•10mo ago
yayza_

How do I compare to a value in a jsonb column?

I have this method: 
    async getLatestLosingSpin(gameId: GameId) {
        const row: any = await this.db
            .selectFrom("slot_results.spin_results")
            .select("data")
            .where("game_id", "ilike", gameId)
            .where("data", "@>", JSON.stringify({ result: { game: { win: { total: "0.00" } } } }))
            .limit(1)
            .executeTakeFirst();

        return row;
    }
    async getLatestLosingSpin(gameId: GameId) {
        const row: any = await this.db
            .selectFrom("slot_results.spin_results")
            .select("data")
            .where("game_id", "ilike", gameId)
            .where("data", "@>", JSON.stringify({ result: { game: { win: { total: "0.00" } } } }))
            .limit(1)
            .executeTakeFirst();

        return row;
    }
I'm pretty much trying to do the opposite. Where the "total" value isn't "0.00". my table just has 3 columns "id", "game_id", and "data"
No description
4 Replies
yayza_
yayza_OP•10mo ago
this works and it's based on what I read from hovering over ref() and reading the documentation: 
// In the next example we use ref to reference a nested JSON property:

db.selectFrom('person')
  .where(({ eb, ref }) => eb(
    ref('address', '->').key('state').key('abbr'),
    '=',
    'CA'
  ))
  .selectAll()
// In the next example we use ref to reference a nested JSON property:

db.selectFrom('person')
  .where(({ eb, ref }) => eb(
    ref('address', '->').key('state').key('abbr'),
    '=',
    'CA'
  ))
  .selectAll()
The only problem is that I have to use //@ts-ignore to bypass that error 😅 , which makes me assume I'm doing it wrong
yayza_
yayza_OP•10mo ago
No description
koskimas
koskimas•10mo ago
You just need to use a correct type for that data column. I'm guessing you just have object or something similar now? It needs to have the result property that needs to have the game property and so on.
yayza_
yayza_OP•10mo ago
oh okay, I just ran the codegen I didnt create the type myself for that column but i'll do that now 🙏 oh, my problem was that kysely-codegen doesn't support json columns yet, but i used the codegen and modified those manually now it works . thanks for the point in the right direction!

Did you find this page helpful?