Java Script UDF in MySQL 8-CodePudding

I am migrating few queries from Google BigQuery to MySQL and need help in replicating the below BigQuery Java script UDF to equivalent MySQL. I don't see any reference over the internet. Does MySQL support Java Script UDFs ? The requirement here is to Split a JSON array into a simple array of string ( each string represents individual JSON string ).

CREATE OR REPLACE FUNCTION `<project>.<dataset>.json2array`(json STRING) RETURNS ARRAY<STRING> LANGUAGE js AS R"""
if (json) {
    return JSON.parse(json).map(x=>JSON.stringify(x));
} else {
    return [];
}
""";

CodePudding user response：

No, MySQL does not support JavaScript stored functions. MySQL supports stored functions written in a procedural language. It also supports server-loadable functions compiled from C or C , but these are less common.

MySQL doesn't have an ARRAY data type. The closest you can get in MySQL is a JSON data type, which may be a one-dimensional array of strings. If your JSON document is assured to be an array in that format, then you can simply do the following:

CREATE FUNCTION json2array(in_string TEXT) RETURNS JSON DETERMINISTIC
  RETURN CAST(in_string AS JSON);

I'm not sure what the point of creating a stored function is in this case, since it only does what CAST() can do. So you might as well just call CAST() and skip creating the stored function.

Perhaps a good use of a stored function is to test the input to make sure it's a document with an array format (use JSON_TYPE()). For example:

CREATE FUNCTION json2array(in_string TEXT) RETURNS JSON DETERMINISTIC
   RETURN IF(JSON_TYPE(in_string) = 'ARRAY', CAST(in_string AS JSON), JSON_ARRAY());