PostgresSQL JSON Add Delete Functions

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

Advertisements

One thought on “PostgresSQL JSON Add Delete Functions

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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s