Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table media_items (id varchar, asset jsonb);
insert into media_items values ('1fsh0n0ri0', '{"key":"2021/1/29/04aaf6f1-bf13-4918-a062-355bdf192d2f.jpeg","url":"https://my.old.url/api/v1/images/4166289-f-542-d-445-b-a-0-d-1-ec-7916-c-0748-e.jpeg?id=MQkvMjAyMi8wMS8yMC80MTY2Mjg5Zi01NDJkLTQ0NWItYTBkMS1lYzc5MTZjMDc0OGUuanBlZwk4OTY%3D","size":32996,"width":580,"height":371,"storage":"$project","filename":"bigstock-concept-of-UK-EU-separation-after-brexit-257595847-580x371.jpg","mimeType":"image/jpeg"}');
insert into media_items values ('1f73vuahm0', '{"key":"2021/2/8/2fe50c7b-d5b1-4a4f-b9f8-6b1931167c25.png","url":"https://my.old.url/api/v1/images/covid-19-m-rna-vaccines.png?id=MQkvMjAyMS82LzEvZWI2YzQwZjItMjYzMS00MTY4LWFhZGQtNjZjMmVkOTg5ZDQ2LnBuZwk4MTQ=","size":1246735,"width":1463,"height":1500,"storage":"$project","filename":"The nuclear fuel cycle.png","mimeType":"image/png"}');

create table document_revisions (id varchar, data jsonb);
insertinto document_revisions values ('xx001', '{"design":{"name":"p:814:794","version":"50.0.0"},"layout":"storyidea","content":[{"identifier":"p:814:794.header","id":"doc-1f73vrdo40","content":{"title":"Triumph of a new drug class","summary":"Fresh from success in the battle with COVID-19, mRNA technology is shaping drug discovery of the future."}},{"identifier":"p:814:794.paragraph","id":"doc-1f73vrdo50","content":{"text":"The coronavirus pandemic ... is poised for approval."}},{"identifier":"p:814:794.image","id":"doc-1f73vuahm0","content":{"img":{"originalUrl":"https://my.old.url/api/v1/images/covid-19-m-rna-vaccines.png?id=MQkvMjAyMS82LzEvZWI2YzQwZjItMjYzMS00MTY4LWFhZGQtNjZjMmVkOTg5ZDQ2LnBuZwk4MTQ=","url":"https://my.old.url/api/v1/images/covid-19-m-rna-vaccines.png?id=MQkvMjAyMS82LzEvZWI2YzQwZjItMjYzMS00MTY4LWFhZGQtNjZjMmVkOTg5ZDQ2LnBuZwk4MTQ%3D&auto=format","mediaId":"3nO-shKnBV18","width":1390,"height":341,"mimeType":"image/png","imageService":"liImageProxy"},"caption":"COVID-19 mRNA vaccines","source":"Source: Some Source"}},{"identifier":"p:814:794.paragraph","id":"doc-1f740ah2m0","content":{"text":"Vaccination delivers ... this coronavirus."}},{"identifier":"p:896:876.imageCarousel","id":"doc-1fsh0mvvd0","containers":{"carousel-content":[{"identifier":"p:896:876.image","id":"doc-1fsh0n0ri0","content":{"img":{"originalUrl":"https://my.old.url/api/v1/images/4166289-f-542-d-445-b-a-0-d-1-ec-7916-c-0748-e.jpeg?id=MQkvMjAyMi8wMS8yMC80MTY2Mjg5Zi01NDJkLTQ0NWItYTBkMS1lYzc5MTZjMDc0OGUuanBlZwk4OTY=","url":"https://my.old.url/api/v1/images/4166289-f-542-d-445-b-a-0-d-1-ec-7916-c-0748-e.jpeg?id=MQkvMjAyMi8wMS8yMC80MTY2Mjg5Zi01NDJkLTQ0NWItYTBkMS1lYzc5MTZjMDc0OGUuanBlZwk4OTY%3D&auto=format","mediaId":"HJnwklc2WD37","width":4000,"height":2667,"mimeType":"image/jpeg","imageService":"liImageProxy"},"caption":"Globe","source":"medial library"}},{"identifier":"p:896:876.image","id":"doc-1fsh0pcne0","content":{"img":{"originalUrl":"https://my.old.url/api/v1/images/f-018-cbfd-48-dc-4-f-58-9-d-8-f-5-e-1-a-3-a-500238.jpeg?id=MQkvMjAyMi8wMS8xOC9mMDE4Y2JmZC00OGRjLTRmNTgtOWQ4Zi01ZTFhM2E1MDAyMzguanBlZwk4OTY=","url":"https://my.old.url/api/v1/images/f-018-cbf

CREATE TABLE



INSERT 0 1



INSERT 0 1



CREATE TABLE



INSERT 0 1


-- perform updates in media_items
update media_items set asset = asset || jsonb_build_object('new_url', 'https://my.new.url/'||((asset -> 'key')#>>'{}'));

UPDATE 2


select jsonb_pretty(asset) from media_items


jsonb_pretty


{
    "key": "2021/1/29/04aaf6f1-bf13-4918-a062-355bdf192d2f.jpeg",
    "url": "https://my.old.url/api/v1/images/4166289-f-542-d-445-b-a-0-d-1-ec-7916-c-0748-e.jpeg?id=MQkvMjAyMi8wMS8yMC80MTY2Mjg5Zi01NDJkLTQ0NWItYTBkMS1lYzc5MTZjMDc0OGUuanBlZwk4OTY%3D",
    "size": 32996,
    "width": 580,
    "height": 371,
    "new_url": "https://my.new.url/2021/1/29/04aaf6f1-bf13-4918-a062-355bdf192d2f.jpeg",
    "storage": "$project",
    "filename": "bigstock-concept-of-UK-EU-separation-after-brexit-257595847-580x371.jpg",
    "mimeType": "image/jpeg"
}

{
    "key": "2021/2/8/2fe50c7b-d5b1-4a4f-b9f8-6b1931167c25.png",
    "url": "https://my.old.url/api/v1/images/covid-19-m-rna-vaccines.png?id=MQkvMjAyMS82LzEvZWI2YzQwZjItMjYzMS00MTY4LWFhZGQtNjZjMmVkOTg5ZDQ2LnBuZwk4MTQ=",
    "size": 1246735,
    "width": 1463,
    "height": 1500,
    "new_url": "https://my.new.url/2021/2/8/2fe50c7b-d5b1-4a4f-b9f8-6b1931167c25.png",
    "storage": "$project",
    "filename": "The nuclear fuel cycle.png",
    "mimeType": "image/png"
}



SELECT 2


create or replace function document_paths() returns table(id text, path text, arr_js jsonb, obj_js jsonb)
language sql
as $$
  with recursive cte(id, path, arr_js, obj_js) as (
     select d.id, '', case when jsonb_typeof(d.data) = 'array' then d.data 
                  else '[null]'::jsonb end, 
            case when jsonb_typeof(d.data) = 'object' then d.data 
              else '{"null":null}'::jsonb end
     from document_revisions d
     union all
     select c.id, c.path ||(case when c.path != '' then ',' else '' end)||(case when arr_js.elem#>>'{}' is null then obj_js.key else arr_js.i::text end), 
        case when arr_js.elem#>>'{}' is null then 
           (case when jsonb_typeof(obj_js.value) = 'array' then obj_js.value 
                 else '[null]'::jsonb end) else 
                 (case when jsonb_typeof(arr_js.elem) = 'object' then '[null]'::jsonb when jsonb_typeof(arr_js.elem) = 'array' then arr_js.elem else '[]'::jsonb end) end,
         case when obj_js.value#>>'{}' is null then 
           (case when jsonb_typeof(arr_js.elem) = 'object' then arr_js.elem 
                 else '{"null":null}'::jsonb end) else 
                 (case when jsonb_typeof(obj_js.value) = 'object' then obj_js.value 
                       else (case when jsonb_typeof(obj_js.value) = 'array' then '{"null":null}'::jsonb 
                               else '{}'::jsonb end) end) end
     from cte c 
     cross join lateral (select row_number() over (order by 1) - 1 i, t.value elem 
                 from jsonb_array_elements(c.arr_js) t) arr_js 
     cross join jsonb_each(c.obj_js) obj_js
  )
  select * from cte
$$

CREATE FUNCTION


create or replace function run_updates()
  returns void as
$body$
declare
    c record;
begin
  for c in select * from document_paths() -- loop over each path
  loop
    if c.obj_js = '{}'::jsonb and (c.path ~ 'url' or c.path ~ 'originalUrl') then -- check if path is pointing to a url or originalUrl
      -- perform update
      update document_revisions set data = jsonb_set(data, ('{'||c.path||'}')::text[], m.asset -> 'new_url') 
      from media_items m where document_revisions.id = c.id and 
        ((data::jsonb#>('{'||c.path||'}')::text[])#>>'{}') like ((m.asset -> 'url')#>>'{}')||'%';
    end if;
  end loop;
end
$body$
language plpgsql

CREATE FUNCTION


select run_updates()


run_updates






SELECT 1


select jsonb_pretty(data) from document_revisions;


jsonb_pretty


{
    "design": {
        "name": "p:814:794",
        "version": "50.0.0"
    },
    "layout": "storyidea",
    "content": [
        {
            "id": "doc-1f73vrdo40",
            "content": {
                "title": "Triumph of a new drug class",
                "summary": "Fresh from success in the battle with COVID-19, mRNA technology is shaping drug discovery of the future."
            },
            "identifier": "p:814:794.header"
        },
        {
            "id": "doc-1f73vrdo50",
            "content": {
                "text": "The coronavirus pandemic ... is poised for approval."
            },
            "identifier": "p:814:794.paragraph"
        },
        {
            "id": "doc-1f73vuahm0",
            "content": {
                "img": {
                    "url": "https://my.old.url/api/v1/images/covid-19-m-rna-vaccines.png?id=MQkvMjAyMS82LzEvZWI2YzQwZjItMjYzMS00MTY4LWFhZGQtNjZjMmVkOTg5ZDQ2LnBuZwk4MTQ%3D&auto=format",
                    "width": 1390,
                    "height": 341,
                    "mediaId": "3nO-shKnBV18",
                    "mimeType": "image/png",
                    "originalUrl": "https://my.new.url/2021/2/8/2fe50c7b-d5b1-4a4f-b9f8-6b1931167c25.png",
                    "imageService": "liImageProxy"
                },
                "source": "Source: Some Source",
                "caption": "COVID-19 mRNA vaccines"
            },
            "identifier": "p:814:794.image"
        },
        {
            "id": "doc-1f740ah2m0",
            "content": {
                "text": "Vaccination delivers ... this coronavirus."
            },
            "identifier": "p:814:794.paragraph"
        },
        {
            "id": "doc-1fsh0mvvd0",
            "containers": {
                "carousel-content": [
                    {
                        "id": "doc-1fsh0n0ri0",
                        "content": {
                            "img": {
                                "url": "https://my.new.url/2021/1/29/04aaf6f1-bf13-4918-a062-355bdf192d2f.jpeg",
                                "width": 4000,
                                "height": 2667,
                                "mediaId": "HJnwklc2WD37",
                                "mimeType": "image/jpeg",
                                "originalUrl": "https://my.old.url/api/v1/images/4166289-f-542-d-445-b-a-0-d-1-ec-7916-c-0748-e.jpeg?id=MQkvMjAyMi8wMS8yMC80MTY2Mjg5Zi01NDJkLTQ0NWItYTBkMS1lYzc5MTZjMDc0OGUuanBlZwk4OTY=",
                                "imageService": "liImageProxy"
                            },
                            "source": "medial library",
                            "caption": "Globe"
                        },
                        "identifier": "p:896:876.image"
                    },
                    {
                        "id": "doc-1fsh0pcne0",
                        "content": {
                            "img": {
                                "url": "https://my.old.url/api/v1/images/f-018-cbfd-48-dc-4-f-58-9-d-8-f-5-e-1-a-3-a-500238.jpeg?id=MQkvMjAyMi8wMS8xOC9mMDE4Y2JmZC00OGRjLTRmNTgtOWQ4Zi01ZTFhM2E1MDAyMzguanBlZwk4OTY%3D&auto=format",
                                "width": 4000,
                                "height": 2662,
                                "mediaId": "wk3oozI90L9z",
                                "mimeType": "image/jpeg",
                                "originalUrl": "https://my.old.url/api/v1/images/f-018-cbfd-48-dc-4-f-58-9-d-8-f-5-e-1-a-3-a-500238.jpeg?id=MQkvMjAyMi8wMS8xOC9mMDE4Y2JmZC00OGRjLTRmNTgtOWQ4Zi01ZTFhM2E1MDAyMzguanBlZwk4OTY=",
                                "imageService": "liImageProxy"
                            },
                            "source": "media library",
                            "caption": "Euro"
                        },
                        "identifier": "p:896:876.image"
                    },
                    {
                        "id": "doc-1fsis76qo0",
                        "content": {
                            "img": {
                                "url": "https://my.old.url/api/v1/images/stock-vector-black-justice-scale-icon-on-white-background-255865786.jpg?id=MQkvMjAyMi8wMS8yNC80ZjY2YzNkYS1iYTZjLTRmYzQtODMyZi03MmQzMTRmN2EzNTMuanBlZwk4OTY%3D&auto=format",
                                "width": 1500,
                                "height": 1600,
                                "mediaId": "kluvb3oSdYgu",
                                "mimeType": "image/jpeg",
                                "originalUrl": "https://my.old.url/api/v1/images/stock-vector-black-justice-scale-icon-on-white-background-255865786.jpg?id=MQkvMjAyMi8wMS8yNC80ZjY2YzNkYS1iYTZjLTRmYzQtODMyZi03MmQzMTRmN2EzNTMuanBlZwk4OTY=",
                                "imageService": "liImageProxy"
                            }
                        },
                        "identifier": "p:896:876.image"
                    }
                ]
            },
            "identifier": "p:896:876.imageCarousel"
        },
        {
            "id": "doc-1f74117rl0",
            "content": {
                "img": {
                    "url": "https://my.old.url/api/v1/images/antibodies-highly-effective-in-treating-cancer.png?id=MQkvMjAyMS8zLzgvM2UyMzY1NzMtMTkyMi00NDY2LTk5NDUtMDIyZTExZDcwN2M5LnBuZwk4MTQ%3D&auto=format",
                    "width": 512,
                    "height": 321,
                    "mediaId": "UrQv7AWisNIM",
                    "mimeType": "image/png",
                    "originalUrl": "https://my.old.url/api/v1/images/antibodies-highly-effective-in-treating-cancer.png?id=MQkvMjAyMS8zLzgvM2UyMzY1NzMtMTkyMi00NDY2LTk5NDUtMDIyZTExZDcwN2M5LnBuZwk4MTQ=",
                    "imageService": "liImageProxy"
                },
                "title": "FDA shifts into a lower gear"
            },
            "containers": {
                "text-column": [
                    {
                        "id": "doc-1f74118dv0",
                        "content": {
                            "text": "Its been about four months ... FDA and soon."
                        },
                        "identifier": "p:814:794.paragraph"
                    }
                ]
            },
            "identifier": "p:814:794.imageRightContainer"
        },
        {
            "id": "doc-1f7417mu00",
            "content": {
                "img": {
                    "url": "https://my.old.url/api/v1/images/new-wave-of-ip-os-in-china.png?id=MQkvMjAyMS8zLzgvMGJlOWI4NTItMWIxMC00ZGU2LTg2ZjEtNjVjM2ZhYmZkMjA1LnBuZwk4MTQ%3D&auto=format",
                    "width": 608,
                    "height": 608,
                    "mediaId": "RgHesGXLiXnD",
                    "mimeType": "image/png",
                    "originalUrl": "https://my.old.url/api/v1/images/new-wave-of-ip-os-in-china.png?id=MQkvMjAyMS8zLzgvMGJlOWI4NTItMWIxMC00ZGU2LTg2ZjEtNjVjM2ZhYmZkMjA1LnBuZwk4MTQ=",
                    "imageService": "liImageProxy"
                },
                "title": "Computer-designed drugs"
            },
            "containers": {
                "text-column": [
                    {
                        "id": "doc-1f7417ndp0",
                        "content": {
                            "text": "<strong>Artificial intelligence (AI) ... to treat fibrotic diseases."
                        },
                        "identifier": "p:814:794.paragraph"
                    }
                ]
            },
            "identifier": "p:814:794.imageLeftContainer"
        },
        {
            "id": "doc-1f741btpp0",
            "content": {
                "heading": "In Focus"
            },
            "identifier": "p:814:794.headings"
        },
        {
            "id": "doc-1fiemjb3s0",
            "content": {
                "linkbuttonurl": "http://my-aws.s3.amazonaws.com/2021/10/21/073e9d7f-bcac-43af-afad-5ede72e02235.pdf",
                "linkbuttonlabel": "Download Fact Sheet"
            },
            "identifier": "p:814:794.linkButton"
        },
        {
            "id": "doc-1f741c1i10",
            "content": {
                "text": "The coronavirus pandemic ... from an IPO transaction."
            },
            "identifier": "p:814:794.paragraph"
        },
        {
            "id": "doc-1fj0hd7ol0",
            "content": {
                "vid": {
                    "width": 1920,
                    "height": 1080,
                    "mediaId": "wieLmsyDoKE1",
                    "mimeType": "video/mp4",
                    "originalUrl": "https://my-aws.s3.amazonaws.com/2021/10/28/eb211707-dda7-408a-ab02-8e81e62dba9f.mp4",
                    "posterImageUrl": "https://my.old.url/api/v1/images/f-018-cbfd-48-dc-4-f-58-9-d-8-f-5-e-1-a-3-a-500238.jpeg?id=MQkvMjAyMi8wMS8xOC9mMDE4Y2JmZC00OGRjLTRmNTgtOWQ4Zi01ZTFhM2E1MDAyMzguanBlZwk4OTY%3D&rect=0%2C206%2C4000%2C2250&auto=format",
                    "durationSeconds": 411.811
                },
                "source": "Source",
                "caption": "Phoenix Memory Autocall on Luxury Basket in EUR, Illustration of key terms"
            },
            "identifier": "p:814:794.internalVideo"
        },
        {
            "id": "doc-1fk7rf82u0",
            "content": {
                "source": "thanks to Vimeo",
                "caption": "This is a vimeo video",
                "videourl": "https://vimeo.com/XXXXXXX",
                "videorender": {
                    "params": {
                        "videourl": "https://vimeo.com/XXXXX"
                    },
                    "service": "videorender"
                }
            },
            "identifier": "p:896:876.video"
        },
        {
            "id": "doc-1f741cpj70",
            "content": {
                "heading": "Investable healthcare themes of ..."
            },
            "identifier": "p:814:794.headings"
        },
        {
            "id": "doc-1fqb7iq3b0",
            "content": {
                "title": "Image Text Container - small image left, para aligns with image"
            },
            "containers": {
                "text-column": [
                    {
                        "id": "doc-1fqb7mb480",
                        "styles": {
                            "image-size": "S",
                            "image-horizontal-align": "image-align-left"
                        },
                        "content": {
                            "img": {
                                "url": "https://my.old.url/api/v1/images/929009-ee-cda-6-4227-83-e-4-80-fc-954730-b-6.jpeg?id=MQkvMjAyMi8wMS8xOC85MjkwMDllZS1jZGE2LTQyMjctODNlNC04MGZjOTU0NzMwYjYuanBlZwk4OTY%3D&auto=format",
                                "width": 4000,
                                "height": 2662,
                                "mediaId": "baTBW3XKKb7A",
                                "mimeType": "image/jpeg",
                                "originalUrl": "https://my.old.url/api/v1/images/929009-ee-cda-6-4227-83-e-4-80-fc-954730-b-6.jpeg?id=MQkvMjAyMi8wMS8xOC85MjkwMDllZS1jZGE2LTQyMjctODNlNC04MGZjOTU0NzMwYjYuanBlZwk4OTY=",
                                "imageService": "liImageProxy"
                            },
                            "source": "Source",
                            "caption": "Caption"
                        },
                        "identifier": "p:896:876.image"
                    },
                    {
                        "id": "doc-1fqb7ir7m0",
                        "content": {
                            "text": "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum."
                        },
                        "identifier": "p:896:876.paragraph"
                    }
                ]
            },
            "identifier": "p:896:876.blockContainer"
        },
        {
            "id": "doc-1f741dpqs0",
            "styles": {
                "table-size-selection": "short"
            },
            "content": {
                "everviztableid": "REDACTED",
                "everviztablecustomheight": "390px"
            },
            "identifier": "p:814:794.table"
        }
    ]
}



SELECT 1