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

Question

The server I was working on for a data project crashed and I am now recreating the database. I used to be working on a MySQL database, and now I'm using MariaDB. I have never used MariaDB before.

Previously, I used the following command to insert some data into one table from another:

CREATE TABLE collaborators_list 
SELECT awards.id, awards.researcher_name, awards.organization_id, 
    JSON_OBJECTAGG(awards.fiscal_year, coapplicants.coapplicant_name, 
    coapplicants.organization_number) 
AS 'coapplicants_list' FROM awards 
INNER JOIN coapplicants 
ON awards.id=coapplicants.id 
GROUP BY awards.researcher_name, awards.organization_id;

Basically, I want to do the same thing in MariaDB. I tried looking here: https://mariadb.com/kb/en/library/json-functions/ but unless I am misreading something, none of these is what I really want...

Help!

Answer 1

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;