How to Create JSON Format With GROUP_CONCAT in MySQL

1. Overview

MySQL provides the aggregate function GROUP_CONCAT() to operate on a group of values. Accordingly, it generates a single string value to return by concatenating a group of values. However, we can use GROUP_CONCAT() to return a JSON format result. We can do this by combining it with MySQL’s JSON functions, or by providing the JSON constructs in the function itself.

We can create JSON format from literal values or expressions such as table columns. A JSON format value can be a string, number, true, false, null, array, or object.

In this tutorial, we’ll learn about the different options for creating JSON format with GROUP_CONCAT() in MySQL. To demonstrate, we’ll use the pre-installed tables from the University database.

2. Creating JSON From Literal Values

We can create any of the JSON formats from literal values.

2.1. Creating a JSON Array

To start, let’s create a JSON array by concatenating multiple strings. We provide the JSON constructs in the function call itself when we don’t use a JSON-specific function:

SELECT 
GROUP_CONCAT('[','"first element"',',','"second element"',',','"third element"',']') 
AS result;
+-----------------------------------------------------+
| result                                              |
+-----------------------------------------------------+
| ["first element","second element","third element"]  |
+-----------------------------------------------------+

Alternatively, we can use the GROUP_CONCAT() function in combination with the JSON_ARRAY() function to create the same format:

SELECT 
GROUP_CONCAT(
    JSON_ARRAY("first element","second element","third element")
) 
AS result;
+------------------------------------------------------+
| result                                               |
+------------------------------------------------------+
| ["first element", "second element", "third element"] |
+------------------------------------------------------+

Indeed, a simplified syntax creates the same JSON array format.

2.2. Creating a JSON Object

As another example, let’s use JSON constructs directly to create a JSON object:

SELECT 
GROUP_CONCAT('{','"1"',': ','"one"',', ','"2"',': ','"two"','}')
AS result;
+--------------------------+
| result                   |
+--------------------------+
| {"1": "one", "2": "two"} |
+--------------------------+

Alternatively, we can use the GROUP_CONCAT() function in combination with the JSON_OBJECT() and JSON_MERGE_PRESERVE() functions to create the same format:

SELECT 
    JSON_MERGE_PRESERVE(
        GROUP_CONCAT(JSON_OBJECT("1","one")),
        GROUP_CONCAT(JSON_OBJECT("2","two"))
    ) 
AS result;
+--------------------------+
| result                   |
+--------------------------+
| {"1": "one", "2": "two"} |
+--------------------------+

When we use the GROUP_CONCAT() function combined with JSON functions, we benefit from the formatting and spacing applied automatically.

3. Creating JSON From a Table

We can create any of the JSON formats from expressions using table columns. Typically, we use the GROUP BY clause when we use GROUP_CONCAT() with a table/column expression to group values into subsets.

3.1. Creating a JSON Array

To start, let’s create a JSON array from a table. To demonstrate, let’s query the prerequisite table to find, for each prerequisite course, the courses for which it’s a prerequisite. Further, we format the course list as an array. We provide the JSON constructs in a function call itself when we don’t use a JSON-specific function:

SELECT 
prerequisite_id AS Prerequisite,
CONCAT('[',GROUP_CONCAT("\"",course_id,"\""),']') AS Courses 
FROM prerequisite 
GROUP BY prerequisite_id;
+--------------+---------------------------+
| Prerequisite | Courses                   |
+--------------+---------------------------+
| CE111        | ["CE511"]                 |
| CE121        | ["CE221"]                 |
              ... 
| CS121        | ["CS221","CS421"]         |
| CS122        | ["CS422"]                 |
| CS131        | ["CS231","CS232","CS235"] |
| CS132        | ["CS232"]                 |
| CS141        | ["CS241","CS242"]         |
| CS142        | ["CS242"]                 |
| CS211        | ["CS411","CS511"]         |
| CS241        | ["CS441","CS443"]         |
+--------------+---------------------------+

Alternatively, we can use the function in combination with the JSON_ARRAYAGG() aggregate function to list the course/s that can be registered for, after each distinct prerequisite course:

SELECT 
GROUP_CONCAT(DISTINCT(prerequisite_id)) AS Prerequisite, 
    JSON_ARRAYAGG(course_id) AS Courses 
FROM prerequisite 
GROUP BY prerequisite_id;
+--------------+-----------------------------+
| Prerequisite | Courses                     |
+--------------+-----------------------------+
| CE111        | ["CE511"]                   |
| CE121        | ["CE221"]                   |
              ... 
| CS121        | ["CS221", "CS421"]          |
| CS122        | ["CS422"]                   |
| CS131        | ["CS231", "CS232", "CS235"] |
| CS132        | ["CS232"]                   |
| CS141        | ["CS241", "CS242"]          |
| CS142        | ["CS242"]                   |
| CS211        | ["CS511", "CS411"]          |
| CS241        | ["CS441", "CS443"]          |
+--------------+-----------------------------+

To demonstrate with a more elaborate example, let’s create JSON arrays for the courses offered by the different departments. Further, let’s group the result by department:

SELECT 
department_id AS Department, 
JSON_ARRAY(GROUP_CONCAT(name)) AS Courses 
FROM course 
GROUP BY department_id \G
*************************** 1. row ***************************
Department: 1
   Courses: ["Introduction to Operating Systems,Introduction to Real Time Operat
ing Systems,Introduction to Computer Architecture,Introduction to Databases,Rela
tional Databases,Introduction to Structured Query Language,Introduction to Data
Structures,Introduction to Algorithms,Statistical Machine Learning,Theory of Mac
hine Learning,Operating Systems: Intermediate,Real Time Operating Systems: Inter
mediate,Computer Architecture: Intermediate,Intermediate: Structured Query Langu
age,Intermediate PostgreSQL,Data Structures: Intermediate Level,Algorithms: Inte
rmediate Level,Planar Graphs,Reinforcement Learning,Theory of Deep Learning,Adva
nced Operating Systems,Advanced Computer Architecture,Advanced Database Concepts
,Advanced Relational Databases,Advanced Machine Learning,Advances in Deep Learni
ng,Advanced Reinforcement Learning,Distributed Systems,Advanced Data Structures,
Advanced Algorithms,Non-Planar Graphs"]
*************************** 2. row ***************************
...

We should use the GROUP_CONCAT() function in combination with the JSON_ARRAY() function to ensure that the formatting is indeed JSON, which can be used as an argument to other functions that expect JSON format. This is especially useful when the number of values in the array is large.

3.2. Creating a JSON Object

For example, let’s use JSON constructs directly to create JSON objects from table/column expressions. We can create multiple JSON objects nested in a single JSON array. Let’s find all the courses provided by each department as before, but this time, format the result as JSON objects with course id as key and course name as value within each JSON object:

SELECT 
department_id AS Department,
CONCAT('[',
    GROUP_CONCAT(CONCAT('{"id":"', id, '", "name":"',name,'"}')),
']') AS Courses 
FROM course 
GROUP BY department_id \G
*************************** 1. row ***************************
Department: 1
   Courses: [{"id":"CS111", "name":"Introduction to Operating Systems"},{"id":"C
S112", "name":"Introduction to Real Time Operating Systems"},{"id":"CS113", "nam
e":"Introduction to Computer Architecture"},{"id":"CS121", "name":"Introduction
to Databases"},{"id":"CS122", "name":"Relational Databases"},{"id":"CS123", "nam
e":"Introduction to Structured Query Language"},{"id":"CS131", "name":"Introduct
ion to Data Structures"},{"id":"CS132", "name":"Introduction to Algorithms"},{"i
d":"CS141", "name":"Statistical Machine Learning"},{"id":"CS142", "name":"Theory
 of Machine Learning"},{"id":"CS211", "name":"Operating Systems: Intermediate"},
{"id":"CS212", "name":"Real Time Operating Systems: Intermediate"},{"id":"CS213"
, "name":"Computer Architecture: Intermediate"},{"id":"CS221", "name":"Intermedi
ate: Structured Query Language"},{"id":"CS222", "name":"Intermediate PostgreSQL"
},{"id":"CS231", "name":"Data Structures: Intermediate Level"},{"id":"CS232", "n
ame":"Algorithms: Intermediate Level"},{"id":"CS235", "name":"Planar Graphs"}]
*************************** 2. row ***************************
...

Alternatively, we can use the GROUP_CONCAT() function in combination with the JSON_OBJECT() function to create the same object format:

SELECT 
department_id AS Department,
CONCAT('[',
    GROUP_CONCAT(JSON_OBJECT('id', id,'name', name)),
']') AS Courses 
FROM course 
GROUP BY department_id\G

Using the GROUP_CONCAT() function in combination with JSON functions provides a more concise syntax and automatic formatting.

4. GROUP BY Related Exception

We need to be aware of a GROUP_CONCAT() function-related exception when we use it with the GROUP BY clause, which is the typical use case. By default, MySQL imposes a requirement on referring to nonaggregated columns in the SELECT list, the HAVING clause, or the ORDER BY clause. A nonaggregated column must be named in the GROUP BY clause or functionally dependent on columns in the GROUP BY clause to be used in any of the abovementioned clauses.

Let’s demonstrate with an example:

SELECT
JSON_OBJECT(id,GROUP_CONCAT(id)) AS CourseIDs,
JSON_OBJECTAGG(id,name) AS Courses
FROM course
GROUP BY department_id\G
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'university.course.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The error is generated because we used the id column in the SELECT list without including it in the GROUP BY clause. We can fix the error by removing the id column from the SELECT list:

SELECT
JSON_OBJECT("course_ids",GROUP_CONCAT(id)) AS CourseIDs,
JSON_OBJECTAGG(id,name) AS Courses
FROM course
GROUP BY department_id\G
*************************** 1. row ***************************
CourseIDs: {"course_ids": "CS132,CS241,CS242,CS411,CS413,CS511,CS421,CS422,CS441
,CS442,CS443,CS535,CS532,CS111,CS112,CS113,CS121,CS122,CS123,CS131,CS531,CS141,C
S142,CS211,CS212,CS213,CS221,CS222,CS231,CS232,CS235"}
  Courses: {"CS111": "Introduction to Operating Systems", "CS112": "Introduction
 to Real Time Operating Systems", "CS113": "Introduction to Computer Architectur
e", "CS121": "Introduction to Databases", "CS122": "Relational Databases", "CS12
3": "Introduction to Structured Query Language", "CS131": "Introduction to Data
Structures", "CS132": "Introduction to Algorithms", "CS141": "Statistical Machin
e Learning", "CS142": "Theory of Machine Learning", "CS211": "Operating Systems:
 Intermediate", "CS212": "Real Time Operating Systems: Intermediate", "CS213": "
Computer Architecture: Intermediate", "CS221": "Intermediate: Structured Query L
anguage", "CS222": "Intermediate PostgreSQL", "CS231": "Data Structures: Interme
diate Level", "CS232": "Algorithms: Intermediate Level", "CS235": "Planar Graphs
", "CS241": "Reinforcement Learning", "CS242": "Theory of Deep Learning", "CS411
": "Advanced Operating Systems", "CS413": "Advanced Computer Architecture", "CS4
21": "Advanced Database Concepts", "CS422": "Advanced Relational Databases", "CS
441": "Advanced Machine Learning", "CS442": "Advances in Deep Learning", "CS443"
: "Advanced Reinforcement Learning", "CS511": "Distributed Systems", "CS531": "A
dvanced Data Structures", "CS532": "Advanced Algorithms", "CS535": "Non-Planar G
raphs"}
*************************** 2. row ***************************

We get a JSON object format for the course ids, and the corresponding courses, grouped by department ID.

5. Conclusion

In this article, we learned about creating JSON format with GROUP_CONCAT() in MySQL. We can create any of the JSON formats such as an array, or an object, by using GROUP_CONCAT() by itself. We can further simplify the syntax and usage by combining the function with JSON functions.