SQL to Update/Change URLs on STRAPI database (on media upload storage change)

update_strapi_database_replacing_upload_url.sql

-- Define the constants. Update old_url_prefix and new_url_prefix.
DO $$ 
DECLARE 
    old_url_prefix text := 'https://oldblob.blob.core.windows.net/media/assets/';
    new_url_prefix text := 'https://newuploads.blob.core.windows.net/uploads/';
BEGIN 
    -- Update the 'url' column
    UPDATE public.files
    SET url = REPLACE(url, old_url_prefix, new_url_prefix)
    WHERE url IS NOT NULL;

    -- Update the URLs within the 'formats' JSON column
    UPDATE public.files
    SET formats = jsonb_set(
        formats,
        '{large,url}',
        ('"' || REPLACE(formats->'large'->>'url', old_url_prefix, new_url_prefix) || '"')::jsonb
    ) WHERE formats->'large'->>'url' IS NOT NULL;

    UPDATE public.files
    SET formats = jsonb_set(
        formats,
        '{small,url}',
        ('"' || REPLACE(formats->'small'->>'url', old_url_prefix, new_url_prefix) || '"')::jsonb
    ) WHERE formats->'small'->>'url' IS NOT NULL;

    UPDATE public.files
    SET formats = jsonb_set(
        formats,
        '{medium,url}',
        ('"' || REPLACE(formats->'medium'->>'url', old_url_prefix, new_url_prefix) || '"')::jsonb
    ) WHERE formats->'medium'->>'url' IS NOT NULL;

    UPDATE public.files
    SET formats = jsonb_set(
        formats,
        '{thumbnail,url}',
        ('"' || REPLACE(formats->'thumbnail'->>'url', old_url_prefix, new_url_prefix) || '"')::jsonb
    ) WHERE formats->'thumbnail'->>'url' IS NOT NULL;
END $$;