Skip to content

Instantly share code, notes, and snippets.

@pozs
Last active May 18, 2020 10:58
Show Gist options
  • Save pozs/a632be48346ca2990a0e to your computer and use it in GitHub Desktop.
Save pozs/a632be48346ca2990a0e to your computer and use it in GitHub Desktop.
Multiple functions to modify json objects in PostgreSQL
-- 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 = '{}'
);
-- 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$;
-- 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$;
-- 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$;
@VeenaHosur
Copy link

how to pass array of keys to json_object_delete_keys

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