时间戳

首页 > 解决方案 > 将 json Array 转换为 Json 对象并在 MySQL 上向其添加更多项目

mysql - 将 json Array 转换为 Json 对象并在 MySQL 上向其添加更多项目

问题描述

在 MySQL 表中，如何使用先前的值将当前保存 JSON 数组值的列的值转换为 JSON 对象数组？

当前列（仅包含 ID）：

items
-------

[1,2,3]
[2,3]
...

所需格式：


items
-------

[ { id:1, lastUpdatedBy:"", timeStamp:"--" },{ id:2, lastUpdatedBy:"", timeStamp:"--" },{ id:3, lastUpdatedBy:"", timeStamp:"--" }]
[  { id:2, lastUpdatedBy:"", timeStamp:"--" },{ id:3, lastUpdatedBy:"", timeStamp:"--" }]
...

标签： mysqlsqljsondatabasegroup-by

解决方案

如果您运行的是 MySQL 8.0，您可以使用 取消嵌套数组json_table()，然后构建 json 对象并使用 聚合json_arrayagg()。

为此，您需要一个主键 - 我假设pk：

select json_arrayagg(json_object(
    'id', i.id, 
    'lastUpdatedBy', 12, 
    'timestamp', '--'
)) items
from mytable t
cross join json_table(t.items, '$[*]' columns (id int path '$')) i 
group by t.pk

如果你想update查询：

update mytable t
inner join (
    select json_arrayagg(json_object(
        'id', i.id, 
        'lastUpdatedBy', 12, 
        'timestamp', '--'
    )) items
    from mytable t
    cross join json_table(t.items, '$[*]' columns (id int path '$')) i 
    group by t.pk
) x on x.pk = t.pk
set x.items = t.items

推荐阅读