jsonb数组中查询某个key的值、根据表a中的某字段批量更新表b的字段

一、创建中间表（核心）

create table dev_mod_inventory_manage.instance_collection_name as 
    (select id,
        field225subfields
    from (select id,
              (jsonb_array_elements(field225 -> 'subfields')):: jsonb ->> 'a' field225subfields
        from (select id,
                    jsonb_array_elements(aa.jsonb -> 'content' -> 'fields') -> '225' field225
             from (select mri.id,
                          mri.jsonb
                   from dev_mod_inventory_manage.marc_records_info mri
                            left join dev_mod_inventory_manage.instance_info ii
                                      on mri.id = ii.id
                   where ii.collection_name is null
                     and length((mri.jsonb -> 'content' -> 'fields')::text) -
                         length(replace((mri.jsonb -> 'content' -> 'fields')::text, '"225"', '')) >
                         0) aa) bb
       where bb.field225 is not null) cc
    where cc.field225subfields is not null
   and length(trim(cc.field225subfields)) > 0);

二、更新前结果查询

三、更新

update dev_mod_inventory_manage.instance_info ii
set collection_name = icn.field225subfields
from dev_mod_inventory_manage.instance_collection_name icn
where ii.id = icn.id;

四、更新后查询对比

select icn.*,ii.collection_name from dev_mod_inventory_manage.instance_collection_name icn
left join dev_mod_inventory_manage.instance_info ii
on icn.id = ii.id;

本例可以借鉴的点在于：

1. 从jsonb中的json数组中查到某个字段
2. 更新a表中的某个字段到b表的某个字段