Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

maybe I miss something. But why do this function doesn't exist?

Based on the documentation it should be: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

Version: 5.7.21-0ubuntu0.16.04.1

Somebody help me out pls :) This function could help me out well.

JSON_ARRAYAGG was introduced in MySQL 5.7.22.

If you have a space between a function name and the parentheses ( JSON_OBJECTAGG ("item", item.item_id) ). MySQL, by default, does not handle the function name as a built-in function.

You can either remove the space between then function name and the parentheses or use sql_mode IGNORE_SPACE . Easier just not to use the space.

My work-around solution.

REGEXP_REPLACE(GROUP_CONCAT(JSON_OBJECT(category_id, accuracy)), '(},{)', ',')
Seems in Mariadb 10.5 this function is active.
                My work-around solution. REGEXP_REPLACE(GROUP_CONCAT(JSON_OBJECT(category_id, accuracy)), '(},{)', ',')  Seems in Mariadb 10.5 this function is active.
– JonyD
                Sep 30, 2021 at 17:52
I had the same problem, update your MariaDB manually to 10.5.0
BE CAREFUL ABOUT YOUR DATA!
https://mariadb.com/kb/en/json_objectagg/
JSON_OBJECTAGG MariaDB starting with 10.5.0
JSON_OBJECTAGG was added in MariaDB 10.5.0.
                I'd appreciate you provide us a detailed instruction how to update MariaDB 10.4 to 10.5.  stackoverflow.com/questions/69428841/…
– JonyD
                Oct 5, 2021 at 20:32
You can use different functions with same approach
 // 1-  DB::raw(" JSON_ARRAYAGG(json_extract(title)) AS test")
 // 2-  DB::raw(" CONCAT('[', GROUP_CONCAT(JSON_EXTRACT(title)), ']')  AS test")
 // 3-  DB::raw(" GROUP_CONCAT(JSON_UNQUOTE(JSON_EXTRACT(title)))  AS test")
I am using this technique for JSON objects
SELECT
GROUP_CONCAT( CONCAT("{status:'",status,"'"), CONCAT("case-count:'",cnt,"'"), CONCAT("}") ) ,"]") as data
FROM ....
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.