Skip to content

Instantly share code, notes, and snippets.

@johnrcui

johnrcui/json_patch.sql

Last active January 1, 2025 22:46
Show Gist options

  • Select an option

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

Select an option

Learn more about clone URLs
Save johnrcui/bafa28f7cd7f83c62187204e29914a1f to your computer and use it in GitHub Desktop.
Download ZIP
JSON Patch (RFC6902) implementation for MySQL
Raw
json_patch.sql
DELIMITER $$
DROP FUNCTION IF EXISTS rfc6901_to_json_path$$
-- Convert a JSON Pointer (RFC6901) into a MySQL JSON path
-- @param rfc6901_path object path in RFC6901 format
-- @returns path as an array of text
CREATE FUNCTION rfc6901_to_json_path(
rfc6901_path TEXT
)
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE json_path TEXT;
DECLARE path_parts TEXT;
DECLARE part TEXT;
DECLARE part_index INT DEFAULT 0;
DECLARE part_count INT;
IF rfc6901_path = '' THEN
RETURN '$';
END IF;
-- Split the path into parts based on '/'
SET path_parts = REPLACE(rfc6901_path, '~1', '/');
SET path_parts = REPLACE(path_parts, '~0', '~');
SET path_parts = SUBSTRING(path_parts, 2); -- Remove the leading '/'
SET part_count = LENGTH(path_parts) - LENGTH(REPLACE(path_parts, '/', '')) + 1;
SET json_path = '$';
WHILE part_index < part_count DO
SET part = SUBSTRING_INDEX(SUBSTRING_INDEX(path_parts, '/', part_index + 1), '/', -1);
IF part REGEXP '^[0-9]+$' THEN
-- If the part is a number, treat it as an array index
SET json_path = CONCAT(json_path, '[', part, ']');
ELSE
-- Otherwise, treat it as an object key
SET json_path = CONCAT(json_path, '.', part);
END IF;
SET part_index = part_index + 1;
END WHILE;
RETURN json_path;
END$$
DROP FUNCTION IF EXISTS json_patch_add$$
-- Insert a value into a document in a given RFC6901 path
-- @param document the JSON document to patch
-- @param rfc6901_path object path in RFC6901 format
-- @param value the JSON value to add or insert
-- @returns the patched document
CREATE FUNCTION json_patch_add(
document JSON,
rfc6901_path TEXT,
value JSON
)
RETURNS JSON
DETERMINISTIC
BEGIN
DECLARE is_target_array BOOL;
DECLARE is_target_append BOOL;
DECLARE path TEXT;
IF rfc6901_path = '' THEN
RETURN value;
END IF;
SET is_target_array = REGEXP_LIKE(rfc6901_path, '/([0-9]|[1-9][0-9]+|-)$');
SET is_target_append = REGEXP_LIKE(rfc6901_path, '/-$');
SET path = rfc6901_to_json_path(rfc6901_path);
IF is_target_array AND is_target_append THEN
RETURN JSON_ARRAY_APPEND(document, path, value);
ELSEIF is_target_array THEN
RETURN JSON_ARRAY_INSERT(document, path, value);
ELSE
RETURN JSON_INSERT(document, path, value);
END IF;
END$$
DROP FUNCTION IF EXISTS json_patch_remove$$
-- Removes a value from a document in the given path
-- @param document the JSON document to patch
-- @param rfc6901_path object path in RFC6901 format
-- @returns the patched document
CREATE FUNCTION json_patch_remove(
document JSON,
rfc6901_path TEXT
)
RETURNS JSON
DETERMINISTIC
BEGIN
DECLARE path TEXT;
IF rfc6901_path = '' THEN
RETURN NULL;
END IF;
SET path = rfc6901_to_json_path(rfc6901_path);
RETURN JSON_REMOVE(document, path);
END$$
DROP FUNCTION IF EXISTS json_patch_replace$$
-- Replace the value in the given path of a document
-- @param document the JSON document to patch
-- @param rfc6901_path object path in RFC6901 format
-- @param value the JSON value to assign
-- @returns the patched document
CREATE FUNCTION json_patch_replace(
document JSON,
rfc6901_path TEXT,
value JSON
)
RETURNS JSON
DETERMINISTIC
BEGIN
DECLARE path TEXT;
IF rfc6901_path = '' THEN
RETURN value;
END IF;
SET path = rfc6901_to_json_path(rfc6901_path);
RETURN JSON_REPLACE(document, path, value);
END$$
DROP FUNCTION IF EXISTS json_patch_copy$$
-- Copy the value from a given path into another path on the document
-- @param document the JSON 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 FUNCTION json_patch_copy(
document JSON,
from_rfc6901_path TEXT,
to_rfc6901_path TEXT
)
RETURNS JSON
DETERMINISTIC
BEGIN
DECLARE from_path TEXT;
DECLARE to_path TEXT;
DECLARE from_value JSON;
SET from_path = rfc6901_to_json_path(from_rfc6901_path);
SET from_value = JSON_EXTRACT(document, from_path);
IF to_rfc6901_path = '' THEN
RETURN from_value;
END IF;
SET to_path = rfc6901_to_json_path(to_rfc6901_path);
RETURN JSON_SET(document, to_path, from_value);
END$$
DROP FUNCTION IF EXISTS json_patch_move$$
-- Move the value from a given path into another path on the document
-- @param document the JSON 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 FUNCTION json_patch_move(
document JSON,
from_rfc6901_path TEXT,
to_rfc6901_path TEXT
)
RETURNS JSON
DETERMINISTIC
BEGIN
DECLARE from_path TEXT;
DECLARE to_path TEXT;
DECLARE from_value JSON;
SET from_path = rfc6901_to_json_path(from_rfc6901_path);
SET from_value = JSON_EXTRACT(document, from_path);
IF to_rfc6901_path = '' THEN
RETURN from_value;
END IF;
SET to_path = rfc6901_to_json_path(to_rfc6901_path);
RETURN JSON_REMOVE(JSON_SET(document, to_path, from_value), from_path);
END$$
DROP FUNCTION IF EXISTS json_patch_test$$
-- 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 JSON value to compare
-- @returns `TRUE` if the values are the same, `FALSE` otherwise
CREATE FUNCTION json_patch_test(
document JSON,
rfc6901_path TEXT,
value JSON
)
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
DECLARE path TEXT;
DECLARE target_value JSON;
SET path = rfc6901_to_json_path(rfc6901_path);
SET target_value = JSON_EXTRACT(document, path);
RETURN CASE
WHEN (target_value IS NULL AND value IS NULL) OR target_value = value THEN
TRUE
ELSE
FALSE
END;
END$$
DROP FUNCTION IF EXISTS json_patch$$
-- Apply a series of patch operations as defined by JSON Patch (RFC6902)
-- @param document the JSON document to patch
-- @param patches an array of patch operations in JSON 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 FUNCTION json_patch(
document JSON,
patches JSON
)
RETURNS JSON
DETERMINISTIC
BEGIN
DECLARE patched JSON;
DECLARE patch JSON;
DECLARE idx INTEGER DEFAULT 0;
DECLARE op TEXT;
IF JSON_TYPE(patches) != 'ARRAY' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Patches must be an array of operations';
END IF;
SET patched = document;
REPEAT
SET patch = JSON_EXTRACT(patches, CONCAT('$[', idx, ']'));
SET op = JSON_UNQUOTE(JSON_EXTRACT(patch, '$.op'));
CASE op
WHEN 'add' THEN
SET patched = json_patch_add(patched, JSON_UNQUOTE(JSON_EXTRACT(patch, '$.path')), JSON_EXTRACT(patch, '$.value'));
WHEN 'remove' THEN
SET patched = json_patch_remove(patched, JSON_UNQUOTE(JSON_EXTRACT(patch, '$.path')));
WHEN 'replace' THEN
SET patched = json_patch_replace(patched, JSON_UNQUOTE(JSON_EXTRACT(patch, '$.path')), JSON_EXTRACT(patch, '$.value'));
WHEN 'copy' THEN
SET patched = json_patch_copy(patched, JSON_UNQUOTE(JSON_EXTRACT(patch, '$.from')), JSON_UNQUOTE(JSON_EXTRACT(patch, '$.path')));
WHEN 'move' THEN
SET patched = json_patch_move(patched, JSON_UNQUOTE(JSON_EXTRACT(patch, '$.from')), JSON_UNQUOTE(JSON_EXTRACT(patch, '$.path')));
WHEN 'test' THEN
IF NOT json_patch_test(patched, JSON_UNQUOTE(JSON_EXTRACT(patch, '$.path')), JSON_EXTRACT(patch, '$.value')) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'JSON patch test operation failed';
END IF;
ELSE
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Unknown JSON patch operation';
END CASE;
SET idx = idx + 1;
UNTIL idx = JSON_LENGTH(patches)
END REPEAT;
RETURN patched;
END$$
DELIMITER ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment