Menu
The database I work with stores JSON entries in LONGTEXT datatypes. I want to be able to SELECT entries depending on the JSON data. Here's some example data:
{
"12f9cb0a-2218-4590-a05d-c1ffab00f693": {
"0": {
"value": "test"
}
},
"4d1dfd2e-7bc1-4303-9c8c-90856e918bb9": {
"item": {
"0": "11"
}
}
}
So I want to select data that contains "4d1dfd2e-7bc1-4303-9c8c-90856e918bb9": { "item": { "0":"11" } } by filtering out the whitespace (tabs, spaces, new lines) using the REGEXP function, I have tried this to no avail:
SELECT * FROM my_table WHERE (elements REGEXP BINARY '"4d1dfd2e-7bc1-4303-9c8c-90856e918bb9":\s*{\s*"item":\s*{\s*"0":\s*"11"\s*}\s*}');
The regex test works using Rubular and Regexpal.com but MYSQL doesn't seem to like the \s* expression. Does anyone have a better solution for this?
492
MySQL supports another type of pattern matching operation based on the regular expressions and the REGEXP operator. It provide a powerful and flexible pattern match that can help us implement power search utilities for our database systems. REGEXP is the operator used when performing regular expression pattern matches.
MySQL REGEXP performs a pattern match of a string expression against a pattern. The pattern is supplied as an argument. If the pattern finds a match in the expression, the function returns 1, else it returns 0. If either expression or pattern is NULL, the function returns NULL.
Basically, LIKE does very simple wildcard matches, and REGEX is capable of very complicated wildcard matches. In fact, regular expressions ( REGEX ) are so capable that they are [1] a whole study in themselves [2] an easy way to introduce very subtle bugs.
A Regular Expression is popularly known as RegEx, is a generalized expression that is used to match patterns with various sequences of characters. A RegEx can be a combination of different data types such as integer, special characters, Strings, images, etc.
Before MySQL 8.0, MySQL regexes only support the notations listed in §12.7.2 "Regular Expressions" of the MySQL 5.7 Reference Manual (or counterpart for the appropriate version), which include the [[:space:]] notation but not the \s notation. So, you just need to replace each occurrence of \s with [[:space:]].
In MySQL 8.0 and later, MySQL uses the regex support of International Components for Unicode [link], which does include \s [link]; but because MySQL itself uses \ as the escape character within strings, you need to double the backslash, replacing each occurrence of \s with \\s.
133
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
