Skip to content

Instantly share code, notes, and snippets.

@inindev
Forked from matheusoliveira/json_manipulator.sql
Last active February 13, 2024 03:18
Show Gist options
  • Star 36 You must be signed in to star a gist
  • Fork 8 You must be signed in to fork a gist
  • Save inindev/2219dff96851928c2282 to your computer and use it in GitHub Desktop.
Save inindev/2219dff96851928c2282 to your computer and use it in GitHub Desktop.
Simple PostgreSQL 9.4 functions to manipulate jsonb objects adapted from Matheus de Oliveira's json_manipulator.sql. https://gist.github.com/matheusoliveira/9488951 (Note: performance is not a concern for those functions)
/*
* derivative work of Matheus de Oliveira's json_manipulator.sql
* https://gist.github.com/matheusoliveira/9488951
*
* adapted to support postgresql 9.4 jsonb type
* no warranties or guarantees of any kind are implied or offered
*
* license is as Matheus conferred it on 4/9/2015:
* matheusoliveira commented on Apr 9
* @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.
* https://gist.github.com/matheusoliveira/9488951
*/
CREATE OR REPLACE FUNCTION public.jsonb_append(data jsonb, insert_data jsonb)
RETURNS jsonb
IMMUTABLE
LANGUAGE sql
AS $$
SELECT json_object_agg(key, value)::jsonb
FROM (
SELECT * FROM jsonb_each(data)
UNION ALL
SELECT * FROM jsonb_each(insert_data)
) t;
$$;
CREATE OR REPLACE FUNCTION public.jsonb_delete(data jsonb, keys text[])
RETURNS jsonb
IMMUTABLE
LANGUAGE sql
AS $$
SELECT json_object_agg(key, value)::jsonb
FROM (
SELECT * FROM jsonb_each(data)
WHERE key <> ALL(keys)
) t;
$$;
CREATE OR REPLACE FUNCTION public.jsonb_merge(data jsonb, merge_data jsonb)
RETURNS jsonb
IMMUTABLE
LANGUAGE sql
AS $$
SELECT json_object_agg(key, value)::jsonb
FROM (
WITH to_merge AS (
SELECT * FROM jsonb_each(merge_data)
)
SELECT *
FROM jsonb_each(data)
WHERE key NOT IN (SELECT key FROM to_merge)
UNION ALL
SELECT * FROM to_merge
) t;
$$;
CREATE OR REPLACE FUNCTION public.jsonb_update(data jsonb, update_data jsonb)
RETURNS jsonb
IMMUTABLE
LANGUAGE sql
AS $$
SELECT json_object_agg(key, value)::jsonb
FROM (
WITH old_data AS (
SELECT * FROM jsonb_each(data)
), to_update AS (
SELECT * FROM jsonb_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.jsonb_lint(from_json jsonb, ntab integer DEFAULT 0)
RETURNS jsonb
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) || jsonb_lint(value, ntab + 1)::text, E',\n') FROM jsonb_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 || ': ' || jsonb_lint(value, ntab + 1)::text, E',\n') FROM jsonb_each(from_json)) ||
E'\n' || repeat(E'\t', ntab) || '}')
ELSE
from_json::text
END)::jsonb
$$;
CREATE OR REPLACE FUNCTION public.jsonb_unlint(from_json jsonb)
RETURNS jsonb
LANGUAGE sql
IMMUTABLE STRICT
AS $$
SELECT (CASE substring(from_json::text FROM '(?m)^[\s]*(.)') /* Get first non-whitespace */
WHEN '[' THEN
('['
|| (SELECT string_agg(jsonb_unlint(value)::text, ',') FROM jsonb_array_elements(from_json)) ||
']')
WHEN '{' THEN
('{'
|| (SELECT string_agg(to_json(key)::text || ':' || jsonb_unlint(value)::text, ',') FROM jsonb_each(from_json)) ||
'}')
ELSE
from_json::text
END)::jsonb
$$;
postgres=# SELECT jsonb_append('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
jsonb_append
--------------------------
{"a": 4, "b": 2, "c": 3}
(1 row)
postgres=# SELECT jsonb_delete('{"b": 2, "c": 3, "a": 4}', '{b, c}');
jsonb_delete
--------------
{"a": 4}
(1 row)
postgres=# SELECT jsonb_update('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
jsonb_update
--------------
{"a": 4}
(1 row)
postgres=# SELECT jsonb_merge('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
jsonb_merge
--------------------------
{"a": 4, "b": 2, "c": 3}
(1 row)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment