CODEWITHSUNDEEP
mysqljson
luqita
luqita

Reputation: 4087

How to search a field with a JSON value in MySQL?

I have a MySQL column containing fields with data looking like this:

["I don't read books anymore","books \/ novels"]

I would like to retrieve all fields with 'books / novels'. How can I do so? I tried:

SELECT * FROM `table` where data_field like '%books \/ novels%'

but that returns zero results. Why? How are JSON fields supposed to be searched?

Upvotes: 1

Views: 3391

Answers (1)

Rob
Rob

Reputation: 526

Try this:

SELECT * FROM `table` WHERE date_field like '%books \\\\/ novels%'

See note here: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

Because MySQL uses C escape syntax in strings (for example, \n to represent a newline character), you must double any \ that you use in LIKE strings. For example, to search for \n, specify it as \\n. To search for \, specify it as \\\\; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

As you pointed out, the below doesn't work because it can possibly match other strings:

You could try this:

SELECT * FROM `table` where data_field like '%books __ novels%'

or possibly this:

SELECT * FROM `table` where data_field like '%books _ novels%'

The underscore is a single character wild card in like searches.

Upvotes: 2

Related Questions