PgSQL常用语句

查询Json类型数组内实体某元素的值

bank_accounts ------>   [{"name": "永隆银行", "cardno": "555555555555", "status": "正常"}]

mybatis:
select * from xxx
where  bank_accounts @> '[{"cardno":"${item.baAccount}"}]'

pgsql：
select * from xxx
where bank_accounts @> '[{"cardno":"555"}]'

 

模糊查询Json类型数组内实体某元素的值

mybatis:
select * from xxx
where  
 EXISTS ( SELECT * FROM jsonb_array_elements ( bank_accounts ) T WHERE T ->> 'cardno' LIKE'%' ||#{item.accountOrCardno}'%' ) 


pgsql：
select * from xxx
where 
 EXISTS ( SELECT * FROM jsonb_array_elements ( bank_accounts ) T WHERE T ->> 'cardno' LIKE'%' || '123333' || '%' )