Is there an equivalent way to do MySQL's JSONOBJECTAGG() in MariaDB? Is there an equivalent way to do MySQL's JSONOBJECTAGG() in MariaDB? json json

Is there an equivalent way to do MySQL's JSONOBJECTAGG() in MariaDB?


No, MariaDB still does not support JSON_ARRAYAGG and JSON_OBJECTAGG functions. A JIRA ticket has been raised for requesting this feature: https://jira.mariadb.org/browse/MDEV-16620

Now, from the docs of JSON_OBJECTAGG():

It takes only two column names or expressions as arguments, the first of these being used as a key and the second as a value.

An error occurs if any key name is NULL or the number of arguments is not equal to 2.

However, you are specifying three arguments in JSON_OBJECTAGG(awards.fiscal_year, coapplicants.coapplicant_name, coapplicants.organization_number); so your attempted query will not work as well.

Now, in the absence of the required functions, we can utilize Group_Concat() with Concat(). I am assuming that you need only first two arguments (as explained in previous para).

GROUP_CONCAT( DISTINCT CONCAT('"', awards.fiscal_year, '": "',                              coapplicants.coapplicant_name, '"')               SEPARATOR ', ')

Note that, in case of string getting very very long, Group_Concat() may truncate it. So, you can increase the allowed length, by executing the following query, before the above query:

SET SESSION group_concat_max_len = @@max_allowed_packet;