1

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(
        ...
      )
    ),
    ']'
  )

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.