Pure PostgreSQL implementation of JSONPatch

jsonb_patch.sql

!!
!! 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;

I even added a few unit tests to be sure everything is working as expected.

@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.