[RESOLVED] mysql sort order based on regex possible?

bike5

From what I have been reading it doesn't seem possible to sort based on a regular expression in mysql. Is that correct? Is there any alternative to this if it is true. I need to sort results by a value inside a json encoded string in the database.

Weedpacket

Since (as far as I can make out from the documentation) the REGEXP operator only evaluates as 1 or 0 depending on whether or not it finds a match in the current value, all it can do is "All the strings that match on this side of the result set and all the strings that don't match on that side of the result set."

Why are you storing a json-encoded string inside the database instead of storing the values that make up the json-encoded string in their own separate columns in their own table?

bike5

I am using the same couple tables to store information for unrelated things that all share the same base structure. Some data types within the table include extra parameters that others don't so the extra params are saved as json in text. I have a list of the results now and each column shown is sortable except anything that came from the json string. I was able to use regex to filter results, so a user can search for things within a specific key/value pair inside the json string. It is a shame you can't sort using regex, I might have to either forget sorting all together for any fields within the json, or maybe create like 10 "custom1" custom2"... fields that hold any extra data and just remember what each has has based on the data type.

Weedpacket

No, have an additional table linked to the main table by the record's ID number. Any records with additional data store that additional data in that additional table (as I put it earlier "their own separate columns in their own table"). Records that don't have additional data won't need to have anything in the additional table.

Of course, if the additional data are in all sorts of wildly different fields then the table would end up with lots and lots of columns (one for every possible field) and almost all of the entries will be nulls ... but if that was the case then sorting on them wouldn't make much sense anyway.

Basically, you have a database. It's for storing data efficiently, and retrieving it efficiently. Use it.

bike5

Well with the help of a friend of mine I was able write a function that basically rips apart the string and returns the value of the key you were looking for, so returning that function as a variable you are able to sort on it now. It has a few issues I can see, including probably not working on multi-level depths (although have not tested) and stripping valid double quotes in the value if they were at the edges but I can fix that just need more IFs and substrings to replace the trims on the return.

DELIMITER $$
DROP FUNCTION IF EXISTS `MYTABLE`.`json_value`$$
CREATE FUNCTION `MYTABLE`.`json_value`( colValue TEXT, keyName varchar(32) ) RETURNS varchar(255) CHARSET latin1
BEGIN
	DECLARE output VARCHAR(255) DEFAULT null;
	DECLARE cur_position INT DEFAULT 1;
	DECLARE remainder TEXT;
	DECLARE cur_string VARCHAR(1000);
	DECLARE delimiter_length TINYINT UNSIGNED;
	DECLARE delimiter_char CHAR(1);
	DECLARE tmp text;
	SET remainder = colValue;
	SET delimiter_length = 1;
	SET tmp = null;
	SET delimiter_char = ",";
	WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
		SET cur_position = INSTR(remainder, delimiter_char);
		IF cur_position = 0 THEN 
			SET cur_string = remainder;
		ELSE 
			SET cur_string = LEFT(remainder, cur_position - 1);
		END IF;
		IF TRIM(cur_string) != '' THEN 
			IF SUBSTRING_INDEX(cur_string, ':', 1) = CONCAT('{"',keyName,'"') THEN  

				SET tmp = SUBSTRING_INDEX(cur_string, ':', -1);
			END IF;
		END IF;
		SET remainder = CONCAT('{',SUBSTRING(remainder, cur_position + delimiter_length));
	END WHILE;
	RETURN TRIM(BOTH '"' FROM (TRIM(TRAILING '}' FROM (TRIM(LEADING '{' FROM TRIM(tmp))))));
END$$
DELIMITER ;

So to use it

data on diff rows column "param":
{"r":0,"addto":1,"sda":"34"}
{"r":"1","addto":"HELLO \"WORLD\""}
{"addto":1,"removable":0} 
{"addto":"0","removable":0} 


select json_value(param, "addto") as test from MYTABLE order by test desc;

will return
HELLO \"WORLD\
1
1


(notice how the last slash was trimmed off)

I thought this was pretty cool, even if it is not the most efficient way I didn't even think it was going to be possible via all mysql.

Now I am not sure for filtering if it would be faster to use this and then run a LIKE on the value or my regular expression check. Gona have to test out execution times when I get more data 🙂 I am thinking the regex will be faster although.