MySQL按created_at排序时JSON_OBJECTAGG报NULL键错误的解决问询

问题重现 #

执行以下SQL查询时出现MySQL错误：JSON documents may not contain NULL member names

SELECT blueprints.*, JSON_OBJECTAGG(properties.ui_reference_name, blueprint_values.value) AS json_properties
FROM `blueprints`
LEFT OUTER JOIN `blueprint_values` ON `blueprint_values`.`blueprint_id` = `blueprints`.`id`
LEFT OUTER JOIN `properties` ON `properties`.`id` = `blueprint_values`.`property_id`
WHERE `blueprints`.`game_id` = 1
GROUP BY blueprints.id
ORDER BY `blueprints`.`created_at` DESC
LIMIT 1 OFFSET 0

对应的执行计划：

+----+-------------+------------------+------------+--------+----------------------------------------+----------------------------------------+---------+-----------------------------------------+-------+----------+---------------------------------+
| id | select_type | table            | partitions | type   | possible_keys                          | key                                    | key_len | ref                                     | rows  | filtered | Extra                           |
+----+-------------+------------------+------------+--------+----------------------------------------+----------------------------------------+---------+-----------------------------------------+-------+----------+---------------------------------+
|  1 | SIMPLE      | blueprints       | NULL       | ref    | PRIMARY,index_blueprints_on_slug       | index_blueprints_on_game_id            | 8       | const                                   | 92108 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | blueprint_values | NULL       | ref    | index_blueprint_values_on_blueprint_id | index_blueprint_values_on_blueprint_id | 8       | cardtrader.blueprints.id                |     8 |   100.00 | NULL                            |
|  1 | SIMPLE      | properties       | NULL       | eq_ref | PRIMARY                                | PRIMARY                                | 8       | cardtrader.blueprint_values.property_id |     1 |   100.00 | NULL                            |
+----+-------------+------------------+------------+--------+----------------------------------------+----------------------------------------+---------+-----------------------------------------+-------+----------+---------------------------------+

将排序字段改为id后查询可正常执行，对应的执行计划：

+----+-------------+------------------+------------+--------+----------------------------------------+----------------------------------------+---------+-----------------------------------------+-------+----------+----------------+
| id | select_type | table            | partitions | type   | possible_keys                          | key                                    | key_len | ref                                     | rows  | filtered | Extra          |
+----+-------------+------------------+------------+--------+----------------------------------------+----------------------------------------+---------+-----------------------------------------+-------+----------+----------------+
|  1 | SIMPLE      | blueprints       | NULL       | ref    | PRIMARY,index_blueprints_on_slug       | index_blueprints_on_game_id            | 8       | const                                   | 92108 |   100.00 | Using filesort |
|  1 | SIMPLE      | blueprint_values | NULL       | ref    | index_blueprint_values_on_blueprint_id | index_blueprint_values_on_blueprint_id | 8       | cardtrader.blueprints.id                |     8 |   100.00 | NULL           |
|  1 | SIMPLE      | properties       | NULL       | eq_ref | PRIMARY                                | PRIMARY                                | 8       | cardtrader.blueprint_values.property_id |     1 |   100.00 | NULL           |
+----+-------------+------------------+------------+--------+----------------------------------------+----------------------------------------+---------+-----------------------------------------+-------+----------+----------------+

两个执行计划的差异仅为：前者Extra字段是Using temporary; Using filesort，后者是Using filesort。

错误原因 #

1. 由于使用了LEFT OUTER JOIN，当某个蓝图没有对应的blueprint_values记录，或者blueprint_values关联的properties记录不存在时，properties.ui_reference_name会被置为NULL。
2. MySQL的JSON_OBJECTAGG函数不允许将NULL作为JSON对象的键，这是触发错误的核心原因。
3. 按created_at排序时，执行计划出现Using temporary，说明MySQL会先创建临时表存储分组后的结果，再进行排序。在这个过程中，所有关联后的行（包括ui_reference_name为NULL的行）都会被纳入JSON_OBJECTAGG的计算，从而触发NULL键的错误。
4. 按id排序时，执行计划没有Using temporary，因为id是blueprints表的主键，MySQL可以利用主键的有序性直接对分组结果排序，处理过程中会自动跳过或过滤掉导致ui_reference_name为NULL的无效关联行，因此不会触发错误。

修复方案（保持按created_at排序） #

方案1：过滤NULL键的行 #

添加条件排除ui_reference_name为NULL的记录，避免JSON_OBJECTAGG处理NULL键：

SELECT blueprints.*, JSON_OBJECTAGG(properties.ui_reference_name, blueprint_values.value) AS json_properties
FROM `blueprints`
LEFT OUTER JOIN `blueprint_values` ON `blueprint_values`.`blueprint_id` = `blueprints`.`id`
LEFT OUTER JOIN `properties` ON `properties`.`id` = `blueprint_values`.`property_id`
WHERE `blueprints`.`game_id` = 1
  AND properties.ui_reference_name IS NOT NULL
GROUP BY blueprints.id
ORDER BY `blueprints`.`created_at` DESC
LIMIT 1 OFFSET 0

方案2：给NULL键设置默认值 #

使用IFNULL函数将NULL的ui_reference_name替换为一个默认字符串，确保JSON键合法：

SELECT blueprints.*, JSON_OBJECTAGG(IFNULL(properties.ui_reference_name, 'unknown_property'), blueprint_values.value) AS json_properties
FROM `blueprints`
LEFT OUTER JOIN `blueprint_values` ON `blueprint_values`.`blueprint_id` = `blueprints`.`id`
LEFT OUTER JOIN `properties` ON `properties`.`id` = `blueprint_values`.`property_id`
WHERE `blueprints`.`game_id` = 1
GROUP BY blueprints.id
ORDER BY `blueprints`.`created_at` DESC
LIMIT 1 OFFSET 0

方案3：先排序筛选再关联分组 #

先通过子查询获取按created_at排序后的目标蓝图，再关联其他表进行分组计算，避免临时表处理时的NULL键问题：

SELECT b.*, JSON_OBJECTAGG(p.ui_reference_name, bv.value) AS json_properties
FROM (
    SELECT * FROM `blueprints` 
    WHERE `game_id` = 1 
    ORDER BY `created_at` DESC 
    LIMIT 1 OFFSET 0
) b
LEFT OUTER JOIN `blueprint_values` bv ON bv.`blueprint_id` = b.`id`
LEFT OUTER JOIN `properties` p ON p.`id` = bv.`property_id`
GROUP BY b.id

内容的提问来源于stack exchange，提问作者Pioz