MySQL JSON_OBJECT + CONCAT & GROUP_CONCAT as alias & truncating / Max Length

user_10035965 0
Viewed 1299

I'm trying to pull some relational info into a JSON string via MySQL. However, the method that I'm using seems to truncate the returned value.

Does MyQSL enforce some kind of max string length for queries using CONCAT / GROUP_CONCAT / JSON_OBJECT ? Can I override this?

(SELECT
    CONCAT(
        '[',
            GROUP_CONCAT(
                JSON_OBJECT(
                    'my_key_1', my_table.my_val_1,
                    'my_key_2', my_table.my_val_2,
                    'my_key_3', my_table.my_val_3,
                    'my_key_4', my_table.my_val_4,
                    ## [etc, etc ...]
                )
            ),
        ']' 
    )
    FROM my_table
) AS my_alias
alias mysql json
2 Answers

Use JSON_ARRAYAGG instead of GROUP_CONCAT:

SELECT
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'my_key_1', my_table.my_val_1,
            'my_key_2', my_table.my_val_2,
            'my_key_3', my_table.my_val_3,
            'my_key_4', my_table.my_val_4,
            ## [etc, etc ...]
        )
    )
FROM my_table

GROUP_CONCAT is truncated at @@group_concat_max_len (which defaults to 1024 on MySQL or on MariaDB before 10.2, but you can set with e.g. SET @@group_concat_max_len=1000000;); this limit does not apply to JSON_ARRAYAGG.

JSON_ARRAYAGG requires MySQL 5.7.22+ or MariaDB 10.5+

ysth_173890

Just in case this helps someone else; for this particular case I was using CodeIgniter 3. My local version was running on a higher MySQL version and, though a great solution, JSON_ARRAYAGG wouldn't work in other environments running below MySQL 5.7

Using the following in my CI SQL query builder seemed to do the trick in order for me to use GROUP_CONCAT for larger returned strings:

$this->db->simple_query('SET SESSION group_concat_max_len=15000');
$sql = "(SELECT
    GROUP_CONCAT(
        '{',
            '\"my_key_1\":', a_table.value_1, ',',
            '\"my_key_2\":', a_table.value_2, ',',
            ## etc, etc
        '}'
    )
) AS my_alias
FROM my_table
## WHERE
";

return $this->db->query($sql);
user_100359650
Related