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

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

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

Answer by Roman Pekar for How do I modify fields inside the new PostgreSQL JSON datatype?

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

Answer by pozs for How do I modify fields inside the new PostgreSQL JSON datatype?

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('', '')  

Where should be string, and can be whatever type to_jsonb() accepts.

For setting a value deep in a JSON hierarchy, the
jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean default true) 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}]}]}'  

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":[]}]}'  

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.

Answer by Magnus for How do I modify fields inside the new PostgreSQL JSON datatype?

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.

Answer by shru for How do I modify fields inside the new PostgreSQL JSON datatype?

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"}}  

Answer by Sandeep for How do I modify fields inside the new PostgreSQL JSON datatype?

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

Answer by Ziggy Crueltyfree Zeitgeister for How do I modify fields inside the new PostgreSQL JSON datatype?

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

Answer by Seymour Cakes for How do I modify fields inside the new PostgreSQL JSON datatype?

With 9.5 jsonb_set;

update objects set body=jsonb_set(body, '{name}', '"Mary"', true) where id=1;   

where body is a jsonb column type.

Answer by Fandi Susanto for How do I modify fields inside the new PostgreSQL JSON datatype?

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

Answer by sigod for How do I modify fields inside the new PostgreSQL JSON datatype?

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

This seems to be working on PostgreSQL 9.5

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72