Enhancement: CASE support in SQL MATCH #3151
Description
Enhancement: CASE support in SQL MATCH.
Cypher supports the use of CASE for modifying results in response and also can be used in conjunction with a where clause. This can be useful to do value conversion for responses or enhanced where query filtering.
There does not currently appear to be a way to mechanism this with ArcadeDb SQL MATCH.
For example, a cypher test case shows an example of converting the response string based on the value of the age.
In addition to RETURN statements, this can be useful when performing WHERE queries as well. For example, the database may contain an enumerated integer value that corresponds to a string representation. It can be useful to effectively perform a String-wildcard against the enumerated value.
I've shared snippets below with a theoretical example and query pattern :
enum ColorTypes
{
RED(1),
BLUE(2),
GREEN(3);
int value;
ColorTypes(int i)
{
value = i;
}
public int getValue()
{
return value;
}
}
private void createNode(ColorTypes color)
{
MutableVertex node = database.newVertex(VERTEX_TYPE);
node.set("color", color.getValue()); // Writes an integer value
node.save();
}
database.transaction(() -> {
createNode(ColorTypes.RED);
createNode(ColorTypes.RED);
createNode(ColorTypes.BLUE);
createNode(ColorTypes.GREEN);
});
// THEORETICAL EXAMPLE
// The wildcard string 'ed' matches to RED -> finds 2 objects
// The wildcard 'e' would match to RED, BLUE, and GREEN -> finds 4 objects
// The wildcard 'gr' would match to GREEN -> finds one object
String query = """
MATCH
{
type: myType,
as: thing,
where: ( (CASE WHEN color = 1 THEN 'red' WHEN color = 2 THEN 'blue' WHEN color = 3 THEN 'green' END) ILIKE '%ed%' )
}
return $elements
""";
ResultSet result = database.query("sql", query);