How do I modify fields inside the new PostgreSQL J

问题:

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:

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

回答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).

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

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.

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

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(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:

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

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

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

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:

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:

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:

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

Original answer: It is possible (without plpython or plv8) in pure SQL too (but needs 9.3+, will not work with 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

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:

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

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-

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

where body is a jsonb column type.

回答3:

With Postgresql 9.5 it can be done by following-

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:

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

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:

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

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

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

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

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

回答4:

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

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

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)

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

回答6:

With PostgreSQL 9.4, we\'ve implemented the following python function. It may also work with 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.

回答7:

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:

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

回答8:

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

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.

回答9:

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:

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\';

回答10:

The following plpython snippet might come in handy.

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\';

回答11:

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

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.

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

More info : JSON Functions and Operators

You can read my note here

回答12:

You can also increment keys atomically within jsonb like this:

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.

For more detailed explanation, see my answer here: https://stackoverflow.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!

回答14:

If you are making this query with a programming language client, eg from python pycopg2, or Node Postgres, Make sure you parse the new data to JSON first.

It might easily look like a python dictionary is the Same as a JSON object but it does not first do json.dumps on the dictionary.

A simple python snippet:

def change_destination(self,parcel_id,destlatlng): query=\"UPDATE parcels SET destlatlng = \'{}\' WHERE parcel_id ={};\".format(json.dumps(destlatlng), parcel_id) self.cursor.execute(query2) self.connection.commit()