Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Simple PostgreSQL functions to manipulate json objects. (Note: performance is not a concern for those functions)
CREATE OR REPLACE FUNCTION public.json_append(data json, insert_data json)
RETURNS json
IMMUTABLE
LANGUAGE sql
AS $$
SELECT ('{'||string_agg(to_json(key)||':'||value, ',')||'}')::json
FROM (
SELECT * FROM json_each(data)
UNION ALL
SELECT * FROM json_each(insert_data)
) t;
$$;
CREATE OR REPLACE FUNCTION public.json_delete(data json, keys text[])
RETURNS json
IMMUTABLE
LANGUAGE sql
AS $$
SELECT ('{'||string_agg(to_json(key)||':'||value, ',')||'}')::json
FROM (
SELECT * FROM json_each(data)
WHERE key <>ALL(keys)
) t;
$$;
CREATE OR REPLACE FUNCTION public.json_merge(data json, merge_data json)
RETURNS json
IMMUTABLE
LANGUAGE sql
AS $$
SELECT ('{'||string_agg(to_json(key)||':'||value, ',')||'}')::json
FROM (
WITH to_merge AS (
SELECT * FROM json_each(merge_data)
)
SELECT *
FROM json_each(data)
WHERE key NOT IN (SELECT key FROM to_merge)
UNION ALL
SELECT * FROM to_merge
) t;
$$;
CREATE OR REPLACE FUNCTION public.json_update(data json, update_data json)
RETURNS json
IMMUTABLE
LANGUAGE sql
AS $$
SELECT ('{'||string_agg(to_json(key)||':'||value, ',')||'}')::json
FROM (
WITH old_data AS (
SELECT * FROM json_each(data)
), to_update AS (
SELECT * FROM json_each(update_data)
WHERE key IN (SELECT key FROM old_data)
)
SELECT * FROM old_data
WHERE key NOT IN (SELECT key FROM to_update)
UNION ALL
SELECT * FROM to_update
) t;
$$;
CREATE OR REPLACE FUNCTION public.json_lint(from_json json, ntab integer DEFAULT 0)
RETURNS json
LANGUAGE sql
IMMUTABLE STRICT
AS $$
SELECT (CASE substring(from_json::text FROM '(?m)^[\s]*(.)') /* Get first non-whitespace */
WHEN '[' THEN
(E'[\n'
|| (SELECT string_agg(repeat(E'\t', ntab + 1) || json_lint(value, ntab + 1)::text, E',\n') FROM json_array_elements(from_json)) ||
E'\n' || repeat(E'\t', ntab) || ']')
WHEN '{' THEN
(E'{\n'
|| (SELECT string_agg(repeat(E'\t', ntab + 1) || to_json(key)::text || ': ' || json_lint(value, ntab + 1)::text, E',\n') FROM json_each(from_json)) ||
E'\n' || repeat(E'\t', ntab) || '}')
ELSE
from_json::text
END)::json
$$;
CREATE OR REPLACE FUNCTION public.json_unlint(from_json json)
RETURNS json
LANGUAGE sql
IMMUTABLE STRICT
AS $$
SELECT (CASE substring(from_json::text FROM '(?m)^[\s]*(.)') /* Get first non-whitespace */
WHEN '[' THEN
('['
|| (SELECT string_agg(json_unlint(value)::text, ',') FROM json_array_elements(from_json)) ||
']')
WHEN '{' THEN
('{'
|| (SELECT string_agg(to_json(key)::text || ':' || json_unlint(value)::text, ',') FROM json_each(from_json)) ||
'}')
ELSE
from_json::text
END)::json
$$;
postgres=# SELECT json_append('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
json_append
---------------------------
{"a":1,"b":2,"c":3,"a":4}
(1 row)
postgres=# SELECT json_delete('{"b": 2, "c": 3, "a": 4}', '{b, c}');
json_delete
-------------
{"a":4}
(1 row)
postgres=# SELECT json_update('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
json_update
-------------
{"a":4}
(1 row)
postgres=# SELECT json_merge('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
json_merge
---------------------
{"b":2,"c":3,"a":4}
(1 row)

that's a nice collection! just a note - these functions could be declared as immutable and strict too.

Also, instead of these lines:

SELECT ('{'||string_agg(to_json(key)||':'||value, ',')||'}')::json

you can use

select json_object_agg(key, value)
igorya commented Feb 20, 2015

Сurrently use string_agg() function is the right decision, imho. Because json_object_agg() is not available in v.9.3

Owner

Correct @igorya. Those functions have been built for 9.3, although, of course, they work on 9.4.

@chochkov, I'll mark then as IMMUTABLE and STRICT, thanks for remembering.

hnsl commented Mar 19, 2015

What licence does this have? Public domain?

Owner

@hannes-landeholm, I'd like to take credit if you share them (a link to this gist is more than enough), but I don't care much about licensing, consider it public domain, modify and use as you will.

jabclab commented Jun 9, 2015

These are great, thanks. I may have spotted an issue with json_merge, e.g.

postgres=# select * from json_merge('{"a":1,"b":2,"d":{"e":3,"g":4}}','{"b":5,"c":7,"d":{"e":8,"f":9}}');
                     json_merge
----------------------------------------------------
 { "a" : 1, "b" : 5, "c" : 7, "d" : {"e":8,"f":9} }

Here I would expect { "a" : 1, "b" : 5, "c" : 7, "d" : {"e":8,"f":9,"g":4} } (note the "g":4) to be the outcome, i.e. it doesn't appear to support nested object properties.

demisx commented Jan 8, 2016

👍 for nested merge support

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment