mysql - 有谁知道是否有办法在 JSON_ARRAYAGG() 中实现任何排序或过滤？

Question

大家好，有谁知道如何过滤数据或使用类似运算符对 JSON_ARRAYAGG() 中的数据进行排序，
我想在以下查询中添加 Like 运算符。我是 MySQL 新手，如何实现这一点

SELECT s.state_id,s.state,
JSON_ARRAYAGG(JSON_OBJECT('city',c.city, 'city_id',c.city_id) /* i want to add like Operator here*/) 
AS 'cities' 
FROM `cities` AS c JOIN states AS s 
WHERE s.state_id = c.state_id GROUP BY State.

我想使用 like 运算符对城市进行排序
这些是 citi 和 state 表的创建表模式:

create table `cities` (
    `city_id` int (11),
    `city` varchar (270),
    `state_id` int (11),
    `is_active` tinyint (1),
    `created_at` datetime ,
    `updated_at` datetime 
); 
insert into `cities` (`city_id`, `city`, `state_id`, `is_active`, `created_at`, `updated_at`) values('1','Port Blair*','1','1','2021-03-31 07:25:28','2021-03-31 07:25:28');
insert into `cities` (`city_id`, `city`, `state_id`, `is_active`, `created_at`, `updated_at`) values('2','Adoni','2','1','2021-03-31 07:25:28','2021-03-31 07:25:28');
insert into `cities` (`city_id`, `city`, `state_id`, `is_active`, `created_at`, `updated_at`) values('3','Amalapuram','2','1','2021-03-31 07:25:28','2021-03-31 07:25:28');
insert into `cities` (`city_id`, `city`, `state_id`, `is_active`, `created_at`, `updated_at`) values('4','Anakapalle','2','1','2021-03-31 07:25:28','2021-03-31 07:25:28');
insert into `cities` (`city_id`, `city`, `state_id`, `is_active`, `created_at`, `updated_at`) values('5','Anantapur','2','1','2021-03-31 07:25:28','2021-03-31 07:25:28');
insert into `cities` (`city_id`, `city`, `state_id`, `is_active`, `created_at`, `updated_at`) values('6','Bapatla','2','1','2021-03-31 07:25:28','2021-03-31 07:25:28');
insert into `cities` (`city_id`, `city`, `state_id`, `is_active`, `created_at`, `updated_at`) values('7','Bheemunipatnam','2','1','2021-03-31 07:25:28','2021-03-31 07:25:28');

状态表:

create table `states` (
    `state_id` int (11),
    `state` varchar (180),
    `is_active` tinyint (1),
    `created_at` datetime ,
    `updated_at` datetime 
); 
insert into `states` (`state_id`, `state`, `is_active`, `created_at`, `updated_at`) values('1','Andaman and Nicobar Islands','1','2021-03-31 06:30:04','2021-03-31 06:30:04');
insert into `states` (`state_id`, `state`, `is_active`, `created_at`, `updated_at`) values('2','Andhra Pradesh','1','2021-03-31 06:30:04','2021-03-31 06:30:04');
insert into `states` (`state_id`, `state`, `is_active`, `created_at`, `updated_at`) values('3','Arunachal Pradesh','1','2021-03-31 06:30:04','2021-03-31 06:30:04');
insert into `states` (`state_id`, `state`, `is_active`, `created_at`, `updated_at`) values('4','Assam','1','2021-03-31 06:30:04','2021-03-31 06:30:04');
insert into `states` (`state_id`, `state`, `is_active`, `created_at`, `updated_at`) values('5','Bihar','1','2021-03-31 06:30:04','2021-03-31 06:30:04');
insert into `states` (`state_id`, `state`, `is_active`, `created_at`, `updated_at`) values('6','Chandigarh','1','2021-03-31 06:30:04','2021-03-31 06:30:04');
insert into `states` (`state_id`, `state`, `is_active`, `created_at`, `updated_at`) values('7','Chhattisgarh','1','2021-03-31 06:30:04','2021-03-31 06:30:04');
insert into `states` (`state_id`, `state`, `is_active`, `created_at`, `updated_at`) values('8','Dadra and Nagar Haveli','1','2021-03-31 06:30:04','2021-03-31 06:30:04');
insert into `states` (`state_id`, `state`, `is_active`, `created_at`, `updated_at`) values('9','Delhi','1','2021-03-31 06:30:04','2021-03-31 06:30:04');
insert into `states` (`state_id`, `state`, `is_active`, `created_at`, `updated_at`) values('10','Goa','1','2021-03-31 06:30:04','2021-03-31 06:30:04');
insert into `states` (`state_id`, `state`, `is_active`, `created_at`, `updated_at`) values('11','Gujarat','1','2021-03-31 06:30:04','2021-03-31 06:30:04');
insert into `states` (`state_id`, `state`, `is_active`, `created_at`, `updated_at`) values('12','Haryana','1','2021-03-31 06:30:04','2021-03-31 06:30:04');
insert into `states` (`state_id`, `state`, `is_active`, `created_at`, `updated_at`) values('13','Himachal Pradesh','1','2021-03-31 06:30:04','2021-03-31 06:30:04');

fiddle

Answer 1

https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html#function_json-arrayagg说:

JSON_ARRAYAGG(col_or_expr)

Aggregates a result set as a single JSON array whose elements consist of the rows. The order of elements in this array is undefined.

https://bugs.mysql.com/bug.php?id=94696是 2019 年的一项功能请求，要求在使用 JSON_ARRAYAGG() 聚合之前对元素进行排序的方法.该票证处于“已验证”状态，这意味着它已被接受为功能请求，但尚未实现。
GROUP_CONCAT() 聚合函数有一个可选的 ORDER BY 子句，您可以在函数调用中使用，但其他聚合函数都没有这样的子句。
您可以利用一个非官方的方法:因为 InnoDB 表根据用于访问表的索引按索引顺序读取行，因此您可以使用索引使其使用特定索引。
下面是测试数据表:

mysql> select * from mytable;
+----+-------------+------+
| id | text        | x    |
+----+-------------+------+
|  1 | longing     | NULL |
|  2 | rusted      | NULL |
|  3 | seventeen   | NULL |
|  4 | daybreak    | NULL |
|  5 | furnace     | NULL |
|  6 | nine        | NULL |
|  7 | benign      | NULL |
|  8 | homecoming  | NULL |
|  9 | one         | NULL |
| 10 | freight car | NULL |
+----+-------------+------+

在这里，我们看到我的表中的一些单词按字母顺序返回:

mysql> select json_arrayagg(text) from mytable;
+-----------------------------------------------------------------------------------------------------------------+
| json_arrayagg(text)                                                                                             |
+-----------------------------------------------------------------------------------------------------------------+
| ["benign", "daybreak", "freight car", "furnace", "homecoming", "longing", "nine", "one", "rusted", "seventeen"] |
+-----------------------------------------------------------------------------------------------------------------+

原因是查询在 text 上使用了索引。列作为访问顺序。因为我只提取了一列，所以它能够进行索引扫描。

mysql> explain select json_arrayagg(text) from mytable;
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | mytable | NULL       | index | NULL          | text | 83      | NULL |   10 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+

而如果我包含另一个非索引列，它会执行表扫描，按主键顺序读取行:

mysql> explain select x, json_arrayagg(text) from mytable;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | mytable | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

结果是数组按主键顺序聚合，而不是按字母顺序:

mysql> select x, json_arrayagg(text) from mytable;
+------+-----------------------------------------------------------------------------------------------------------------+
| x    | json_arrayagg(text)                                                                                             |
+------+-----------------------------------------------------------------------------------------------------------------+
| NULL | ["longing", "rusted", "seventeen", "daybreak", "furnace", "nine", "benign", "homecoming", "one", "freight car"] |
+------+-----------------------------------------------------------------------------------------------------------------+

我可以制作另一个索引来强制它使用另一个订单吗？

mysql> update mytable set x = 11 - id;
Query OK, 10 rows affected (0.03 sec)

mysql> select * from mytable order by id;
+----+-------------+------+
| id | text        | x    |
+----+-------------+------+
|  1 | longing     |   10 |
|  2 | rusted      |    9 |
|  3 | seventeen   |    8 |
|  4 | daybreak    |    7 |
|  5 | furnace     |    6 |
|  6 | nine        |    5 |
|  7 | benign      |    4 |
|  8 | homecoming  |    3 |
|  9 | one         |    2 |
| 10 | freight car |    1 |
+----+-------------+------+

mysql> alter table mytable add key (x, text);

EXPLAIN 显示如果我只引用该索引中的两列，它将更喜欢进行索引扫描:

mysql> explain select x, json_arrayagg(text) from mytable;                                                                                                              
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | mytable | NULL       | index | NULL          | x    | 88      | NULL |   10 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+

因为 x是此索引中的第一个，它确定行聚合的顺序。

mysql> select x, json_arrayagg(text) from mytable;
+------+-----------------------------------------------------------------------------------------------------------------+
| x    | json_arrayagg(text)                                                                                             |
+------+-----------------------------------------------------------------------------------------------------------------+
|    1 | ["freight car", "one", "homecoming", "benign", "nine", "furnace", "daybreak", "seventeen", "rusted", "longing"] |
+------+-----------------------------------------------------------------------------------------------------------------+

如果你有更多的列，或者如果表是一个不是连接顺序中第一个的连接表，那么按照你想要的方式获取数组顺序可能会非常棘手——甚至不可能——同时仍然访问表很好的优化。