Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
+----+---------+-------------+
| id | user_id | model |
+----+---------+-------------+
| 1 | 1 | Tesla |
| 2 | 1 | Ferrari |
| 3 | 2 | Lamborghini |
+----+---------+-------------+
phone:
+----+---------+----------+--------+
| id | user_id | company | number |
+----+---------+----------+--------+
| 1 | 1 | Verzion | 1 |
| 2 | 1 | AT&T | 2 |
| 3 | 1 | T-Mobile | 3 |
| 4 | 2 | Sprint | 4 |
| 5 | 1 | Sprint | 2 |
+----+---------+----------+--------+
1. The brackets [] are omitted
For example this query that gets users with their list of cars:
SELECT
user.id AS id,
user.name AS name,
JSON_ARRAYAGG(
JSON_OBJECT(
'id', car.id,
'model', car.model
) AS cars
FROM user
INNER JOIN car ON user.id = car.user_id
GROUP BY user.id;
Result: brackets [] were omitted in cars (JSON_ARRAYAGG has the behavior similar to GROUP_CONCAT)
+----+------+-----------------------------------------------------------+
| id | name | cars |
+----+------+-----------------------------------------------------------+
| 1 | Jhon | {"id": 1, "model": "Tesla"},{"id": 2, "model": "Ferrari"} |
| 2 | Bob | {"id": 3, "model": "Lamborghini"} |
+----+------+-----------------------------------------------------------+
However when adding the filter WHERE user.id = 1, the brackets [] are not omitted:
+----+------+-------------------------------------------------------------+
| id | name | cars |
+----+------+-------------------------------------------------------------+
| 1 | Jhon | [{"id": 1, "model": "Tesla"},{"id": 2, "model": "Ferrari"}] |
+----+------+-------------------------------------------------------------+
2. Incorrect wrong result, values are duplicates or omitted
This error is strange as the following conditions must be met:
Consult more than 2 tables
The DISTINCT option must be used
A user has at least 2 cars and at least 3 phones.
Duplicate values
for example, this query that gets users with their car list and their phone list:
SELECT
user.id AS id,
user.name AS name,
JSON_ARRAYAGG( DISTINCT
JSON_OBJECT(
'id', car.id,
'model', car.model
) AS cars,
JSON_ARRAYAGG( DISTINCT
JSON_OBJECT(
'id', phone.id,
'company', phone.company,
'number', phone.number
) AS phones
FROM user
INNER JOIN car ON user.id = car.user_id
INNER JOIN phone ON user.id = phone.user_id
GROUP BY user.id;
I will leave the output in json format and I will only leave the elements that interest.
Result: brackets [] were omitted and duplicate Verizon
"id": 1,
"name": "Jhon",
"phones": // [ Opening bracket expected
"id": 5,
"company": "Sprint",
"number": 2
"id": 1,
"company": "Verzion",
"number": 1
"id": 1,
"company": "Verzion",
"number": 1
}, // Duplicate object with the DISTINCT option
"id": 2,
"company": "AT&T",
"number": 2
"id": 3,
"company": "T-Mobile",
"number": 3
// ] Closing bracket expected
Omitted values
This error occurs when omit phone.id is omitted in the query
SELECT
user.id AS id,
user.name AS name,
JSON_ARRAYAGG( DISTINCT
JSON_OBJECT(
'id', car.id,
'model', car.model
) AS cars,
JSON_ARRAYAGG( DISTINCT
JSON_OBJECT(
--'id', phone.id,
'company', phone.company,
'number', phone.number
) AS phones
FROM user
INNER JOIN car ON user.id = car.user_id
INNER JOIN phone ON user.id = phone.user_id
GROUP BY user.id;
Result: brackets [] were omitted and Sprint was omitted.
Apparently this happens because it makes an OR type between the columns of the JSON_OBJECT, since the company exists in a different row and number in a other different row
"id": 1,
"name": "Jhon",
"phones": // [ Opening bracket expected
// "company": "Sprint",
// "number": 2
//}, `Sprint` was omitted
"company": "Verzion",
"number": 1
"company": "AT&T",
"number": 2
"company": "T-Mobile",
"number": 3
// ] Closing bracket expected
GROUP_CONCAT instance of JSON_ARRAYAGG solves the problem of duplicate or omitted objects
However, by adding the filter WHERE user.id = 1, the brackets [] are not omitted and also the problem of duplicate or omitted objects is also solved:
"id": 1,
"name": "Jhon",
"phones": [
"id": 1,
"company": "Verzion",
"number": 1
"id": 2,
"company": "AT&T",
"number": 2
"id": 3,
"company": "T-Mobile",
"number": 3
"id": 5,
"company": "Sprint",
"number": 2
What am I doing wrong?
–
So far my solution is this, but I would like to use JSON_ARRAYAGG since the query is cleaner
SELECT
user.id AS id,
user.name AS name,
CONCAT(
GROUP_CONCAT( DISTINCT
JSON_OBJECT(
'id', car.id,
'model', car.model
) AS cars
FROM user
INNER JOIN car ON user.id = car.user_id
GROUP BY user.id;
SELECT
user.id AS id,
user.name AS name,
CONCAT(
GROUP_CONCAT( DISTINCT
JSON_OBJECT(
'id', car.id,
'model', car.model
) AS cars,
CONCAT(
GROUP_CONCAT( DISTINCT
JSON_OBJECT(
'id', phone.id,
'company', phone.company,
'number', phone.number
) AS phones
FROM user
INNER JOIN car ON user.id = car.user_id
INNER JOIN phone ON user.id = phone.user_id
GROUP BY user.id;
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.
