How to filter db data based on a JSONB column in KNEX using Typescript / Javascript

user_12526458 0
Viewed 298

Please help point me in the right direction: I have a jsonb column in one of my tables. It saves stringified data like this:

entry: {
    1: "data1",
    2: "data2"
}

If I want to get entries whose key 1 is data1, how do I write the query? Below throws an sql error:

sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['1'] = 'data1' limit 500' at line 1",

Here is what I used in the model:

return await db(db_table).select('*')
.limit(per_page).where('form_id', form_id)
.whereRaw("entry->>[?] = ?",['1', 'data1']); // issue is here

How can I construct that last line to search json field/column and return all filtered rows in the table where key 1 is equal to data1? Thank you!

jsonb knex.js react-native typescript javascript
1 Answers

It should look like this:

return await db(db_table).select('*')
  .limit(per_page).where('form_id', form_id)
  .whereRaw("entry->>$.?? = ?",['1', 'data1']); // issue is here
  // ------------------^ this extra ? marks a placeholder for a column
user_65393170
Related