Small MySQL tip for working with multi-enum/array fields

[rinorway]

Sometimes when querying Espo you need to manage array or multi-enum fields.

If you use formulas, then https://docs.espocrm.com/administration/formula/array/ functions is your friend,
but if you need to, here are some SQL snippets that may help.

Example :

ID	Colors
1	NULL
2	["yellow", "blue"]
3	["yellow","green"]

To manage these data, you may want to check if an item is available, add and item in the array or remove an item.

For this you can use the following functions

ADD AN ELEMENT TO ARRAY

CREATE FUNCTION `arrayAdd`(jsonField JSON, valueToAdd VARCHAR(255)) RETURNS json
DETERMINISTIC
BEGIN
IF jsonField IS NULL THEN
RETURN JSON_ARRAY(valueToAdd);
ELSEIF JSON_CONTAINS(jsonField, JSON_QUOTE(valueToAdd)) THEN
RETURN jsonField;
ELSE
RETURN JSON_ARRAY_APPEND(jsonField, '$', valueToAdd);
END IF;
END;

CHECK IF AN ELEMENT EXISTS IN ARRAY

CREATE FUNCTION `arrayCheck`(jsonField JSON, valueToCheck VARCHAR(255)) RETURNS tinyint(1)
DETERMINISTIC
BEGIN
IF jsonField IS NULL THEN
RETURN FALSE;
END IF;

IF JSON_CONTAINS(jsonField, JSON_QUOTE(valueToCheck)) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END

REMOVE AN ELEMENT FROM ARRAY

CREATE FUNCTION `arrayRemove`(jsonField JSON, valueToRemove VARCHAR(255)) RETURNS json
DETERMINISTIC
BEGIN
DECLARE jsonPath VARCHAR(255);
IF jsonField IS NULL THEN
RETURN NULL;
END IF;

SET jsonPath = JSON_UNQUOTE(JSON_SEARCH(jsonField, 'one', valueToRemove));

IF jsonPath IS NULL THEN
RETURN jsonField;
END IF;

RETURN JSON_REMOVE(jsonField, jsonPath);
END​

Example :

select id,
colors,
arrayRemove(colors,"yellow") as a,
arrayAdd(colors,"purple") as b,
arrayCheck(colors,"blue") as IsBlue
from
(select 22 as id , '["yellow","blue"]' as colors ) t​

result

id.colors..............a..........b............... ................IsBlue
-------------------------------------------------------------------------
22.["yellow","blue"]...["blue"]...["yellow", "blue", "purple"]....1

[rinorway]

Warning : please note that in order to work correctly, the table array_values needs to be updated as well.

• The value in the entity table is used to display the field in the general UI
• The values in the arrray_values table are used for selections / filters

To be more precise, the SQL should take that into account as well.

So using the arrayAdd and arrayRemove would need to be adaptated to acoomodate that.