时间戳

首页 > 解决方案 > Postgres SQL向所有json数组添加键

arrays - Postgres SQL向所有json数组添加键

问题描述

在我的数据库中，我有一个表，该表有一个名为 的列items，该列具有jsonb类型，几乎每条记录都有这样的数据：

{"items": [{"id": "item-id", "sku": "some-sku", "quantity": 1, "master_sku": "some-master-sku"}]}

我需要创建一个添加type到每个项目的迁移，所以它应该如下所示：

{"items": [{"id": "item-id", "sku": "some-sku", "quantity": 1, "master_sku": "some-master-sku", "type": "product"}]}

我必须添加type到每条记录，每条记录看起来都一样。问题是，我的表中有大约百万条记录，我无法迭代每个项目并添加新项目，type因为它可能需要很长时间并且我的部署脚本可能会崩溃。

伙计们，我怎样才能以最简单的方式做到这一点？

标签： arraysjsonpostgresqljsonb

解决方案

只要格式/内容总是或多或少相同，最快的方法可能是字符串操作，如下所示：

UPDATE your_table
SET your_field = REGEXP_REPLACE(your_field::TEXT, '\}(.+?)', ', "type": "product"}\1', 'g')::JSONB
WHERE your_field IS NOT NULL;

示例：https ://www.db-fiddle.com/f/fsHoFKz9szpmV5aF4dt7r/1

这只是检查}后面跟着某物的任何字符（所以我们知道它不是“items”对象中的最后一个字符，并将其替换为缺少的键/值（以及后面的任何字符）。

当然，这实际上不会执行任何验证，例如该键/值是否已经存在。您希望在性能和正确性之间处于什么位置取决于您对数据的了解。

推荐阅读