Skip to content

Instantly share code, notes, and snippets.

@yyscamper
Created February 26, 2018 09:07
  • Star 2 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save yyscamper/a8b0a87e104ec04cc856e986198dd07b to your computer and use it in GitHub Desktop.
PostgreSQL: Rename JSONB Keys (Batch Mode)
CREATE OR REPLACE FUNCTION jsonb_rename_keys(
jdata JSONB,
keys TEXT[]
)
RETURNS JSONB AS $$
DECLARE
result JSONB;
len INT;
newkey TEXT;
oldkey TEXT;
BEGIN
len = array_length(keys, 1);
IF len < 1 OR (len % 2) != 0 THEN
RAISE EXCEPTION 'The length of keys must be even, such as {old1,new1,old2,new2,...}';
END IF;
result = jdata;
FOR i IN 1..len BY 2 LOOP
oldkey = keys[i];
IF (jdata ? oldkey) THEN
newkey = keys[i+1];
result = (result - oldkey) || jsonb_build_object(newkey, result->oldkey);
END IF;
END LOOP;
RETURN result;
END;
$$ LANGUAGE plpgsql;
@yyscamper
Copy link
Author

yyscamper commented Feb 26, 2018

Usage: jsonb_rename_keys(jdata, ARRAY['oldkey1', 'newkey1', 'oldkey2', 'newkey2', ....])
If old key doesn't exist, then simply ignore it.

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