I am using mariadb and I store json data with unicode encoded strings eg \u0395, in a medium text column name remoteData with collation utf8_general_ci.
My problem is where query this column like that
where remoteData like '%ΕΛΕΥΘΕΡΙΟΣ%'
I get an empty set because the value is stored as \uxxxx sequence like this
"patientFirstName":"\u0395\u039b\u0395\u03a5\u0398\u0395\u03a1\u0399\u039f\u03a3"
I have been suggested to use JSON_EXTRACT function like this
WHERE JSON_EXTRACT(`remoteData`, "$.patientFirstName")
but as far as I know this will ignore any index on the column. The table in two weeks has gone to almast 100000 rows and it is growing fast, so I want to be indexed.
What would be the best approach to handle such data?
Should I change from sql starage to no sql like mongo etc?
Thanks in advance.