以pg_stat_statements为例

编译扩展

cd /usr/local/pgsql/contrib/pg_stat_statements/
make && make install

启用扩展

切换为postgres用户sudo su - postgres，运行sql命令行psql，启用扩展

CREATE EXTENSION pg_stat_statements;

修改postgresql.conf

进入宝塔面板，修改postgres配置

shared_preload_libraries= 'pg_stat_statements'

重启postgresql

根据字段值查找

SELECT t.*
FROM table_name t
WHERE json_field->>'type'='5' and  json_field->>'value'!=''

查找并替换

update
table_name d
set cover_content=(cover_content::jsonb || jsonb_build_object('cover_img_id',((select f.id  from file f WHERE  f.path = SUBSTRING(cover_content->>'value',44))  limit 1)::text)::jsonb)
where cover_content->>'type'='5' and  cover_content->>'value'!='' and cover_content->>'cover_img_id' is null;

更新嵌套对象的键值：

如果 jsonb 字段包含嵌套对象，例如：

'{"user": {"name": "Alice", "age": 30}}'

你想更新 user 对象中的 age 为 35，可以使用：

UPDATE your_table
SET jsonb_column = jsonb_set(jsonb_column, '{user,age}', '"35"');

数组里面的字段查找

字段里面存的是数组，数组里面存的对象，可以通过以下语句直接查询对象属性的值

select *
    from cart,
jsonb_array_elements(data::jsonb) as elem where ( elem->>'cover_img_id' is null or  elem->>'cover_img_id'='') order by created_at desc;

数组字段查找并替换

update cart set data= (
  SELECT jsonb_agg(
    CASE
      WHEN element->>'cover_img_url' <> '' THEN jsonb_set(element, '{cover_img_id}',
          ((((select f.id  from file f WHERE  f.path = SUBSTRING(element->>'cover_img_url',44))  limit 1)::text)::jsonb)
          )
      ELSE element
    END
  )
  FROM jsonb_array_elements(data::jsonb) AS element
)
where id in (
    select id
    from cart,
jsonb_array_elements(data::jsonb) as elem where elem->>'cover_img_url' <> '' and ( elem->>'cover_img_id' is null or  elem->>'cover_img_id'='') order by created_at desc
    );

多级数组查找替换

select id                                as cart_id,
       cart_user_id                      as user_id,
       elem ->> 'id'           as file_id,
       (updated_at + interval '30 days') as expired_at
from cart,
     jsonb_array_elements(data::jsonb) as elem
limit 10;

update file f
set expired_at=t.expired_at
from (select id                                as cart_id,
       cart_user_id                      as user_id,
       (elem ->> 'id')::int           as file_id,
       (updated_at + interval '30 days') as expired_at
from cart,
     jsonb_array_elements(data::jsonb) as elem)
         as t
where f.id = t.file_id
  and f.expired_at < t.expired_at;