FREE AI-Enhanced Online PostgreSQL Compiler

Untitled*

You must execute the code again to save those values in the database.

SELECT version();
CREATE OR REPLACE FUNCTION mt_jsonb_fix_null_parent_fixed(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 IS NULL THEN
            retval = jsonb_set(retval, dst_path_segment, '{}'::jsonb, TRUE);
        END IF;
        i = i + 1;
    END LOOP;
    RETURN retval;
END;
$function$;

SELECT mt_jsonb_fix_null_parent_fixed('{"a": {}}'::jsonb, ARRAY['a', 'b', 'x', 'y', 'z']) AS FIXED_RESULT;

CREATE OR REPLACE FUNCTION 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$;
SELECT mt_jsonb_fix_null_parent('{"a": {}}'::jsonb, ARRAY['a', 'b', 'x', 'y', 'z']) AS CURRENT_RESULT;

version
PostgreSQL 14.17 (Debian 14.17-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

fixed_result
{"a": {"b": {"x": {"y": {}}}}}

current_result
{"a": {}}

CREATE FUNCTION

CREATE FUNCTION