PostgreSQL重命名jsonb字段中的属性

Question

在postgresql 9.5中，是否有一种方法可以重命名jsonb字段中的属性？

例如：

{ "nme" : "test" }

应该被重新命名为

{ "name" : "test"}

- T. Kong

---

请查看字符串替换（https://dba.stackexchange.com/a/172601/41320） - Ilya Serbis

类似的问题已经加入 https://stackoverflow.com/questions/59908676/write-a-postgresql-query-to-update-a-json-column/59908979#59908979 - Akshay Vijay Jain

Answer 1

在 UPDATE 中使用 删除 (-) 和连接 (||) 操作符，例如：

create table example(id int primary key, js jsonb);
insert into example values
    (1, '{"nme": "test"}'),
    (2, '{"nme": "second test"}');

update example
set js = js - 'nme' || jsonb_build_object('name', js->'nme')
where js ? 'nme'
returning *;

 id |           js            
----+-------------------------
  1 | {"name": "test"}
  2 | {"name": "second test"}
(2 rows)

- klin

---

有人可能会想知道，查询语句中的 returning * 并非必需品。它只会导致显示所有更新过的行，就像 select 命令一样。 - Manu CJ

@klin：我尝试了你的查询，它可以工作。但是有没有办法让我们在{}中获取ids，而不仅仅是在双引号中提到？ http://rextester.com/HQS81928 - Pranav Unde

@PranavUnde - 请用适当的示例提出一个新问题。 - klin

它可以正常工作，您能否告诉我如何更新嵌套对象，例如{"skill":{"c":{"name":"C", "code":"c"}}}，如何更新skill中的c属性。 - Mahesh

找到了解决方案，首先添加带有新属性的对象，然后删除现有属性。就像这样。update example SET js = JSONB_SET(js::JSONB, '{skill,c++}', js->'skill'->'c', true) where js->'skill' ? 'c';update example SET js = js#-'{skill, c}' where js->'skill' ? 'c'; - Mahesh

太棒了，谢谢！这里有一个Rails ActiveRecord的例子，希望对任何需要的人有所帮助：https://www.todayilearned.fyi/by/odlp/til-you-can-rename-postgresql-jsonb-attributes-in-a-query - odlp

Answer 2

我使用以下方法处理嵌套属性，并跳过任何不使用旧名称的json：

UPDATE table_name
SET json_field_name = jsonb_set(json_field_name #- '{path,to,old_name}',
                                '{path,to,new_name}',
                                json_field_name#>'{path,to,old_name}')
WHERE json_field_name#>'{path,to}' ? 'old_name';

仅供参考文档：

• #- 删除指定路径下的字段或元素（对于 JSON 数组，负整数从结尾开始计算） https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSONB-OP-TABLE

• #> 获取指定路径下的 JSON 对象 https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-OP-TABLE

- emlai

---

那对我起作用了，尽管我在 {path, to, old_name} 的第一个条目上搞砸了，但似乎并没有影响结果，我仍然得到了正确的东西。 - Matheus Felipe

@MatheusFelipe，请帮我一个忙。我需要将LicenseDate更改为LicenseExpirationDateUPDATE dbo."dcp_OrgLevelEntityItems" SET "Attributes" = jsonb_set("Attributes" => '{LicenseDate}', '{LicenseExpirationDate}') WHERE "Id" = 1JSON示例 - 列名为Attributes{ "Name": "LIC", "Street": 223871, "City": 59390, "LicenseDate": "01092019" } - Velkumar

Answer 3

这是一个老问题，但在搜索排名中仍位居前列。虽然不是很符合JSON的特点，但如果 key 名称冲突的风险很小，一种可行的解决方案是将字段处理为 TEXT，进行替换（也可以使用正则表达式），然后转换回 JSON。类似@klin的设置，可以使用以下内容：

CREATE TABLE example(id INT PRIMARY KEY, js JSONB);
INSERT INTO example VALUES
    (1, '{"nme": "test"}'),
    (2, '{"nme": "second test"}');

UPDATE EXAMPLE
SET js = (REPLACE(js::TEXT, '"nme"', '"name"'))::JSONB
RETURNING *;

- rocksteady