Understanding the internals of Marten native partial updates using Postgres PL/pgSQL.

Babu AnnamalaiBabu Annamalai
11 min read

This is a continuation of https://mysticmind.dev/marten-native-partial-updates-patching. All the native patch operations are driven by a bunch of custom PL/pgSQL functions with prefix mt_jsonb_. Marten uses JSONB for all its JSON storage hence all the functions are written to work with JSONB.

My primary goal was to have a collection of functions for patching which can work well across for Postgres versions 9.6 and above. All these functions can be used independently outside of Marten as a suite of generic PL/pgSQL patching functionality.

We have the following functions which together handles all the supported patching operations:

  • mt_jsonb_path_to_array

  • mt_jsonb_fix_null_parent

  • mt_jsonb_copy

  • mt_jsonb_duplicate

  • mt_jsonb_move

  • mt_jsonb_append

  • mt_jsonb_insert

  • mt_jsonb_remove

  • mt_jsonb_increment

  • mt_jsonb_patch

Let us dive in and look at each of the functions in detail.

mt_jsonb_path_to_array function

CREATE OR REPLACE FUNCTION mt_jsonb_path_to_array(text, character)
    RETURNS text[]
    LANGUAGE plpgsql
AS $function$
DECLARE
    location ALIAS FOR $1;
    regex_pattern ALIAS FOR $2;
BEGIN
RETURN regexp_split_to_array(location, regex_pattern)::text[];
END;
$function$;

This is a helper function to translate the JSON path string expression to an array of JSON path name elements. In Marten, we use dot notation as a separator to pass JSON string expression.

Let us say, we have a JSON as below:

{
    "name": "Luke Skywalker",
    "details": {
        "eyeColor": "Blue",
        "species": "Human"
    }
}

Now to access say eyeColor, the JSON path expression is details.eyeColor. When you call the function mt_jsonb_path_to_array by passing details.eyeColor and regex pattern as \., we get back a text array as below:

{details,eyeColor}

mt_jsonb_fix_null_parent function

CREATE OR REPLACE FUNCTION {databaseSchema}.mt_jsonb_fix_null_parent(jsonb, text[])
    RETURNS jsonb
    LANGUAGE plpgsql
AS $function$
DECLARE
    retval ALIAS FOR $1;
    dst_path ALIAS FOR $2;
    dst_path_segment text[] = ARRAY[]::text[];
    dst_path_array_length integer;
    i integer = 1;
BEGIN
    dst_path_array_length = array_length(dst_path, 1);
    WHILE i <=(dst_path_array_length - 1)
    LOOP
        dst_path_segment = dst_path_segment || ARRAY[dst_path[i]];
        IF retval #> dst_path_segment = 'null'::jsonb THEN
            retval = jsonb_set(retval, dst_path_segment, '{}'::jsonb, TRUE);
        END IF;
        i = i + 1;
    END LOOP;
    RETURN retval;
END;
$function$;

This is a very important helper function which helps us to deal with patching at a deeply nested level especially for copy and move operations. Note that existing JSONB operators falls short a bit on these areas. The main purpose of this function is to ensure that all parent objects in a specified path within a JSONB structure exist and are not null. This is useful in scenarios where you need to add or modify nested properties in a JSONB object, but some of the parent objects might not exist (i.e., they're null).

Example

For example, if you have a JSONB object like this:

{
  "a": null
}

When you run the function as below:

SELECT mt_jsonb_fix_null_parent('{"a": null}'::jsonb, ARRAY['a', 'b', 'x']);

After these operations, our JSONB would look like: {"a": {"b": {"x": {}}}

And you want to ensure that you can safely set a value at a.x.y or b.c.d, this function would first convert the null values to empty objects, allowing you to add nested properties without encountering null pointer exceptions.

mt_jsonb_copy

This function allows you to copy a value from one path in a JSONB object to another, ensuring that the destination path is properly set up to receive the new value.

CREATE OR REPLACE FUNCTION mt_jsonb_copy(jsonb, text[], text[])
    RETURNS jsonb
    LANGUAGE plpgsql
AS $function$
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 = mt_jsonb_fix_null_parent(retval, dst_path);
    RETURN jsonb_set(retval, dst_path, tmp_value::jsonb, TRUE);
END;
$function$;

Example

Imagine you have a JSONB object representing a character in the Star Wars universe, like this:

{
    "name": "Luke Skywalker",
    "lightsaber": {
        "color": "green",
        "type": "Jedi"
    }
}

Suppose you want to copy the lightsaber color to a new path, perhaps to a summary section of the character's data. You would call the mt_jsonb_copy function like this:

SELECT mt_jsonb_copy(
    '{"name": "Luke Skywalker", "lightsaber": {"color": "green", "type": "Jedi"}}'::jsonb, -- source data
    '{lightsaber,color}',  -- Source path
    '{summary,lightsaber_color}'  -- Destination path
);

Result

After executing the function, the modified JSONB object would look like this:

{
    "name": "Luke Skywalker",
    "lightsaber": {
        "color": "green",
        "type": "Jedi"
    },
    "summary": {
        "lightsaber_color": "green"
    }
}

mt_jsonb_duplicate

This function allows for duplicating values from a specified location in a JSONB object to multiple target paths. This means you can take a single piece of data and replicate it across various keys within the JSON structure. Note that this function uses both mt_jsonb_path_to_array and mt_jsonb_copy.

CREATE OR REPLACE FUNCTION mt_jsonb_duplicate(jsonb, text[], jsonb)
RETURNS jsonb
LANGUAGE plpgsql
AS $function$
DECLARE
    retval ALIAS FOR $1;
    location ALIAS FOR $2;
    targets ALIAS FOR $3;
    tmp_value jsonb;
    target_path text[];
    target text;
BEGIN
    FOR target IN SELECT jsonb_array_elements_text(targets)
    LOOP
        target_path = mt_jsonb_path_to_array(target, '\.');
        retval = mt_jsonb_copy(retval, location, target_path);
    END LOOP;

    RETURN retval;
END;
$function$;

Example

Let's say we have a JSONB object representing a Star Wars character, Luke Skywalker:

{
    "name": "Luke Skywalker",
    "species": "Human",
    "homeworld": "Tatooine",
    "films": ["A New Hope", "The Empire Strikes Back"]
}

We want to duplicate the homeworld information into two new keys: origin and birthplace. User the function as below:

SELECT {databaseSchema}.mt_jsonb_duplicate(
    '{"name": "Luke Skywalker", "species": "Human", "homeworld": "Tatooine", "films": ["A New Hope", "The Empire Strikes Back"]}'::jsonb, -- source data
    '{homeworld}', -- location
    '["origin", "birthplace"]'::jsonb -- target paths
);

Result:

{
    "name": "Luke Skywalker",
    "species": "Human",
    "homeworld": "Tatooine",
    "films": ["A New Hope", "The Empire Strikes Back"],
    "origin": "Tatooine",
    "birthplace": "Tatooine"
}

mt_jsonb_move

This function allows to manipulate JSONB data by moving a value from one object key to another within a JSONB object.

CREATE OR REPLACE FUNCTION mt_jsonb_move(jsonb, text[], text)
    RETURNS jsonb
    LANGUAGE plpgsql
AS $function$
DECLARE
    retval ALIAS FOR $1;
    src_path ALIAS FOR $2;
    dst_name ALIAS FOR $3;
    dst_path text[];
    tmp_value jsonb;
BEGIN
    tmp_value = retval #> src_path;
    retval = retval #- src_path;
    dst_path = src_path;
    dst_path[array_length(dst_path, 1)] = dst_name;
    retval = mt_jsonb_fix_null_parent(retval, dst_path);
    RETURN jsonb_set(retval, dst_path, tmp_value, TRUE);
END;
$function$;

Example

Let's say we have a JSONB object representing a Star Wars character:

{
    "characters": {
        "Luke": {
            "species": "Human",
            "homeworld": "Tatooine"
        },
        "Yoda": {
            "species": "Yoda's species",
            "homeworld": "Unknown"
        }
    }
}

If we want to change the key homeworld for "Luke" to a new key name home, we would call the function like this:

SELECT {databaseSchema}.mt_jsonb_move(
    '{"characters": {"Luke": {"species": "Human", "homeworld": "Tatooine"}, "Yoda": {"species": "Yoda\'s species", "homeworld": "Unknown"}}}}'::jsonb, -- source data
    '{characters, Luke, homeworld}', -- source path
    'home' -- destination
);

Result:

{
    "characters": {
        "Luke": {
            "species": "Human",
            "home": "Tatooine"
        },
        "Yoda": {
            "species": "Yoda's species",
            "homeworld": "Unknown"
        }
    }
}

mt_jsonb_append

This function is used to add a JSONB value to an array at a specific location in a JSONB object in PostgreSQL. It checks if the value already exists at that location and only adds it if it doesn't (if specified). This helps prevent duplicates.

CREATE OR REPLACE FUNCTION mt_jsonb_append(jsonb, text[], jsonb, boolean)
    RETURNS jsonb
    LANGUAGE plpgsql
AS $function$
DECLARE
    retval ALIAS FOR $1;
    location ALIAS FOR $2;
    val ALIAS FOR $3;
    if_not_exists ALIAS FOR $4;
    tmp_value jsonb;
BEGIN
    tmp_value = retval #> location;
    IF tmp_value IS NOT NULL AND jsonb_typeof(tmp_value) = 'array' THEN
        CASE
            WHEN NOT if_not_exists THEN
                retval = jsonb_set(retval, location, tmp_value || val, FALSE);
            WHEN jsonb_typeof(val) = 'object' AND NOT tmp_value @> jsonb_build_array(val) THEN
                retval = jsonb_set(retval, location, tmp_value || val, FALSE);
            WHEN jsonb_typeof(val) <> 'object' AND NOT tmp_value @> val THEN
                retval = jsonb_set(retval, location, tmp_value || val, FALSE);
            ELSE NULL;
        END CASE;
    END IF;
    RETURN retval;
END;
$function$;

Example

Let's say you have the following JSONB object:

{
    "name": "Han Solo",
    "friends": [
        {"name": "Chewbacca"},
        {"name": "Leia Organa"}
    ]
}

You want to add "Luke Skywalker" to the friends array, but only if it doesn't already exist. Here’s how you would call the function:

SELECT {databaseSchema}.mt_jsonb_append(
    '{"name": "Han Solo", "friends": [{"name": "Chewbacca"}, {"name": "Leia Organa"}]}'::jsonb, -- The JSONB object
    '{friends}', -- The path to the friends array
    '{"name": "Luke Skywalker"}'::jsonb, -- The nested object to add
    true -- Only add if it doesn't exist
);

Result

After running the function, the modified JSONB object would be:

{
    "name": "Han Solo",
    "friends": [
        {"name": "Chewbacca"},
        {"name": "Leia Organa"},
        {"name": "Luke Skywalker"}
    ]
}

If you called the function again with "Luke Skywalker" and the same parameters, it would not add it again.

mt_jsonb_insert

This function is designed to insert a JSONB value into a specified location within a target JSONB object array. The function checks if the specified location already contains the value to be inserted, and if not, it adds the new value based on the provided index or appends it at the end if the index is not specified.

CREATE OR REPLACE FUNCTION mt_jsonb_insert(jsonb, text[], jsonb, integer, boolean)
    RETURNS jsonb
    LANGUAGE plpgsql
AS $function$
DECLARE
    retval ALIAS FOR $1;
    location ALIAS FOR $2;
    val ALIAS FOR $3;
    elm_index ALIAS FOR $4;
    if_not_exists ALIAS FOR $5;
    tmp_value jsonb;
BEGIN
    tmp_value = retval #> location;
    IF tmp_value IS NOT NULL AND jsonb_typeof(tmp_value) = 'array' THEN
        IF elm_index IS NULL THEN
            elm_index = jsonb_array_length(tmp_value) + 1;
        END IF;
        CASE
            WHEN NOT if_not_exists THEN
                retval = jsonb_insert(retval, location || elm_index::text, val);
            WHEN jsonb_typeof(val) = 'object' AND NOT tmp_value @> jsonb_build_array(val) THEN
                retval = jsonb_insert(retval, location || elm_index::text, val);
            WHEN jsonb_typeof(val) <> 'object' AND NOT tmp_value @> val THEN
                retval = jsonb_insert(retval, location || elm_index::text, val);
            ELSE NULL;
        END CASE;
    END IF;
    RETURN retval;
END;
$function$;

Example

Suppose you have a JSONB object representing a collection of Star Wars characters, and you want to insert a new character into an array of characters. Here's how you might use the function:

Initial JSONB Object:

{
    "characters": [
        {"name": "Luke Skywalker", "role": "Jedi"},
        {"name": "Darth Vader", "role": "Sith"}
    ]
}

Inserting a New Character:

SELECT mt_jsonb_insert(
   '{"characters":[{"name": "Luke Skywalker", "role": "Jedi"},{"name": "Darth Vader", "role": "Sith"}]}'::jsonb, -- source data
   '{characters}',  -- location
   '{"name": "Han Solo", "role": "Smuggler"}'::jsonb,  -- value to insert
   NULL,  -- elm_index (NULL means append)
   FALSE  -- if_not_exists
);

Resulting JSONB Object:

{
   "characters": [
       {"name": "Luke Skywalker", "role": "Jedi"},
       {"name": "Darth Vader", "role": "Sith"},
       {"name": "Han Solo", "role": "Smuggler"}
   ]
}

mt_jsonb_remove

This function allows to remove a specific value from a JSONB array located at a specified path within a JSONB object.

CREATE OR REPLACE FUNCTION mt_jsonb_remove(jsonb, text[], jsonb)
    RETURNS jsonb
    LANGUAGE plpgsql
AS $function$
DECLARE
    retval ALIAS FOR $1;
    location ALIAS FOR $2;
    val ALIAS FOR $3;
    tmp_value jsonb;
BEGIN
    tmp_value = retval #> location;
    IF tmp_value IS NOT NULL AND jsonb_typeof(tmp_value) = 'array' THEN
        tmp_value =(SELECT jsonb_agg(elem)
        FROM jsonb_array_elements(tmp_value) AS elem
        WHERE elem <> val);

        IF tmp_value IS NULL THEN
            tmp_value = '[]'::jsonb;
        END IF;
    END IF;
    RETURN jsonb_set(retval, location, tmp_value, FALSE);
END;
$function$;

Example

Suppose we have the following JSONB object representing a list of Star Wars characters:

{
    "characters": [
        {"name": "Luke Skywalker"},
        {"name": "Darth Vader"},
        {"name": "Leia Organa"},
        {"name": "Han Solo"}
    ]
}

If we want to remove "Darth Vader" from the characters array, we would call the function like this:

SELECT mt_jsonb_remove(
    '{"characters": [{"name": "Luke Skywalker"}, {"name": "Darth Vader"}, {"name": "Leia Organa"}, {"name": "Han Solo"}]}}'::jsonb, -- source data
    '{characters}', -- source path to array
    '{"name": "Darth Vader"}'::jsonb -- array item to delete
);

The resulting JSONB object would be:

{
    "characters": [
        {"name": "Luke Skywalker"},
        {"name": "Leia Organa"},
        {"name": "Han Solo"}
    ]
}

mt_jsonb_increment

This function increments a numeric value stored in a JSONB object at a specified location (key path). It takes three parameters: the JSONB object, an array of text representing the key path to the value to be incremented, and the numeric value to add. If the specified location does not exist, it initializes it to 0 before performing the increment.

CREATE OR REPLACE FUNCTION mt_jsonb_increment(jsonb, text[], numeric)
    RETURNS jsonb
    LANGUAGE plpgsql
AS $function$
DECLARE
retval ALIAS FOR $1;
    location ALIAS FOR $2;
    increment_value ALIAS FOR $3;
    tmp_value jsonb;
BEGIN
    tmp_value = retval #> location;
    IF tmp_value IS NULL THEN
        tmp_value = to_jsonb(0);
END IF;

RETURN jsonb_set(retval, location, to_jsonb(tmp_value::numeric + increment_value), TRUE);
END;
$function$;

Example

Suppose we have a JSONB object representing the scores of various Star Wars characters in a game:

{
    "Luke Skywalker": {
        "score": 10
    },
    "Darth Vader": {
        "score": 15
    }
}

We want to increment Luke Skywalker score by 5. The function call would look like this:

SELECT mt_jsonb_increment(
    '{"Luke Skywalker": {"score": 10}, "Darth Vader": {"score": 15}}'::jsonb,
    '{Luke Skywalker, score}',
    5
);

After executing the function, the updated JSONB object would be:

{
    "Luke Skywalker": {
        "score": 15
    },
    "Darth Vader": {
        "score": 15
    }
}

In this example, Luke Skywalker's score has been successfully incremented from 10 to 15.

mt_jsonb_patch

This function allows to apply a series of modifications (patches) to a JSONB object. It allows you to manipulate JSONB data by specifying a set of operations to be performed on the original JSONB object.

CREATE OR REPLACE FUNCTION mt_jsonb_patch(jsonb, jsonb)
    RETURNS jsonb
    LANGUAGE plpgsql
AS $function$
DECLARE
    retval ALIAS FOR $1;
    patchset ALIAS FOR $2;
    patch jsonb;
    patch_path text[];
    value jsonb;
BEGIN
    FOR patch IN SELECT * from jsonb_array_elements(patchset)
    LOOP
        patch_path = mt_jsonb_path_to_array((patch->>'path')::text, '\.');

        CASE patch->>'type'
            WHEN 'set' THEN
                retval = jsonb_set(retval, patch_path,(patch->'value')::jsonb, TRUE);
        WHEN 'delete' THEN
                retval = retval#-patch_path;
        WHEN 'append' THEN
                retval = mt_jsonb_append(retval, patch_path,(patch->'value')::jsonb, FALSE);
        WHEN 'append_if_not_exists' THEN
                retval = mt_jsonb_append(retval, patch_path,(patch->'value')::jsonb, TRUE);
        WHEN 'insert' THEN
                retval = mt_jsonb_insert(retval, patch_path,(patch->'value')::jsonb,(patch->>'index')::integer, FALSE);
        WHEN 'insert_if_not_exists' THEN
                retval = mt_jsonb_insert(retval, patch_path,(patch->'value')::jsonb,(patch->>'index')::integer, TRUE);
        WHEN 'remove' THEN
                retval = mt_jsonb_remove(retval, patch_path,(patch->'value')::jsonb);
        WHEN 'duplicate' THEN
                retval = mt_jsonb_duplicate(retval, patch_path,(patch->'targets')::jsonb);
        WHEN 'rename' THEN
                retval = mt_jsonb_move(retval, patch_path,(patch->>'to')::text);
        WHEN 'increment' THEN
                retval = mt_jsonb_increment(retval, patch_path,(patch->>'increment')::numeric);
        WHEN 'increment_float' THEN
                retval = mt_jsonb_increment(retval, patch_path,(patch->>'increment')::numeric);
        ELSE NULL;
        END CASE;
    END LOOP;
    RETURN retval;
END;
$function$;

The function iterates through each patch in the patchset and applies the specified operation based on the type of each patch. The supported operations include:

  • set: Sets a value at a specified path.

  • delete: Deletes a value at a specified path.

  • append: Appends a value to an array at a specified path.

  • append_if_not_exists: Appends a value only if it does not already exist at the specified path.

  • insert: Inserts a value at a specified index in an array.

  • insert_if_not_exists: Inserts a value at a specified index only if it does not already exist.

  • remove: Removes a value from an array at a specified path.

  • duplicate: Duplicates values from specified targets.

  • rename: Renames a key at a specified path.

  • increment: Increments a numeric value at a specified path.

  • increment_float: Increments a floating-point value at a specified path.

The function returns the modified JSONB object after applying all patches.

Example

Here’s the initial JSONB data representing a Star Wars character:

{
  "name": "Luke Skywalker",
  "age": 23,
  "tags": ["Jedi", "Hero"],
  "skills": {
    "lightsaber": ["green", "blue"],
    "force": ["telekinesis", "mind control"]
  },
  "allies": [
    {"name": "Han Solo", "role": "Pilot"},
    {"name": "Leia Organa", "role": "Leader"}
  ]
}

Let us define the patchset which defines a series of patch operations:

[
    {
        "type": "set",
        "path": "name",
        "value": "Luke Skywalker (Jedi Master)"
    },
    {
        "type": "delete",
        "path": "age"
    },
    {
        "type": "append",
        "path": "tags",
        "value": "Legend"
    },
    {
        "type": "insert",
        "path": "allies",
        "value": {"name": "Yoda", "role": "Mentor"},
        "index": 2
    },
    {
        "type": "rename",
        "path": "skills.lightsaber",
        "to": "saber_colors"
    }
]

The function call would like the below:

SELECT mt_jsonb_patch(
    '{
        "name": "Luke Skywalker",
        "age": 23,
        "tags": ["Jedi", "Hero"],
        "skills": {
            "lightsaber": ["green", "blue"],
            "force": ["telekinesis", "mind control"]
        },
        "allies": [
            {"name": "Han Solo", "role": "Pilot"},
            {"name": "Leia Organa", "role": "Leader"}
        ]
    }'::jsonb,
    '[
        {
            "type": "set",
            "path": "name",
            "value": "Luke Skywalker (Jedi Master)"
        },
        {
            "type": "delete",
            "path": "age"
        },
        {
            "type": "append",
            "path": "tags",
            "value": "Legend"
        },
        {
            "type": "insert",
            "path": "allies",
            "value": {"name": "Yoda", "role": "Mentor"},
            "index": 2
        },
        {
            "type": "rename",
            "path": "skills.lightsaber",
            "to": "saber_colors"
        }
    ]'::jsonb
) AS updated_data;

After applying all the patches, the resulting JSONB data would look like this:

{
  "name": "Luke Skywalker (Jedi Master)",
  "tags": ["Jedi", "Hero", "Legend"],
  "skills": {
    "saber_colors": ["green", "blue"],
    "force": ["telekinesis", "mind control"]
  },
  "allies": [
    {"name": "Han Solo", "role": "Pilot"},
    {"name": "Leia Organa", "role": "Leader"},
    {"name": "Yoda", "role": "Mentor"}
  ]
}

In summary, I build these patching functions to be used across various Postgres versions without worrying about JSON operators or JSON path. These functions power the Marten native patching functionality.

Note that these functions automatically get added to each of the database schemas when Marten runs so you can readily use the mt_jsonb_* functions in your custom SQL to build your patching functionality as you see it fit.

1
Subscribe to my newsletter

Read articles from Babu Annamalai directly inside your inbox. Subscribe to the newsletter, and don't miss out.

#martendbPostgreSQLdotnet

Written by

Babu Annamalai
Babu Annamalai

Hey 👋 I am Babu Annamalai, partner and co-founder at Radarleaf Technologies. A polyglot software developer based in Bengaluru, love to work with web technologies and OSS . I am a lifetime learner in exploration mode. I spend a plenty of time working on the following OSS projects as below: Co-maintainer of Marten. I am available in the project's gitter channel to help with any queries/questions on Marten. This is the most successful and active project in terms of user base. Creator and maintainer of ReverseMarkdown.NET, MysticMind.PostgresEmbed and .NET Sort Refs I also contribute to other OSS projects as well.