How do I modify fields inside the new PostgreSQL JSON datatype?
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
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:
- Updates the a field
- Removes the b field
- 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.
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.
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$;
Updated: Added function for replacing an existing json field's key by another given key. Can be in handy for updating data types in migrations or other scenarios like data structure amending.
CREATE OR REPLACE FUNCTION json_object_replace_key(
json_value json,
existing_key text,
desired_key text)
RETURNS json AS
$BODY$
SELECT COALESCE(
(
SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
FROM (
SELECT *
FROM json_each(json_value)
WHERE key <> existing_key
UNION ALL
SELECT desired_key, json_value -> existing_key
) AS "fields"
-- WHERE value IS NOT NULL (Actually not required as the string_agg with value's being null will "discard" that entry)
),
'{}'
)::json
$BODY$
LANGUAGE sql IMMUTABLE STRICT
COST 100;
Update: functions are compacted now.