MySQL full text search on JSON data

Question

I'm trying to replicate the following LIKE query using a full text search on JSON data;

SELECT * FROM table
  WHERE response LIKE '%"prod_id": "foo"%'
  AND response LIKE '%"start_date": "2016-07-13"%'

In my database the above query returns 28 rows

This is my attempt:

SELECT * FROM table
  WHERE MATCH(response)
    AGAINST('+"\"prod_id\": \"foo\"",+"\"start_date\": \"2016-07-13\""')

However this returns over 4,500 rows (the same as running the first query for only the prod_id ~1,900 rows when running the first query on just the date)

It was my understanding that +"text here" would indicate a required word, and that literal double quotes (present in the JSON data) should be escaped, and that , would indicate a split between the two strings I'm looking for. What am I not understanding correctly? Is there any point in running this as a full text query anyway?

I think that you forgot to add the [IN BOOLEAN MODE](https://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html) modifier to activate those boolean operators. The default modifier is [IN NATURAL LANGUAGE MODE](http://dev.mysql.com/doc/refman/5.7/en/fulltext-natural-language.html) which doesn't suit your needs. — Sevle, Apr 01 '16 at 11:58
Thanks @Sevle that was certainly half the problem - the rest of my required tweaks are below. — Novocaine, Apr 01 '16 at 14:32
This is not 5.7 JSON column! It's just a TEXT column where JSON stored as plain text. — orzFly, Aug 13 '18 at 04:10

Novocaine · Accepted Answer · 2018-08-13T15:54:13.190

Thanks to @Sevle I've tweaked my query like so, and it's returning the correct results;

SELECT * FROM table
  WHERE MATCH(response)
    AGAINST('+\"prod_id: foo\" +\"start_date: 2016-07-13\"' IN BOOLEAN MODE)

The comma was not helping and I was escaping the wrong characters, and of course I did need IN BOOLEAN MODE to be added. Finally, I removed the double quotes I was searching for in the JSON string.

It may also be worth noting that as I'm using PHP PDO to run this query I also had to make the following tweaks.

Instead of constructing the query like so trying to bind the variables like I normally would;

$query = $db->prepare('...AGAINST('+\"prod_id: :prod_id\" +\"start_date: :start_date\"');
$query->execute(array('prod_id' => 'foo', 'start_date' => '2016-07-13'));

I had to do this, as I found I could not bind variables in full text searches

$sql_against = $db->quote('...AGAINST('+\"prod_id: foo\" +\"start_date: 2016-07-13\"');
$query = $db->prepare("...AGAINST($sql_against IN BOOLEAN MODE)")

MySQL full text search on JSON data

1 Answers1