Skip to content

Instantly share code, notes, and snippets.

@Klaasvaak
Created February 2, 2015 13:28
Embed
What would you like to do?
MySQL JSON search
Instead of using regexp like so:
ancestors column would be something like: {"category": 1}
WHERE `ancestors` REGEXP '(.*\"category\":\"1\")'
Use the JSON functions:
WHERE JSON_EXTRACT(`ancestors`, 'category') = 1
Arrays:
WHERE JSON_SEARCH(JSON_EXTRACT('{"categories": [1,2,3]}', 'categories'), "1") != null
More info:
https://blogs.oracle.com/svetasmirnova/ (usefull blog)
http://labs.mysql.com/ (Download page, select MySQL JSON UDFs 0.3.3)
https://fosdem.org/2015/schedule/event/moving_to_nosql/ (fosdem talk from Sveta Smirnova - Oracle)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment