Last active
July 2, 2024 21:52
-
-
Save InfoSec812/b830a9db4c9048552f8c51d7987cc4d0 to your computer and use it in GitHub Desktop.
Pure PostgreSQL implementation of JSONPatch
This file contains hidden or 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
!! | |
!! Implementation of JSONPatch (http://jsonpatch.com/) using PostgreSQL >= 9.5 | |
!! | |
CREATE OR REPLACE FUNCTION jsonb_copy(JSONB, TEXT[], TEXT[]) RETURNS JSONB AS $$ | |
DECLARE | |
retval ALIAS FOR $1; | |
src_path ALIAS FOR $2; | |
dst_path ALIAS FOR $3; | |
tmp_value JSONB; | |
BEGIN | |
tmp_value = retval#>src_path; | |
RETURN jsonb_set(retval, dst_path, tmp_value::JSONB, true); | |
END; | |
$$ LANGUAGE PLPGSQL; | |
CREATE OR REPLACE FUNCTION jsonb_move(JSONB, TEXT[], TEXT[]) RETURNS JSONB AS $$ | |
DECLARE | |
retval ALIAS FOR $1; | |
src_path ALIAS FOR $2; | |
dst_path ALIAS FOR $3; | |
tmp_value JSONB; | |
BEGIN | |
tmp_value = retval#>src_path; | |
retval = retval #- src_path; | |
RETURN jsonb_set(retval, dst_path, tmp_value::JSONB, true); | |
END; | |
$$ LANGUAGE PLPGSQL; | |
CREATE OR REPLACE FUNCTION jsonb_test(JSONB, TEXT[], JSONB) RETURNS VOID AS $$ | |
DECLARE | |
doc ALIAS FOR $1; | |
test_path ALIAS FOR $2; | |
test_val ALIAS FOR $3; | |
BEGIN | |
IF (doc#>test_path)::JSONB != test_val::JSONB THEN | |
RAISE 'Testing % for value % failed', test_path, test_val; | |
END IF; | |
RETURN; | |
END; | |
$$ LANGUAGE PLPGSQL; | |
CREATE OR REPLACE FUNCTION jsonb_patch(JSONB, JSONB) RETURNS JSONB AS $$ | |
DECLARE | |
retval ALIAS FOR $1; | |
patchset ALIAS FOR $2; | |
patch_path TEXT[]; | |
value JSONB; | |
chg RECORD; | |
BEGIN | |
FOR chg IN SELECT * FROM jsonb_array_elements(patchset) | |
LOOP | |
patch_path = regexp_split_to_array(substr(chg.value->>'path', 2), E'/')::TEXT[]; | |
CASE chg.value->>'op' | |
WHEN 'add' THEN retval = jsonb_set(retval, patch_path, (chg.value->'value')::JSONB, true); | |
WHEN 'replace' THEN retval = jsonb_set(retval, patch_path, (chg.value->'value')::JSONB, false); | |
WHEN 'remove' THEN retval = retval #- patch_path; | |
WHEN 'copy' THEN retval = jsonb_copy(retval, regexp_split_to_array(substr(chg.value->>'from', 2), E'/')::TEXT[], patch_path); | |
WHEN 'move' THEN retval = jsonb_move(retval, regexp_split_to_array(substr(chg.value->>'from', 2), E'/')::TEXT[], patch_path); | |
WHEN 'test' THEN PERFORM jsonb_test(retval, patch_path, (chg.value->'value')::JSONB); | |
END CASE; | |
END LOOP; | |
RETURN retval; | |
END; | |
$$ LANGUAGE PLPGSQL; | |
SELECT 'Test all operations apply successfully'; | |
SELECT jsonb_patch('{"a": 1}', $$[ | |
{"op": "add", "path": "/c", "value": {"d": {"e": "f"}}}, | |
{"op": "copy", "from": "/c", "path": "/e"}, | |
{"op": "test", "path": "/c", "value": {"d": {"e": "f"}}}, | |
{"op": "move", "from": "/e", "path": "/g"}, | |
{"op": "test", "path": "/e", "value": "null"}, | |
{"op": "add", "path": "/b", "value": []}, | |
{"op": "add", "path": "/b/0", "value": "a"}, | |
{"op": "remove", "path": "/a"}, | |
{"op": "test", "path": "/a", "value": "null"}, | |
{"op": "replace", "path": "/b/0", "value": "c"}, | |
{"op": "test", "path": "/b/0", "value": "c"} | |
]$$::JSONB)='{"b": ["c"], "c": {"d": {"e": "f"}}, "g": {"d": {"e": "f"}}}'::JSONB; | |
SELECT 'Test failed `test` operation'; | |
DO $$ | |
BEGIN | |
SELECT jsonb_patch('{"a": 1}', '[{"op":"remove", "path":"/a"}, {"op":"test", "path": "/b/0", "value": "error"}]'::JSONB); | |
-- Force a division_by_zero exception if the test operation succeeds (which it should not do) | |
PERFORM 5 / 0; | |
EXCEPTION | |
WHEN DIVISION_BY_ZERO THEN RAISE 'Failed unit test'; | |
WHEN others THEN RETURN; | |
END; | |
$$ LANGUAGE PLPGSQL; |
@InfoSec812 This is really cool! Would you be interested in releasing this in under an OSS license?
I concur with @lucaswiman: please consider providing an OSS license for this!
Actually, this implementation does not properly raise exceptions as the standard requires. For example, if you try to remove a key that doesn't exist, the standard requires that this fails the whole operation. In this implementation, the operation will succeed but the change will not have been applied.
Also this doesn't properly implement the 'add' operation with arrays, because the standard calls for jsonb_insert behavior on an array and jsonb_set behavior on any object.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I even added a few unit tests to be sure everything is working as expected.