utility_functions.sql

CREATE OR REPLACE FUNCTION public.json_append(data JSON, insert_data JSON)
  RETURNS JSON
LANGUAGE SQL
AS $$
SELECT
  ('{' || string_agg(to_json(key) || ':' || value, ',') || '}') :: JSON
FROM (
       SELECT
         *
       FROM json_each(data)
       UNION ALL
       SELECT
         *
       FROM json_each(insert_data)
     ) t;
$$;

CREATE OR REPLACE FUNCTION public.json_delete(data JSON, keys TEXT [])
  RETURNS JSON
LANGUAGE SQL
AS $$
SELECT
  ('{' || string_agg(to_json(key) || ':' || value, ',') || '}') :: JSON
FROM (
       SELECT
         *
       FROM json_each(data)
       WHERE key <> ALL (keys)
     ) t;
$$;

CREATE OR REPLACE FUNCTION public.json_merge(data JSON, merge_data JSON)
  RETURNS JSON
LANGUAGE SQL
AS $$
SELECT
  ('{' || string_agg(to_json(key) || ':' || value, ',') || '}') :: JSON
FROM (
       WITH to_merge AS (
           SELECT
             *
           FROM json_each(merge_data)
       )
       SELECT
         *
       FROM json_each(data)
       WHERE key NOT IN (SELECT
                           key
                         FROM to_merge)
       UNION ALL
       SELECT
         *
       FROM to_merge
     ) t;
$$;

CREATE OR REPLACE FUNCTION public.json_update(data JSON, update_data JSON)
  RETURNS JSON
LANGUAGE SQL
AS $$
SELECT
  ('{' || string_agg(to_json(key) || ':' || value, ',') || '}') :: JSON
FROM (
       WITH old_data AS (
           SELECT
             *
           FROM json_each(data)
       ), to_update AS (
           SELECT
             *
           FROM json_each(update_data)
           WHERE key IN (SELECT
                           key
                         FROM old_data)
       )
       SELECT
         *
       FROM old_data
       WHERE key NOT IN (SELECT
                           key
                         FROM to_update)
       UNION ALL
       SELECT
         *
       FROM to_update
     ) t;
$$;

--remove empty or null from the fields of a json object
CREATE OR REPLACE FUNCTION public.json_clean(data JSON)
  RETURNS JSON
LANGUAGE SQL
AS $$
SELECT
  ('{' || string_agg(to_json(key) || ':' || value, ',') || '}') :: JSON
FROM (
       WITH to_clean AS (
           SELECT
             *
           FROM json_each(data)
       )
       SELECT
         *
       FROM json_each(data)
       WHERE cast(value AS TEXT) != 'null' AND cast(value AS TEXT) != ''
     ) t;
$$;

--remove nulls or empties from a json array
CREATE OR REPLACE FUNCTION public.json_clean_array(data JSON)
  RETURNS JSON
LANGUAGE SQL
AS $$
SELECT
  array_to_json(array_agg(value)) :: JSON
FROM (
       SELECT
         value
       FROM json_array_elements(data)
       WHERE cast(value AS TEXT) != 'null' AND cast(value AS TEXT) != ''
     ) t;
$$;