Pgsql数据库jsonb操作函数集合
CREATE OR REPLACE FUNCTION "json_object_del_path"( "json" json, "key_path" TEXT[] ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT CASE WHEN ("json" -> "key_path"[l] ) IS NULL THEN "json" ELSE CASE COALESCE(array_length("key_path", 1), 0) WHEN 0 THEN "json" WHEN 1 THEN "json_object_del_key"("json", "key_path"[l]) ELSE "json_object_set_key"( "json", "key_path"[l], "json_object_del_path"( COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json, "key_path"[l+1:u] ) ) END END FROM array_lower("key_path", 1) l, array_upper("key_path", 1) u $function$;
CREATE OR REPLACE FUNCTION "json_object_del_key"( "json" json, "key_to_del" TEXT ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT CASE WHEN ("json" -> "key_to_del") IS NULL THEN "json" ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}') FROM (SELECT * FROM json_each("json") WHERE "key" <> "key_to_del" ) AS "fields")::json END $function$;
CREATE OR REPLACE FUNCTION "json_object_update_key"( "json" json, "key_to_set" TEXT, "value_to_set" anyelement ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT CASE WHEN ("json" -> "key_to_set") IS NULL THEN "json" ELSE (SELECT concat('{', 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 END $function$;
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 concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json 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'::json 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" $function$;
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 concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json FROM (SELECT * FROM json_each("json") WHERE "key" <> "key_to_set" UNION ALL SELECT "key_to_set", to_json("value_to_set")) AS "fields" $function$;
CREATE OR REPLACE FUNCTION "json_object_set_path"( "json" json, "key_path" TEXT[], "value_to_set" anyelement ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT CASE COALESCE(array_length("key_path", 1), 0) WHEN 0 THEN to_json("value_to_set") WHEN 1 THEN "json_object_set_key"("json", "key_path"[l], "value_to_set") ELSE "json_object_set_key"( "json", "key_path"[l], "json_object_set_path"( COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json, "key_path"[l+1:u], "value_to_set" ) ) END FROM array_lower("key_path", 1) l, array_upper("key_path", 1) u $function$;
usage:
s1=# SELECT json_object_del_key ('{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}', 'foo'), json_object_del_path('{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}', '{"foo","moe"}'); json_object_del_key | json_object_del_path ---------------------+----------------------------------------- {"hello":[7,3,1]} | {"hello":[7,3,1],"foo":{"mofu":"fuwa"}}