json - 如何使用 PostgreSQL 更新 jsonb 字符串？

Question

我正在使用 PostgreSQL 9.4.5。我想更新 jsonb 列。

我的表是这样构造的:

CREATE TABLE my_table (
  gid    serial PRIMARY KEY,
  "data" jsonb
);

JSON 字符串是这样的:

{"files": [], "ident": {"id": 1, "country": null, "type ": "20"}}

以下 SQL 无法完成工作(语法错误 - SQL 状态 = 42601):

UPDATE my_table SET "data" -> 'ident' -> 'country' = 'Belgium';

有什么办法可以实现吗？

Answer 1

好的，有两个函数:

create or replace function set_jsonb_value(p_j jsonb, p_key text, p_value jsonb) returns jsonb as $$
  select jsonb_object_agg(t.key, t.value) from (
    select 
      key, 
      case 
        when jsonb_typeof(value) = 'object' then set_jsonb_value(value, p_key, p_value)
        when key = p_key then p_value 
        else value 
      end as value from jsonb_each(p_j)) as t;
$$ language sql immutable;

第一个只是更改现有 key 的值，而不考虑 key 路径:

postgres=# select set_jsonb_value(
  '{"files": [], "country": null, "ident": {"id": 1, "country": null, "type ": "20"}}', 
  'country', 
  '"foo"');
                                   set_jsonb_value                                    
--------------------------------------------------------------------------------------
 {"files": [], "ident": {"id": 1, "type ": "20", "country": "foo"}, "country": "foo"}
(1 row)

create or replace function set_jsonb_value(p_j jsonb, p_path text[], p_value jsonb) returns jsonb as $$
  select jsonb_object_agg(t.key, t.value) from (
    select 
      key, 
      case
        when jsonb_typeof(value) = 'object' then set_jsonb_value(value, p_path[2:1000], p_value)
        when key = p_path[1] then p_value 
        else value 
      end as value from jsonb_each(p_j)
    union all
    select 
      p_path[1],
      case 
        when array_length(p_path,1) = 1 then p_value 
        else set_jsonb_value('{}', p_path[2:1000], p_value) end 
    where not p_j ? p_path[1]) as t;
$$ language sql immutable;

第二个使用指定的路径更改现有键的值，如果路径不存在则创建它:

postgres=# select set_jsonb_value(
  '{"files": [], "country": null, "ident": {"id": 1, "type ": "20"}}', 
  '{ident,country}'::text[], 
  '"foo"');
                                   set_jsonb_value                                   
-------------------------------------------------------------------------------------
 {"files": [], "ident": {"id": 1, "type ": "20", "country": "foo"}, "country": null}
(1 row)

postgres=# select set_jsonb_value(
  '{"files": [], "country": null, "ident": {"id": 1, "type ": "20"}}', 
  '{ident,foo,bar,country}'::text[], 
  '"foo"');
                                            set_jsonb_value                                            
-------------------------------------------------------------------------------------------------------
 {"files": [], "ident": {"id": 1, "foo": {"bar": {"country": "foo"}}, "type ": "20"}, "country": null}
(1 row)

希望对使用 PostgreSQL < 9.5
的人有所帮助免责声明:在 PostgreSQL 9.5 上测试