postgres jsonb patch

jsonb_patch.sql

-- ISC LICENSE
--
-- Copyright (c) 2021, Marc Bachmann <[email protected]>

-- Permission to use, copy, modify, and/or distribute this software for any
-- purpose with or without fee is hereby granted, provided that the above
-- copyright notice and this permission notice appear in all copies.

-- THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
-- WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
-- MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
-- ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
-- WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
-- ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
-- OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.


-- json = '[
--   {"op":"add", "path": "/foo/bar","value": {"foo":"bar", "hello": "world"}},
--   {"op":"remove","path":"/foo/bar/foo"}
-- ]'
--
-- select li_jsonb_patch('{}', json::jsonb);
--   -> {"foo": {"bar": {"hello": "world"}}}
CREATE OR REPLACE FUNCTION li_jsonb_patch_array_add(val jsonb, key text, value jsonb)
RETURNS jsonb
LANGUAGE plpgsql IMMUTABLE
AS $$
DECLARE
  pos int;
  len int;
  res jsonb[];
BEGIN
  IF (val IS NULL) THEN val := '[]'; END IF;
  IF key = '-' THEN RETURN val || jsonb_build_array(value); END IF;

  BEGIN
    pos := key::int;
  EXCEPTION WHEN OTHERS THEN
    RAISE EXCEPTION 'invalid_path' USING HINT = 'must be integer';
  END;

  IF pos::text != key THEN
    RAISE EXCEPTION 'invalid_path'  USING HINT = 'must not have leading zeros';
  END IF;

  IF pos < 0 THEN
    RAISE EXCEPTION 'invalid_path' USING HINT = 'out of bounds (lower)';
  END IF;

  SELECT array_agg(v) INTO res FROM jsonb_array_elements(val) v;
  len := array_length(res, 1);
  IF pos > len THEN
    RAISE EXCEPTION 'invalid_path' USING HINT = 'out of bounds (upper)';
  END IF;

  IF pos = len THEN RETURN val || jsonb_build_array(value); END IF;
  IF pos = 0 THEN RETURN jsonb_build_array(value) || val; END IF;

  return to_jsonb(res[1:pos] || value || res[pos + 1:]);
END;
$$;

CREATE OR REPLACE FUNCTION li_json_pointer(str text)
RETURNS text[] AS $$
DECLARE
  pointer text[] := ARRAY[]::text[];
BEGIN
  IF str = '/' THEN RETURN ARRAY['']; END IF;
  FOREACH str IN ARRAY string_to_array(right(str, -1), '/') LOOP
    pointer := pointer || REPLACE(REPLACE(str, '~0', '~'), '~1', '/');
  END LOOP;
  RETURN pointer;
END $$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION li_jsonb_patch(val jsonb, operations jsonb)
RETURNS jsonb
LANGUAGE plpgsql IMMUTABLE
AS $$
DECLARE
  operation jsonb;
  pointer text[];
  current_path text[];
  m_text text;
  m_hint text;
BEGIN
  IF (val IS NULL) THEN val = '{}'::jsonb; END IF;

  FOR operation IN SELECT * FROM jsonb_array_elements(operations) LOOP
    BEGIN
      IF
        operation->>'path' IS NULL
        OR
        left(operation->>'path', 1) NOT IN ('', '/')
      THEN
        RAISE EXCEPTION 'invalid_path' USING HINT = 'path must start with /';
      END IF;

      pointer := li_json_pointer(operation->>'path');

      CASE operation->>'op'
        WHEN 'test' THEN
          IF operation->'value' IS NULL THEN
            RAISE EXCEPTION 'invalid_op' USING HINT = 'value is required';
          ELSIF val#>pointer IS NULL OR (val#>pointer)::text != (operation->'value')::text THEN
            RAISE EXCEPTION 'test_failed';
          END IF;
        WHEN 'remove' THEN
          IF array_length(pointer, 1) IS NULL THEN
            val := NULL;
          ELSE
            IF val #> pointer IS NULL THEN
              RAISE EXCEPTION 'remove_failed' USING HINT = 'field does not exist';
            END IF;
            val = val #- pointer;
          END IF;
        WHEN 'add' THEN
          IF operation->'value' IS NULL THEN
            RAISE EXCEPTION 'invalid_value'
            USING HINT = 'operation requires a value';
          ELSIF array_length(pointer, 1) IS NULL THEN
            val := operation->'value';
          ELSE
            for i IN 1..(array_upper(pointer, 1) - 1) LOOP
              current_path := current_path || pointer[i];
              IF (
                val #> current_path IS NULL
                OR
                jsonb_typeof(val #> current_path) NOT IN ('object', 'array')
              ) THEN
                val := jsonb_set(val, current_path, '{}');
              END IF;
            END LOOP;
            IF array_length(current_path, 1) IS NULL AND jsonb_typeof(val) = 'array' THEN
              val := li_jsonb_patch_array_add(
                val,
                pointer[array_length(pointer, 1)], operation->'value'
              );
            ELSIF jsonb_typeof(val #> current_path) = 'array' THEN
              val := jsonb_set(
                val,
                current_path,
                li_jsonb_patch_array_add(
                  val #> current_path,
                  pointer[array_length(pointer, 1)],
                  operation->'value'
                )
              );
            ELSE
              val := jsonb_set(val, pointer, operation->'value');
            END IF;
            current_path := NULL;
          END IF;
        ELSE
          RAISE EXCEPTION 'invalid_op'
          USING HINT = 'only the operations add, test and remove are supported';
      END CASE;
    EXCEPTION WHEN OTHERS THEN
      GET STACKED DIAGNOSTICS m_text = MESSAGE_TEXT, m_hint = PG_EXCEPTION_HINT;

      m_text := jsonb_build_object(
        'error', m_text,
        'message', m_hint,
        'operation', operation
      )::text;

      RAISE EXCEPTION 'Invalid Patch Operation: %', m_text
      USING
        HINT = 'patch_invalid_operation',
        DETAIL = m_text;
    END;
  END LOOP;
  RETURN val;
END;
$$;