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;