如何使用SQL将表中Numbers列的数组赋值给JSON字段内的数组？

你的表初始数据如下：

Details                             |   Numbers
[{headers: "test", numbers: []}]    |   [1, 2]
[{headers: "test", numbers: []}]    |   [3, 4]

想要实现将Numbers列的值赋值给Details数组内第一个元素的numbers属性，最终效果如下：

Details                                 |   Numbers
[{headers: "test", numbers: [1, 2]}]    |   [1, 2]
[{headers: "test", numbers: [3, 4]}]    |   [3, 4]

当然可以通过UPDATE脚本批量实现，以下是主流数据库的写法：

MySQL（5.7及以上版本） #

假设Details是JSON类型，Numbers是JSON数组类型，执行以下语句：

UPDATE your_table_name
SET Details = JSON_SET(
    Details,
    '$[0].numbers',
    Numbers
);

如果Numbers是字符串类型，需要先转换为JSON：

UPDATE your_table_name
SET Details = JSON_SET(
    Details,
    '$[0].numbers',
    CAST(Numbers AS JSON)
);

说明：$[0].numbers是JSON路径，定位到Details数组的第一个元素下的numbers属性，将其替换为Numbers列的值。

PostgreSQL（9.5及以上版本） #

假设Details是jsonb类型，Numbers是jsonb数组类型，执行以下语句：

UPDATE your_table_name
SET Details = jsonb_set(
    Details,
    '{0, numbers}',
    Numbers
);

如果Numbers是字符串类型，需要转换为jsonb：

UPDATE your_table_name
SET Details = jsonb_set(
    Details,
    '{0, numbers}',
    Numbers::jsonb
);

说明：'{0, numbers}'是JSON路径数组，0代表数组索引，numbers代表对应的键名，以此定位到要修改的属性并替换值。

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