PostgresSQL jsonb类型字段查询使用整理

jsonb查询

• select * from sec_role where attributes::jsonb ->>‘job_id’ is not null;
• select count(*) from ci where type = ‘40bca208-5184-11ea-887d-cb65bdaac481’ and attributes ->> ‘clock_date’ = ‘2020-02-28’

jsonb更新

• update sec_user set attributes = attributes || ‘{“mobile”:“18831465421”}’ where account=‘20194010337’;

• update sec_function set attributes =
  jsonb_set(attributes,’{is_group}’,‘false’) where url !=’’ and
  function_type = ‘0’;

• update sec_department set type = ‘’ where attributes->>‘whistle_dept_type’ = ‘1’;