mysql - 如何将 select 语句结果转换为 JSONARRAY 并更新另一个表

Question

我想将select结果转换为JSON并写入另一个表:

update patrol_patrol a, position_user b
set a.route = json_array(select coordinate from b )
where a.id = 1;

并得到错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select coordinate from b ) where a.id = 1' at line 2 

 select route from patrol_patrol;                                                                                                                                                    
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| route                                                                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ["112.58006496213066,22.311484443420195"] |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

 select coordinate from position_user;                                                                                                                                                    
+---------------------------------------+
| coordinate                            |
+---------------------------------------+
| 112.701036,22.738611                  |
| 112.701036,22.738632                  |
| 112.701036,22.738632                  |
| 112.701036,22.738652                  

position_user.coordinate 更新后应该是["112.701036,22.738611", "112.701036,22.738632", "112.701036,22.738652", ....]

Answer 1

由于您只更新 patrol_patrol 表，因此您应该只将其包含在更新语句的第一部分中。为了得到你要找的东西，我建议使用 JSON_ARRAYAGG函数，它将您的结果合并到一个数组中，然后可用于将结果分配给 a.route:

UPDATE patrol_patrol a
SET a.route = (SELECT JSON_ARRAYAGG(coordinate) FROM position_user)
WHERE a.id = 1;

可以找到 dbfiddle here演示这种方法。