distinct – MariaDB JSON_ARRAYAGG gives wrong result

I have 2 problems in MariaDB 15.1 when using JSON_ARRAYAGG


1. The brackets () are omitted
2. wrong result, values are duplicates or omitted

My database is the following:

user:

+----+------+
| id | name |
+----+------+
|  1 | Jhon |
|  2 | Bob  |
+----+------+

car:

+----+---------+-------------+
| 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
  CONCAT(
    '(',
    GROUP_CONCAT( DISTINCT   
      JSON_OBJECT(
        ...
      )
    ),
    ')'
  )