Postgres 9.3 has good support for JSON types. There are lot of functions and operators available for parsing JSON. However it does not provide any functions for performing modifications to the JSON. You cannot even perform simple key add or delete operations on the JSON. So you are pretty much stuck with a readonly only copy of JSON always. Well there is no direct way of doing these operations , but you can use the HSTORE extension to overcome this shortcoming. Postgres ships this module by default, but you need to make sure that its available as extension in your schema. If not you can install the extension using following command.
CREATE EXTENSION hstore;
Function to Set a JSON Key Value
You can create following function to set a key into a JSON object
CREATE OR REPLACE FUNCTION set_key(json_in json, key_name text, key_value text) RETURNS json AS $$ DECLARE item json; DECLARE fields hstore; BEGIN -- Initialize the hstore with desired key value fields := hstore(key_name,key_value); -- Parse through Input Json and push each key into hstore FOR item IN SELECT row_to_json(r.*) FROM json_each_text(json_in) AS r LOOP --RAISE NOTICE 'Parsing Item % %', item->>'key', item->>'value'; fields := (fields::hstore || hstore(item->>'key', item->>'value')); END LOOP; --RAISE NOTICE 'Result %', hstore_to_json(fields); RETURN hstore_to_json(fields); END; $$ LANGUAGE plpgsql SECURITY DEFINER STRICT;
Example
SELECT set_key(('{"Name":"My Name", "Items" :[{ "Id" : 1, "Name" : "Name 1"}, { "Id" : 2, "Name 2" : "Item2 Name"}]}')::json, 'Id', '2'); -- Result "{"Id": "2", "Name": "My Name", "Items": "[{ \"Id\" : 1, \"Name\" : \"Name 1\"}, { \"Id\" : 2, \"Name 2\" : \"Item2 Name\"}]"}"
Function to Delete a JSON Key Value
You can create following function to delete a key from a JSON object
CREATE OR REPLACE FUNCTION remove_key(json_in json, key_name text) RETURNS json AS $$ DECLARE item json; DECLARE fields hstore; BEGIN -- Initialize the hstore with desired key being set to NULL fields := hstore(key_name,NULL); -- Parse through Input Json and push each key into hstore FOR item IN SELECT row_to_json(r.*) FROM json_each_text(json_in) AS r LOOP --RAISE NOTICE 'Parsing Item % %', item->>'key', item->>'value'; fields := (fields::hstore || hstore(item->>'key', item->>'value')); END LOOP; --RAISE NOTICE 'Result %', hstore_to_json(fields); -- Remove the desired key from store fields := fields-key_name; RETURN hstore_to_json(fields); END; $$ LANGUAGE plpgsql SECURITY DEFINER STRICT;
Example
SELECT remove_key(('{ "Id" : "2" , "Name":"My Name", "Items" :[{ "Id" : 1, "Name" : "Name 1"}, { "Id" : 2, "Name 2" : "Item2 Name"}]}')::json, 'Id'); -- Result "{"Name": "My Name", "Items": "[{ \"Id\" : 1, \"Name\" : \"Name 1\"}, { \"Id\" : 2, \"Name 2\" : \"Item2 Name\"}]"}"
Postgres Documentation for Handling JSON types:
http://www.postgresql.org/docs/9.3/static/functions-json.html
JSON -> Hstore -> JSON is not really a round trip, as Hstore only allows text values, as well as text keys.
This has implications for your functions:
=# SELECT remove_key(‘{“a”:1, “b”:2}’::json, ‘a’);
remove_key
————
{“b”: “2”}
(1 row)
Indeed, your example shows how badly this behaves: your nested object is turned into a text value!
I have a function that behaves as expected (and can be used as an operator, giving you the same syntax as for Hstore). It’s a fair bit slower than just using hstore though: http://schinckel.net/2014/06/01/adding-json-operators-to-postgresql/
LikeLike