MariaDB JSON_ARRAYAGG给出错误的结果

Question

我在使用MariaDB 15.1时遇到了两个问题

省略括号[]

不正确的结果，则值重复或省略。

我的数据库如下：

用户：

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

汽车：

+----+---------+-------------+
| id | user_id | model       |
+----+---------+-------------+
|  1 |       1 | Tesla       |
|  2 |       1 | Ferrari     |
|  3 |       2 | Lamborghini |
+----+---------+-------------+

电话：

+----+---------+----------+--------+
| 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.省略括号[]

例如，这个获得用户汽车列表的查询：

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;

结果:在[]中省略了括号cars (JSON_ARRAYAGG的行为类似于GROUP_CONCAT)。

+----+------+-----------------------------------------------------------+
| id | name | cars                                                      |
+----+------+-----------------------------------------------------------+
|  1 | Jhon | {"id": 1, "model": "Tesla"},{"id": 2, "model": "Ferrari"} |
|  2 | Bob  | {"id": 3, "model": "Lamborghini"}                         |
+----+------+-----------------------------------------------------------+

但是，在添加筛选器WHERE user.id = 1时，不省略方括号[]：

+----+------+-------------------------------------------------------------+
| id | name | cars                                                        |
+----+------+-------------------------------------------------------------+
|  1 | Jhon | [{"id": 1, "model": "Tesla"},{"id": 2, "model": "Ferrari"}] |
+----+------+-------------------------------------------------------------+

2.不正确的错误结果，值重复或省略。

这个错误很奇怪，因为必须满足以下条件：

• 查阅超过2个表
• DISTINCT选项必须使用
• 用户至少有2辆汽车和至少3部手机。

重复值

例如，该查询获取用户的汽车列表和电话列表：

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;

我将以json格式保留输出，只留下感兴趣的元素。

结果:省略括号[]，重复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
}

省略值

如果查询中省略了phone.id，则会发生此错误。

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;

结果:省略托槽[]，省略Sprint。

显然，这是因为它在JSON_OBJECT的列之间形成了OR类型，因为company存在于不同的行中，而number存在于其他的行中。

{
  "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实例JSON_ARRAYAGG解决了duplicate或omitted对象的问题

但是，通过添加筛选器WHERE user.id = 1，不省略方括号[]，也解决了duplicate或omitted对象的问题：

{
  "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
    }
  ]
}

我做错了什么？

Answer 1

到目前为止，我的解决方案是这样的，但是我想使用JSON_ARRAYAGG，因为查询更干净

-- 1
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;

-- 2

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;