Forked from matheusoliveira/json_manipulator.sql
Last active
February 13, 2024 03:18
-
-
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)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
* 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 | |
$$; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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