CODEWITHSUNDEEP
phpmysqlsqljson
Kristian Vitozev
Kristian Vitozev

Reputation: 5971

Search against property in JSON object using MySQL 5.6

Long story short: I'm using MySQL 5.6 (at the moment, it's impossible to update to newer version and use improved JSON capabilities), and I have a column where JSON is stored.

Sample JSON objects stored in database:

-- Row #1 (`options` column)
{"name": "Test", "description": "Some description", "type": "post", "scheduled": true, "deleted": false, "timestamp": "1482263883"}

-- Row #2 (`options` column)
{"name": "", "description": "Some description test", "type": "post", "scheduled": true, "deleted": false, "timestamp": "1482263883"}

The problem: I want to be able to search over the records against name property stored in my JSON object.

SELECT * FROM `my_table` WHERE ((`options` LIKE '%"name":"%Test%"%'))

The query above works fine, but sometimes it may return false positives (e.g. the second row will be returned as well as it contains "test" string in description property).

Some notes:

  1. The object stored in options column is exactly the same for each row, e.g. name property is located at first position for each record.

Upvotes: 0

Views: 559

Answers (1)

Barmar
Barmar

Reputation: 780673

You can use a regular expression instead of LIKE. This allows you to use a pattern that won't go across string boundaries, unlike the % wildcard in LIKE

SELECT * FROM my_table
WHERE options REGEXP '"name": *"[^"]*Test'

However, instead of the false positive you got, this could produce a false negative if a row contains:

"name": "This is a \"quoted\" Test"

because [^"]* will not go across the escaped quotes.

There might be some way to refine the regexp to allow for this, but it won't be simple.

Upvotes: 3

Related Questions