查询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' || '%' )