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

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...

13 个解决方案

#1

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

更新:使用PostgreSQL 9.5，在PostgreSQL本身中有一些jsonb操作功能(但json没有;操作json值需要强制类型转换)。

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

合并两个(或多个)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 key can 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.

其中 应该是string，而 可以是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)

path can 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:

路径也可以包含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 path count from the end of JSON arrays.

同样，从JSON数组末尾出现在路径计数中的负整数。

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

所以,f.ex。在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 path in target is 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:

但是，当目标中的路径是JSON对象的键时，该函数的工作方式略有不同(与jsonb_set())。在这种情况下，它只会在不使用键时为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 hierarchy can 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_object_agg()将JSON字符串聚合为JSON对象。

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 noted these 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(如果字段存在，就更新它，如果字段不存在，就插入)。这里有一个变体，它只更新:

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.

更新:函数现在已被压缩。

#2

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列类型。

#3

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);

#4

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.

按键删除:按键从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_key function)

按键递归删除:按键路径从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"}}

#5

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

#6

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 json or jsonb type) 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数据作为文本(而不是JSON或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版本快得多。

#7

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.

正如您所看到的，它深入分析并在需要时更新/添加值。

#8

Sadly, I've not found anything in the documentation, but you can use some workaround, for example you could write some extended function.

遗憾的是，我在文档中没有找到任何内容，但是您可以使用一些变通方法，例如，您可以编写一些扩展函数。

For example, in Python:

例如,在Python中:

CREATE or REPLACE FUNCTION json_update(data json, key text, value json)
returns json
as $$
from json import loads, dumps
if key is None: return data
js = loads(data)
js[key] = value
return dumps(js)
$$ language plpython3u

and then

然后

update test set data=json_update(data, 'a', to_json(5)) where data->>'b' = '2';

#9

The following plpython snippet might come in handy.

下面的plpython代码片段可能会派上用场。

CREATE EXTENSION IF NOT EXISTS plpythonu;
CREATE LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION json_update(data json, key text, value text)
 RETURNS json
 AS $$
    import json
    json_data = json.loads(data)
    json_data[key] = value
    return json.dumps(json_data, indent=4)
 $$ LANGUAGE plpythonu;

-- Check how JSON looks before updating

SELECT json_update(content::json, 'CFRDiagnosis.mod_nbs', '1')
FROM sc_server_centre_document WHERE record_id = 35 AND template = 'CFRDiagnosis';

-- Once satisfied update JSON inplace

UPDATE sc_server_centre_document SET content = json_update(content::json, 'CFRDiagnosis.mod_nbs', '1')
WHERE record_id = 35 AND template = 'CFRDiagnosis';

#10

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($1) = 'object' AND jsonb_typeof($2) = '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($1) o
        FULL JOIN jsonb_each($2) n ON (n.key = o.key))
   ELSE 
     (CASE WHEN jsonb_typeof($1) = 'array' THEN LEFT($1::text, -1) ELSE '['||$1::text END ||', '||
      CASE WHEN jsonb_typeof($2) = 'array' THEN RIGHT($2::text, -1) ELSE $2::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 );

#11

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

你可以在这里看我的笔记。

#12

You can also increment keys atomically within jsonb like this:

您还可以在jsonb中自动增加键值，如下所示:

UPDATE users SET counters = counters || CONCAT('{"bar":', COALESCE(counters->>'bar','0')::int + 1, '}')::jsonb WHERE id = 1;

SELECT * FROM users;

 id |    counters
----+------------
  1 | {"bar": 1}

Undefined key -> assumes starting value of 0.

未定义键->的起始值为0。

For more detailed explanation, see my answer here: https://*.com/a/39076637

有关更详细的解释，请参见我的答案:https://*.com/a/39076637

#13

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!

希望它能帮助别人走出困境!