botpress query by json field (knex, sqllite vs postgres)

botpress-query-by-json.js

let query = bp.database('web_messages')
if (bp.database.isLite) {
  query
    .where('attr_fookey', 'like', `%foo_value%`)
    .select(bp.database.raw(`web_messages.id, json_extract(web_messages.payload, '$.foo') as attr_fookey`))
} else {
  query.whereRaw(`web_messages.payload ->>'foo' like '%foo_value%'`)
  query.select(this.knex.raw(`web_messages.id`))
}

console.log('\n', await query, '\n')