Skip to content

Instantly share code, notes, and snippets.

@johnrcui

johnrcui/json_patch.plsql

Last active October 5, 2024 04:25
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save johnrcui/a15babdd6fa77f76a457ccd8d1a0c76f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save johnrcui/a15babdd6fa77f76a457ccd8d1a0c76f to your computer and use it in GitHub Desktop.
Download ZIP
JSON Patch (RFC6902) implementation for PostgresSQL
Raw
json_patch.plsql
-- Convert a JSON Pointer (RFC6901) into an array of text
-- @param rfc6901_path object path in RFC6901 format
-- @returns path as an array of text
CREATE OR REPLACE FUNCTION rfc6901_to_json_path(rfc6901_path TEXT)
RETURNS TEXT[]
LANGUAGE plpgsql
IMMUTABLE
STRICT
PARALLEL
SAFE AS
$$
BEGIN
IF rfc6901_path = '/' THEN
RETURN '{""}';
ELSE
RETURN STRING_TO_ARRAY(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
rfc6901_path
, '^/'
, ''
)
, '(?:((?:~[01]|[^/])+)/)'
, '\1\x03'
, 'g'
)
, '~0'
, '~'
, 'g'
)
, '~1'
, '/'
, 'g'
)
, '\x03'
);
END IF;
END;
$$;
-- Insert a value into a document in a given RFC6901 path
-- @param document the JSONB document to patch
-- @param rfc6901_path object path in RFC6901 format
-- @param value the JSONB value to add or insert
-- @returns the patched document
CREATE OR REPLACE FUNCTION jsonb_patch_add(document JSONB, rfc6901_path TEXT, value JSONB)
RETURNS JSONB
LANGUAGE plpgsql
IMMUTABLE
STRICT
PARALLEL
SAFE AS
$$
DECLARE
path TEXT[];
target_path TEXT[];
is_target_array BOOLEAN;
is_target_append BOOLEAN;
BEGIN
IF (rfc6901_path = '') THEN
RETURN value;
END IF;
path = rfc6901_to_json_path(rfc6901_path);
target_path = COALESCE(TRIM_ARRAY(path, 1), '{}');
is_target_array = rfc6901_path ~ '/(\d+|-)$' AND 'array' = JSONB_TYPEOF(JSONB_EXTRACT_PATH(document, VARIADIC target_path));
is_target_append = rfc6901_path ~ '/-$';
IF (is_target_array AND is_target_append) THEN
RETURN jsonb_insert(document, ARRAY_APPEND(target_path, '-1'), value, TRUE);
ELSE
RETURN jsonb_insert(document, path, value);
END IF;
END;
$$;
-- Removes a value from a document in the given path
-- @param document the JSONB document to patch
-- @param rfc6901_path object path in RFC6901 format
-- @returns the patched document
CREATE OR REPLACE FUNCTION jsonb_patch_remove(document JSONB, rfc6901_path TEXT)
RETURNS JSONB
LANGUAGE plpgsql
IMMUTABLE
STRICT
PARALLEL
SAFE AS
$$
BEGIN
IF (rfc6901_path = '') THEN
RETURN NULL;
END IF;
RETURN document #- rfc6901_to_json_path(rfc6901_path);
END;
$$;
-- Replace the value in the given path of a document
-- @param document the JSONB document to patch
-- @param rfc6901_path object path in RFC6901 format
-- @param value the JSONB value to assign
-- @returns the patched document
CREATE OR REPLACE FUNCTION jsonb_patch_replace(document JSONB, rfc6901_path TEXT, value JSONB)
RETURNS JSONB
LANGUAGE plpgsql
IMMUTABLE
STRICT
PARALLEL
SAFE AS
$$
DECLARE
path TEXT[];
BEGIN
IF (rfc6901_path = '') THEN
RETURN value;
END IF;
path = rfc6901_to_json_path(rfc6901_path);
RETURN jsonb_set(document, path, value, TRUE);
END;
$$;
-- Copy the value from a given path into another path on the document
-- @param document the JSONB document to patch
-- @param from_rfc6901_path the path to copy from in RFC6901 format
-- @param to_rfc6901_path the path to copy into in RFC6901 format
-- @returns the patched document
CREATE OR REPLACE FUNCTION jsonb_patch_copy(document JSONB, from_rfc6901_path TEXT, to_rfc6901_path TEXT)
RETURNS JSONB
LANGUAGE plpgsql
IMMUTABLE
STRICT
PARALLEL
SAFE AS
$$
DECLARE
from_path TEXT[];
to_path TEXT[];
from_value JSONB;
BEGIN
from_path = rfc6901_to_json_path(from_rfc6901_path);
from_value = JSONB_EXTRACT_PATH(document, VARIADIC from_path);
IF (to_rfc6901_path = '') THEN
RETURN from_value;
END IF;
to_path = rfc6901_to_json_path(to_rfc6901_path);
RETURN jsonb_set(document, to_path, from_value, TRUE);
END;
$$;
-- Move the value from a given path into another path on the document
-- @param document the JSONB document to patch
-- @param from_rfc6901_path the path to move from in RFC6901 format
-- @param to_rfc6901_path the path to move into in RFC6901 format
-- @returns the patched document
CREATE OR REPLACE FUNCTION jsonb_patch_move(document JSONB, from_rfc6901_path TEXT, to_rfc6901_path TEXT)
RETURNS JSONB
LANGUAGE plpgsql
IMMUTABLE
STRICT
PARALLEL
SAFE AS
$$
DECLARE
from_path TEXT[];
to_path TEXT[];
from_value JSONB;
BEGIN
from_path = rfc6901_to_json_path(from_rfc6901_path);
from_value = JSONB_EXTRACT_PATH(document, VARIADIC from_path);
IF (to_rfc6901_path = '') THEN
RETURN from_value;
END IF;
to_path = rfc6901_to_json_path(to_rfc6901_path);
RETURN jsonb_set(document, to_path, from_value, TRUE) #- from_path;
END;
$$;
-- Test if the value in a path of a document matches a given value
-- @param document the document to test
-- @param rfc6901_path object path whose value to test in RF6901 format
-- @param value a JSONB value to compare
-- @returns `TRUE` if the values are the same, `FALSE` otherwise
CREATE OR REPLACE FUNCTION jsonb_patch_test(document JSONB, rfc6901_path TEXT, value JSONB)
RETURNS BOOLEAN
LANGUAGE plpgsql
IMMUTABLE
STRICT
PARALLEL
SAFE AS
$$
DECLARE
path TEXT[];
target_value JSONB;
BEGIN
path = rfc6901_to_json_path(rfc6901_path);
target_value = JSONB_EXTRACT_PATH(document, VARIADIC path);
RETURN CASE
WHEN (target_value IS NULL AND value IS NULL) OR target_value = value THEN
TRUE
ELSE
FALSE
END;
END;
$$;
-- Apply a series of patch operations as defined by JSON Patch (RFC6902)
-- @param document the JSONB document to patch
-- @param patches an array of patch operations in JSONB format
-- @returns the patched document
-- @throws if `patches` is not a JSON array
-- @throws if a given patch is not a supported operation
-- @throws if the patch contains a `test` operation and the test fails
CREATE OR REPLACE FUNCTION jsonb_patch(document JSONB, patches JSONB)
RETURNS JSONB
LANGUAGE plpgsql
IMMUTABLE
STRICT
PARALLEL
SAFE AS
$$
DECLARE
patched JSONB;
patch JSONB;
op TEXT;
BEGIN
IF JSONB_TYPEOF(patches) != 'array' THEN
RAISE EXCEPTION 'Patches must be an array of operations';
END IF;
patched = document;
FOR patch IN SELECT * FROM JSONB_ARRAY_ELEMENTS(patches)
LOOP
op = patch->>'op';
IF (op = 'add') THEN
patched = jsonb_patch_add(patched, patch->>'path', patch->'value');
ELSIF (op = 'remove') THEN
patched = jsonb_patch_remove(patched, patch->>'path');
ELSIF (op = 'replace') THEN
patched = jsonb_patch_replace(patched, patch->>'path', patch->'value');
ELSIF (op = 'copy') THEN
patched = jsonb_patch_copy(patched, patch->>'from', patch->>'path');
ELSIF (op = 'move') THEN
patched = jsonb_patch_move(patched, patch->>'from', patch->>'path');
ELSIF (op = 'test'
AND NOT jsonb_patch_test(patched, patch->>'path', patch->>'value')) THEN
RAISE EXCEPTION 'JSON patch test operation failed';
ELSE
RAISE EXCEPTION 'Unknown JSON patch operation';
END IF;
END LOOP;
RETURN patched;
END;
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment