MySQL REGEXP + whitespace (\ s)

Question

The database I'm working with stores JSON records in LONGTEXT data types. I want to be able to select records based on JSON data. Here are some sample data:

{ "12f9cb0a-2218-4590-a05d-c1ffab00f693": { "0": { "value": "test" } }, "4d1dfd2e-7bc1-4303-9c8c-90856e918bb9": { "item": { "0": "11" } } } 

So, I want to select data containing "4d1dfd2e-7bc1-4303-9c8c-90856e918bb9": {"item": {"0": "11"}} , filtering out spaces (tabs, spaces, newlines) using the REGEXP function I 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 is not like the \ s * expression. Does anyone have a better solution for this?

Answer 1

MySQL doesn't seem to support the \s notation in regular expressions, but the notation is [[:space:]] (where [:space:] , inside the character class means "any space character").

By the way, when you need a backslash - for example, when you need a literal asterisk \* - you really need to double the backslash (for example, \\* ), as indicated in & sect; 12.5.2 "Regular Expressions" of the MySQL 5.6 Reference Guide :

Note
Since MySQL uses C escape syntax in strings (for example, " \n " to represent a newline character), you must double any " \ " that you use in your REGEXP strings.

Answer 2

Use string replacement;

 select replace(json, ' ','') from table; 

If there are spaces in your data, this will not work.

Answer 3

It is better to use a json type: SELECT CAST(`column` AS JSON) WHERE JSON_EXTRACT(CAST(`column` as JSON),'$."4d1dfd2e-7bc1-4303-9c8c-90856e918bb9".item."0"')="11"