时间戳

首页 > 解决方案 > MariaDB JSON_ARRAYAGG 给出错误的结果

mariadb - MariaDB JSON_ARRAYAGG 给出错误的结果

问题描述

MariaDB 15.1 使用时有 2 个问题JSON_ARRAYAGG

括号[]被省略

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

我的数据库如下：

用户：

+----+------+
| 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.不正确的错误结果，值重复或省略

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

重复值

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

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被省略。

显然，发生这种情况是因为它在 的列之间产生了 OR 类型JSON_OBJECT，因为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对象的问题

但是，通过添加 filter WHERE user.id = 1，括号[]没有省略，也解决了duplicateoromitted对象的问题：

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

我究竟做错了什么？

标签： mariadbdistinctjson-arrayagg

解决方案

到目前为止，我的解决方案是这样，但我想使用 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;

推荐阅读