Last active
May 18, 2020 10:58
-
-
Save pozs/a632be48346ca2990a0e to your computer and use it in GitHub Desktop.
Multiple functions to modify json objects in PostgreSQL
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
-- Aggregate function to aggregate key-value pairs to json object (opposite of json_each()) | |
-- requires PostgreSQL 9.3+ (but < 9.4!) | |
-- requires function "json_object_set_key" | |
DROP AGGREGATE IF EXISTS "json_object_agg" (TEXT, anyelement); | |
CREATE AGGREGATE "json_object_agg" (TEXT, anyelement) | |
( | |
STYPE = json, | |
SFUNC = "json_object_set_key", | |
INITCOND = '{}' | |
); |
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
-- SQL function to delete key(s) from a json object | |
-- requires PostgreSQL 9.3+ | |
CREATE OR REPLACE FUNCTION "json_object_delete_keys"( | |
"json" json, | |
VARIADIC "keys_to_delete" TEXT[] | |
) | |
RETURNS json | |
LANGUAGE sql | |
IMMUTABLE | |
STRICT | |
AS $function$ | |
SELECT COALESCE( | |
(SELECT ('{' || string_agg(to_json("key") || ':' || "value", ',') || '}') | |
FROM json_each("json") | |
WHERE "key" <> ALL ("keys_to_delete")), | |
'{}' | |
)::json | |
$function$; |
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
-- SQL function to set a key to a specific value within a json object | |
-- requires PostgreSQL 9.3+ | |
CREATE OR REPLACE FUNCTION "json_object_set_key"( | |
"json" json, | |
"key_to_set" TEXT, | |
"value_to_set" anyelement | |
) | |
RETURNS json | |
LANGUAGE sql | |
IMMUTABLE | |
STRICT | |
AS $function$ | |
SELECT COALESCE( | |
(SELECT ('{' || string_agg(to_json("key") || ':' || "value", ',') || '}') | |
FROM (SELECT * | |
FROM json_each("json") | |
WHERE "key" <> "key_to_set" | |
UNION ALL | |
SELECT "key_to_set", to_json("value_to_set")) AS "fields"), | |
'{}' | |
)::json | |
$function$; |
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
-- SQL function to set keys to specific values within a json object | |
-- requires PostgreSQL 9.3+ | |
CREATE OR REPLACE FUNCTION "json_object_set_keys"( | |
"json" json, | |
"keys_to_set" TEXT[], | |
"values_to_set" anyarray | |
) | |
RETURNS json | |
LANGUAGE sql | |
IMMUTABLE | |
STRICT | |
AS $function$ | |
SELECT COALESCE( | |
(SELECT ('{' || string_agg(to_json("key") || ':' || "value", ',') || '}') | |
FROM (SELECT * | |
FROM json_each("json") | |
WHERE "key" <> ALL ("keys_to_set") | |
UNION ALL | |
SELECT DISTINCT ON ("keys_to_set"["index"]) | |
"keys_to_set"["index"], | |
CASE | |
WHEN "values_to_set"["index"] IS NULL THEN 'null' | |
ELSE to_json("values_to_set"["index"]) | |
END | |
FROM generate_subscripts("keys_to_set", 1) AS "keys"("index") | |
JOIN generate_subscripts("values_to_set", 1) AS "values"("index") | |
USING ("index")) AS "fields"), | |
'{}' | |
)::json | |
$function$; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
how to pass array of keys to json_object_delete_keys