Search code examples
mysqlknex.jsbookshelf.js

How do I search for a substring in a JSON field in MySQL (Knex/Bookshelf)?

I'm using Bookshelf/Knex for an ORM. I've got a MySQL database, with a table which has a JSON field called "data." Inside that field, there is a key "title" and a key "message." I want to return all rows which have substring "searchString" in either data.title or data.message. How can I do this?

Would this work?

qb.raw(`data->'title' LIKE ${searchString} OR data->'message LIKE ${searchString}`)
Solution

  • This is how to do it without SQL injection hole:

    const results = await new Modelname()
                .query((qb) => {
                        qb.whereRaw(
                           `JSONColumnName->
                               '$.JSONFieldName' LIKE ?`, [`%${searchString}%`])
                })
                .fetch();