Could someone please assist me with this query issue? I have a table with a jsonb column that stores stringified data in the following format:
entry: {
1: "data1",
2: "data2"
}
I am trying to retrieve entries where the key is 1 and its value is "data1". However, my current query is throwing 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",
This is how the query looks in my model:
return await db(db_table).select('*')
.limit(per_page).where('form_id', form_id)
.whereRaw("entry->>[?] = ?",['1', 'data1']); // issue is here
Can anyone suggest the correct way to construct the last line in order to search the json field/column and return all matching rows from the table where key 1 equals data1? Thank you!