如何修改新的 PostgreSQL JSON 数据类型中的字段？

How do I modify fields inside the new PostgreSQL JSON datatype?

提问by user9645

With postgresql 9.3 I can SELECT specific fields of a JSON data type, but how do you modify them using UPDATE? I can't find any examples of this in the postgresql documentation, or anywhere online. I have tried the obvious:

使用 postgresql 9.3 我可以选择 JSON 数据类型的特定字段，但是如何使用 UPDATE 修改它们？我在 postgresql 文档或在线任何地方都找不到任何此类示例。我已经尝试过显而易见的：

postgres=# create table test (data json);
CREATE TABLE
postgres=# insert into test (data) values ('{"a":1,"b":2}');
INSERT 0 1
postgres=# select data->'a' from test where data->>'b' = '2';
 ?column?
----------
 1
(1 row)
postgres=# update test set data->'a' = to_json(5) where data->>'b' = '2';
ERROR:  syntax error at or near "->"
LINE 1: update test set data->'a' = to_json(5) where data->>'b' = '2...

回答by pozs

Update: With PostgreSQL 9.5, there are some jsonbmanipulation functionality within PostgreSQL itself (but none for json; casts are required to manipulate jsonvalues).

更新：在 PostgreSQL 9.5 中，jsonbPostgreSQL 本身有一些操作功能（但没有用于json; 需要强制转换来操作json值）。

Merging 2 (or more) JSON objects (or concatenating arrays):

合并 2 个（或更多）JSON 对象（或连接数组）：

SELECT jsonb '{"a":1}' || jsonb '{"b":2}', -- will yield jsonb '{"a":1,"b":2}'
       jsonb '["a",1]' || jsonb '["b",2]'  -- will yield jsonb '["a",1,"b",2]'

So, setting a simple keycan be done using:

因此，可以使用以下方法设置一个简单的键：

SELECT jsonb '{"a":1}' || jsonb_build_object('<key>', '<value>')

Where <key>should be string, and <value>can be whatever type to_jsonb()accepts.

哪里<key>应该是字符串，<value>可以是任何类型to_jsonb()接受。

For setting a value deep in a JSON hierarchy, the jsonb_set()function can be used:

为了在 JSON 层次结构中设置一个值，jsonb_set()可以使用该函数：

SELECT jsonb_set('{"a":[null,{"b":[]}]}', '{a,1,b,0}', jsonb '{"c":3}')
-- will yield jsonb '{"a":[null,{"b":[{"c":3}]}]}'

Full parameter list of jsonb_set():

的完整参数列表jsonb_set()：

jsonb_set(target         jsonb,
          path           text[],
          new_value      jsonb,
          create_missing boolean default true)

pathcan contain JSON array indexes too & negative integers that appear there count from the end of JSON arrays. However, a non-existing, but positive JSON array index will append the element to the end of the array:

path也可以包含 JSON 数组索引和从 JSON 数组末尾开始计数的负整数。但是，一个不存在但为正的 JSON 数组索引会将元素附加到数组的末尾：

SELECT jsonb_set('{"a":[null,{"b":[1,2]}]}', '{a,1,b,1000}', jsonb '3', true)
-- will yield jsonb '{"a":[null,{"b":[1,2,3]}]}'

For inserting into JSON array (while preserving all of the original values), the jsonb_insert()function can be used (in 9.6+; this function only, in this section):

对于插入 JSON 数组（同时保留所有原始值），jsonb_insert()可以使用该函数（在 9.6+ 中；仅此函数，在本节中）：

SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2')
-- will yield jsonb '{"a":[null,{"b":[2,1]}]}', and
SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2', true)
-- will yield jsonb '{"a":[null,{"b":[1,2]}]}'

Full parameter list of jsonb_insert():

的完整参数列表jsonb_insert()：

jsonb_insert(target       jsonb,
             path         text[],
             new_value    jsonb,
             insert_after boolean default false)

Again, negative integers that appear in pathcount from the end of JSON arrays.

同样，path从 JSON 数组的末尾开始计数的负整数。

So, f.ex. appending to an end of a JSON array can be done with:

所以，例如 附加到 JSON 数组的末尾可以通过以下方式完成：

SELECT jsonb_insert('{"a":[null,{"b":[1,2]}]}', '{a,1,b,-1}', jsonb '3', true)
-- will yield jsonb '{"a":[null,{"b":[1,2,3]}]}', and

However, this function is working slightly differently (than jsonb_set()) when the pathin targetis a JSON object's key. In that case, it will only add a new key-value pair for the JSON object when the key is not used. If it's used, it will raise an error:

但是，jsonb_set()当pathintarget是 JSON 对象的键时，此函数的工作方式略有不同（与）。在这种情况下，它只会在未使用密钥时为 JSON 对象添加新的键值对。如果使用它，它将引发错误：

SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,c}', jsonb '[2]')
-- will yield jsonb '{"a":[null,{"b":[1],"c":[2]}]}', but
SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b}', jsonb '[2]')
-- will raise SQLSTATE 22023 (invalid_parameter_value): cannot replace existing key

Deleting a key (or an index)from a JSON object (or, from an array) can be done with the -operator:

可以使用-运算符从 JSON 对象（或从数组）中删除键（或索引）：

SELECT jsonb '{"a":1,"b":2}' - 'a', -- will yield jsonb '{"b":2}'
       jsonb '["a",1,"b",2]' - 1    -- will yield jsonb '["a","b",2]'

Deleting, from deep in a JSON hierarchycan be done with the #-operator:

可以使用#-运算符从 JSON 层次结构的深处删除：

SELECT '{"a":[null,{"b":[3.14]}]}' #- '{a,1,b,0}'
-- will yield jsonb '{"a":[null,{"b":[]}]}'

For 9.4, you can use a modified version of the original answer (below), but instead of aggregating a JSON string, you can aggregate into a json object directly with json_object_agg().

对于 9.4，您可以使用原始答案的修改版本（如下），但不是聚合 JSON 字符串，而是可以直接使用 聚合到 json 对象中json_object_agg()。

Original answer: It is possible (without plpython or plv8) in pure SQL too (but needs 9.3+, will not work with 9.2)

原始答案：在纯 SQL 中也可以（没有 plpython 或 plv8）（但需要 9.3+，不适用于 9.2）

CREATE OR REPLACE FUNCTION "json_object_set_key"(
  "json"          json,
  "key_to_set"    TEXT,
  "value_to_set"  anyelement
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json
  FROM (SELECT *
          FROM json_each("json")
         WHERE "key" <> "key_to_set"
         UNION ALL
        SELECT "key_to_set", to_json("value_to_set")) AS "fields"
$function$;

SQLFiddle

SQLFiddle

Edit:

编辑：

A version, which sets multiple keys & values:

一个版本，它设置多个键和值：

CREATE OR REPLACE FUNCTION "json_object_set_keys"(
  "json"          json,
  "keys_to_set"   TEXT[],
  "values_to_set" anyarray
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json
  FROM (SELECT *
          FROM json_each("json")
         WHERE "key" <> ALL ("keys_to_set")
         UNION ALL
        SELECT DISTINCT ON ("keys_to_set"["index"])
               "keys_to_set"["index"],
               CASE
                 WHEN "values_to_set"["index"] IS NULL THEN 'null'::json
                 ELSE to_json("values_to_set"["index"])
               END
          FROM generate_subscripts("keys_to_set", 1) AS "keys"("index")
          JOIN generate_subscripts("values_to_set", 1) AS "values"("index")
         USING ("index")) AS "fields"
$function$;

Edit 2: as @ErwinBrandstetter notedthese functions above works like a so-called UPSERT(updates a field if it exists, inserts if it does not exist). Here is a variant, which only UPDATE:

编辑 2：正如@ErwinBrandstetter 所指出的，上面的这些函数就像所谓的UPSERT（如果存在则更新字段，如果不存在则插入）。这是一个变体，它仅UPDATE：

CREATE OR REPLACE FUNCTION "json_object_update_key"(
  "json"          json,
  "key_to_set"    TEXT,
  "value_to_set"  anyelement
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE
  WHEN ("json" -> "key_to_set") IS NULL THEN "json"
  ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')
          FROM (SELECT *
                  FROM json_each("json")
                 WHERE "key" <> "key_to_set"
                 UNION ALL
                SELECT "key_to_set", to_json("value_to_set")) AS "fields")::json
END
$function$;

Edit 3: Here is recursive variant, which can set (UPSERT) a leaf value (and uses the first function from this answer), located at a key-path (where keys can only refer to inner objects, inner arrays not supported):

编辑 3：这是递归变体，它可以设置 ( UPSERT) 一个叶值（并使用此答案中的第一个函数），位于键路径（其中键只能引用内部对象，不支持内部数组）：

CREATE OR REPLACE FUNCTION "json_object_set_path"(
  "json"          json,
  "key_path"      TEXT[],
  "value_to_set"  anyelement
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE COALESCE(array_length("key_path", 1), 0)
         WHEN 0 THEN to_json("value_to_set")
         WHEN 1 THEN "json_object_set_key"("json", "key_path"[l], "value_to_set")
         ELSE "json_object_set_key"(
           "json",
           "key_path"[l],
           "json_object_set_path"(
             COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json,
             "key_path"[l+1:u],
             "value_to_set"
           )
         )
       END
  FROM array_lower("key_path", 1) l,
       array_upper("key_path", 1) u
$function$;

Update: functions are compacted now.

更新：功能现在被压缩。

回答by Teo Choong Ping

With 9.5 use jsonb_set-

在 9.5 中使用 jsonb_set-

UPDATE objects
SET body = jsonb_set(body, '{name}', '"Mary"', true)
WHERE id = 1; 

where body is a jsonb column type.

其中 body 是 jsonb 列类型。

回答by Fandi Susanto

With Postgresql 9.5 it can be done by following-

使用 Postgresql 9.5 可以通过以下方式完成 -

UPDATE test
SET data = data - 'a' || '{"a":5}'
WHERE data->>'b' = '2';

OR

或者

UPDATE test
SET data = jsonb_set(data, '{a}', '5'::jsonb);

---

---

Somebody asked how to update many fields in jsonb value at once. Suppose we create a table:

有人问如何一次更新 jsonb 值中的多个字段。假设我们创建一个表：

CREATE TABLE testjsonb ( id SERIAL PRIMARY KEY, object JSONB );

Then we INSERT a experimental row:

然后我们插入一个实验行：

INSERT INTO testjsonb
VALUES (DEFAULT, '{"a":"one", "b":"two", "c":{"c1":"see1","c2":"see2","c3":"see3"}}');

Then we UPDATE the row:

然后我们更新行：

UPDATE testjsonb SET object = object - 'b' || '{"a":1,"d":4}';

Which does the following:

执行以下操作：

1. Updates the a field
2. Removes the b field
3. Add the d field

1. 更新 a 字段
2. 删除 b 字段
3. 添加 d 字段

Selecting the data:

选择数据：

SELECT jsonb_pretty(object) FROM testjsonb;

Will result in:

会导致：

      jsonb_pretty
-------------------------
 {                      +
     "a": 1,            +
     "c": {             +
         "c1": "see1",  +
         "c2": "see2",  +
         "c3": "see3",  +
     },                 +
     "d": 4             +
 }
(1 row)

To update field inside, Dont use the concat operator ||. Use jsonb_set instead. Which is not simple:

要更新内部字段，请不要使用 concat 运算符||。使用 jsonb_set 代替。这并不简单：

UPDATE testjsonb SET object =
jsonb_set(jsonb_set(object, '{c,c1}','"seeme"'),'{c,c2}','"seehim"');

Using the concat operator for {c,c1} for example:

例如，使用 {c,c1} 的 concat 运算符：

UPDATE testjsonb SET object = object || '{"c":{"c1":"seedoctor"}}';

Will remove {c,c2} and {c,c3}.

将删除 {c,c2} 和 {c,c3}。

For more power, seek power at postgresql json functions documentation. One might be interested in the #-operator, jsonb_setfunction and also jsonb_insertfunction.

如需更多功能，请在postgresql json 函数文档中寻求功能。人们可能对#-运算符、jsonb_set函数和jsonb_insert函数感兴趣。

回答by sigod

UPDATE test
SET data = data::jsonb - 'a' || '{"a":5}'::jsonb
WHERE data->>'b' = '2'

This seems to be working on PostgreSQL 9.5

这似乎适用于 PostgreSQL 9.5

回答by shru

To build upon @pozs's answers, here are a couple more PostgreSQL functions which may be useful to some. (Requires PostgreSQL 9.3+)

为了建立在@pozs 的答案之上，这里有几个 PostgreSQL 函数可能对某些人有用。(需要 PostgreSQL 9.3+)

Delete By Key:Deletes a value from JSON structure by key.

Delete By Key：通过键从 JSON 结构中删除一个值。

CREATE OR REPLACE FUNCTION "json_object_del_key"(
  "json"          json,
  "key_to_del"    TEXT
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE
  WHEN ("json" -> "key_to_del") IS NULL THEN "json"
  ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')
          FROM (SELECT *
                  FROM json_each("json")
                 WHERE "key" <> "key_to_del"
               ) AS "fields")::json
END
$function$;

Recursive Delete By Key:Deletes a value from JSON structure by key-path. (requires @pozs's json_object_set_keyfunction)

Recursive Delete By Key：通过 key-path 从 JSON 结构中删除一个值。（需要@pozs 的json_object_set_key功能）

CREATE OR REPLACE FUNCTION "json_object_del_path"(
  "json"          json,
  "key_path"      TEXT[]
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE
  WHEN ("json" -> "key_path"[l] ) IS NULL THEN "json"
  ELSE
     CASE COALESCE(array_length("key_path", 1), 0)
         WHEN 0 THEN "json"
         WHEN 1 THEN "json_object_del_key"("json", "key_path"[l])
         ELSE "json_object_set_key"(
           "json",
           "key_path"[l],
           "json_object_del_path"(
             COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json,
             "key_path"[l+1:u]
           )
         )
       END
    END
  FROM array_lower("key_path", 1) l,
       array_upper("key_path", 1) u
$function$;

Usage examples:

用法示例：

s1=# SELECT json_object_del_key ('{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}',
                                 'foo'),
            json_object_del_path('{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}',
                                 '{"foo","moe"}');

 json_object_del_key |          json_object_del_path
---------------------+-----------------------------------------
 {"hello":[7,3,1]}   | {"hello":[7,3,1],"foo":{"mofu":"fuwa"}}

回答by Neethu

If your field type is of json the following will work for you.

如果您的字段类型是 json，则以下内容对您有用。

UPDATE 
table_name
SET field_name = field_name::jsonb - 'key' || '{"key":new_val}' 
WHERE field_name->>'key' = 'old_value'.

Operator '-' delete key/value pair or string element from left operand. Key/value pairs are matched based on their key value.

运算符“-”从左操作数中删除键/值对或字符串元素。键/值对根据其键值进行匹配。

Operator '||' concatenate two jsonb values into a new jsonb value.

运算符“||” 将两个 jsonb 值连接成一个新的 jsonb 值。

Since these are jsonb operators you just need to typecast to::jsonb

由于这些是 jsonb 运算符，因此您只需将类型转换为 ::jsonb

More info : JSON Functions and Operators

更多信息：JSON 函数和运算符

You can read my note here

你可以在这里阅读我的笔记

回答by Magnus

With PostgreSQL 9.4, we've implemented the following python function. It may also work with PostgreSQL 9.3.

在 PostgreSQL 9.4 中，我们实现了以下 python 函数。它也可以与 PostgreSQL 9.3 一起使用。

create language plpython2u;

create or replace function json_set(jdata jsonb, jpaths jsonb, jvalue jsonb) returns jsonb as $$
import json

a = json.loads(jdata)
b = json.loads(jpaths)

if a.__class__.__name__ != 'dict' and a.__class__.__name__ != 'list':
  raise plpy.Error("The json data must be an object or a string.")

if b.__class__.__name__ != 'list':
   raise plpy.Error("The json path must be an array of paths to traverse.")

c = a
for i in range(0, len(b)):
  p = b[i]
  plpy.notice('p == ' + str(p))

  if i == len(b) - 1:
    c[p] = json.loads(jvalue)

  else:
    if p.__class__.__name__ == 'unicode':
      plpy.notice("Traversing '" + p + "'")
      if c.__class__.__name__ != 'dict':
        raise plpy.Error("  The value here is not a dictionary.")
      else:
        c = c[p]

    if p.__class__.__name__ == 'int':
      plpy.notice("Traversing " + str(p))
      if c.__class__.__name__ != 'list':
        raise plpy.Error("  The value here is not a list.")
      else:
        c = c[p]

    if c is None:
      break    

return json.dumps(a)
$$ language plpython2u ;

Example usage:

用法示例：

create table jsonb_table (jsonb_column jsonb);
insert into jsonb_table values
('{"cars":["Jaguar", {"type":"Unknown","partsList":[12, 34, 56]}, "Atom"]}');

select jsonb_column->'cars'->1->'partsList'->2, jsonb_column from jsonb_table;

update jsonb_table
set jsonb_column = json_set(jsonb_column, '["cars",1,"partsList",2]', '99');

select jsonb_column->'cars'->1->'partsList'->2, jsonb_column from jsonb_table;

Note that for a previous employer, I have written a set of C functions for manipulating JSON data as text (not as a jsonor jsonbtype) for PostgreSQL 7, 8 and 9. For example, extracting data with json_path('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']'), setting data with json_path_set('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']', '99.87')and so on. It took about 3 days work, so if you need it to run on legacy systems and have the time to spare, it may be worth the effort. I imagine the C version is much faster than the python version.

请注意，对于以前的雇主，我已经为 PostgreSQL 7、8 和 9编写了一组 C 函数，用于将 JSON 数据处理为文本（而不是 ajson或jsonb类型）。例如，使用 提取数据json_path('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']')，设置数据json_path_set('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']', '99.87')等等。大约需要 3 天的时间，所以如果您需要它在遗留系统上运行并且有空闲时间，那么这可能是值得的。我想 C 版本比 python 版本快得多。

回答by J. Raczkiewicz

I wrote small function for myself that works recursively in Postgres 9.4. Here is the function (I hope it works well for you):

我为自己编写了在 Postgres 9.4 中递归运行的小函数。这是功能（我希望它对您有用）：

CREATE OR REPLACE FUNCTION jsonb_update(val1 JSONB,val2 JSONB)
RETURNS JSONB AS $$
DECLARE
    result JSONB;
    v RECORD;
BEGIN
    IF jsonb_typeof(val2) = 'null'
    THEN 
        RETURN val1;
    END IF;

    result = val1;

    FOR v IN SELECT key, value FROM jsonb_each(val2) LOOP

        IF jsonb_typeof(val2->v.key) = 'object'
            THEN
                result = result || jsonb_build_object(v.key, jsonb_update(val1->v.key, val2->v.key));
            ELSE
                result = result || jsonb_build_object(v.key, v.value);
        END IF;
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

Here is sample use:

这是示例使用：

select jsonb_update('{"a":{"b":{"c":{"d":5,"dd":6},"cc":1}},"aaa":5}'::jsonb, '{"a":{"b":{"c":{"d":15}}},"aa":9}'::jsonb);
                            jsonb_update                             
---------------------------------------------------------------------
 {"a": {"b": {"c": {"d": 15, "dd": 6}, "cc": 1}}, "aa": 9, "aaa": 5}
(1 row)

As you can see it analyze deep down and update/add values where needed.

如您所见，它会深入分析并在需要时更新/添加值。

回答by Ziggy Crueltyfree Zeitgeister

Even though the following will not satisfy this request (the function json_object_agg is not available in PostgreSQL 9.3), the following can be useful for anyone looking for a || operator for PostgreSQL 9.4, as implemented in the upcoming PostgreSQL 9.5:

即使以下内容不能满足此请求（PostgreSQL 9.3 中不提供 json_object_agg 函数），但以下内容对任何寻找 || 的人都非常有用 PostgreSQL 9.4 的运算符，在即将推出的 PostgreSQL 9.5 中实现：

CREATE OR REPLACE FUNCTION jsonb_merge(left JSONB, right JSONB)
RETURNS JSONB
AS $$
SELECT
  CASE WHEN jsonb_typeof() = 'object' AND jsonb_typeof() = 'object' THEN
       (SELECT json_object_agg(COALESCE(o.key, n.key), CASE WHEN n.key IS NOT NULL THEN n.value ELSE o.value END)::jsonb
        FROM jsonb_each() o
        FULL JOIN jsonb_each() n ON (n.key = o.key))
   ELSE 
     (CASE WHEN jsonb_typeof() = 'array' THEN LEFT(::text, -1) ELSE '['||::text END ||', '||
      CASE WHEN jsonb_typeof() = 'array' THEN RIGHT(::text, -1) ELSE ::text||']' END)::jsonb
   END     
$$ LANGUAGE sql IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION jsonb_merge(jsonb, jsonb) TO public;
CREATE OPERATOR || ( LEFTARG = jsonb, RIGHTARG = jsonb, PROCEDURE = jsonb_merge );

回答by Antonio

This worked for me, when trying to update a string type field.

在尝试更新字符串类型字段时，这对我有用。

UPDATE table_name 
SET body = jsonb_set(body, '{some_key}', to_json('value'::TEXT)::jsonb);

Hope it helps someone else out!

希望它可以帮助别人！

Assuming the table table_name has a jsonb column named body and you want to change body.some_key = 'value'

假设表 table_name 有一个名为 body 的 jsonb 列，并且您想更改 body.some_key = 'value'