How create json format with group-concat mysql?

sql

json-engineering

mysql

data-structures

byAlex Kataev·Sep 25, 2024

To create a nested JSON using MySQL, integrate JSON_OBJECT with GROUP_CONCAT. Implant the former within the latter to transform each data row into a JSON element. Then concatenate these elements to construct a JSON array.

Consider this example where the users table contains id, name, and role_id:

SELECT role_id,
       CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', id, 'name', name)), ']') AS json_format
FROM users
GROUP BY role_id;

The result is a JSON array of user data grouped by role_id, with each user represented by id and name in JSON format.

Navigating concatenation limitations

Be aware: the SQL response could surpass the default length limitation set by GROUP_CONCAT. To avoid truncation, assign a larger value to group_concat_max_len:

# Like a high jumper setting a new record!
SET SESSION group_concat_max_len = 1000000;

Users with MySQL 5.7.22+ can leverage JSON_ARRAYAGG that conveniently dodges string concatenation limits and delivers a well-formatted JSON array:

SELECT role_id,
       JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name)) AS json_format
FROM users
GROUP BY role_id;

Fallback with COALESCE

Nulls can occasionally plague your data. Use COALESCE to arrange for an alternative value when nulls appear, ensuring a consistent output:

SELECT role_id,
       CONCAT(
           '[', 
           GROUP_CONCAT(JSON_OBJECT('id', COALESCE(id, 'N/A'), 'name', COALESCE(name, 'Unknown'))),
           ']'
       ) AS json_format
FROM users
GROUP BY role_id;

Tailoring JSON Structure to your Needs

With a variety of complex data structures, you might come across a scenario where a requirement demands to group JSON objects inside arrays or nest them further. Combine CONCAT, GROUP_CONCAT, and JSON_OBJECT to structure your JSON.

Grouping contacts by email

Suppose there's a contacts table with fields email, phone, and name. Let's group the names and phone numbers by email in JSON:

SELECT email,
       CONCAT(
           '{',
               '"', email, '":',
               CONCAT(
                   '[',
                   GROUP_CONCAT(JSON_OBJECT('name', name, 'phone', phone) ORDER BY name SEPARATOR ','),
                   ']'
               ),
           '}'
       ) AS json_format
FROM contacts
GROUP BY email;

This SQL query generates nested JSON arrays against each email having distinct name-phone pairs, ordered by name.

Potential hurdles and solutions

When shaping JSON directly in MySQL, keep these points in mind:

Data types: Confirm the compatibility of data types with JSON functions.
String Encoding: Utilize HEX() to encode special characters when required.
Query Efficiency: GROUP_CONCAT could falter with extremely large datasets. Optimize your queries accordingly.