Java Script UDF in MySQL 8

Question

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 [];
}
""";

There is a project that provides the code for a MySQL server plugin and a number of MySQL UDFs that expose Google's v8 javascript engine in MySQL queries and stored routines. Check this out. github.com/rpbouman/mysqlv8udfs A presentation about the project. blog.mariadb.org/wp-content/uploads/2014/05/… — Isaac Han
– Isaac Han, Commented Dec 29, 2022 at 7:59

Bill Karwin · Accepted Answer · 2022-11-06 17:40:03Z

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());

Collectives™ on Stack Overflow

Java Script UDF in MySQL 8

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related