以JSONB为例,新增一张t_test表,表里包含主键、个人信息、工作经历三个字段,如下所示:
1查询年龄在20-25之间的记录
select * from t_test where (person_infomation_data->>'age')::int between 20 and 25
2查询老家为广州或深圳的记录
select * from t_test where (person_infomation_data->>'city') in ('广州','深圳')
3查询性别为女的记录
select * from t_test where (person_infomation_data->>'sex') = '女'
4查询兴趣爱好包括足球的记录
select * from t_test where (person_infomation_data->>'interest')::jsonb ? '足球'
5查询兴趣爱好为足球或者跳舞的记录
#写法一
select * from t_test where jsonb_exists_any((person_infomation_data->>'interest')::jsonb,array['足球','跳舞'])
#写法二
select * from t_test where (person_infomation_data->>'interest')::jsonb ?| array['足球','跳舞']
6查询兴趣爱好同时为足球篮球的记录
#写法一
select * from t_test where jsonb_exists_all((person_infomation_data->>'interest')::jsonb,array['足球','篮球'])
#写法二
select * from t_test where (person_infomation_data->>'interest')::jsonb ?& array['足球','篮球']
7查询兴趣爱好为多个的记录
select * from t_test where jsonb_array_length((person_infomation_data->>'interest')::jsonb) > 1
8查询在甲公司任职过的记录
select * from t_test where (select count(*) from jsonb_to_recordset(work_experience_data) as jsonbset(company text)where company = '甲') > 0
9查询换过岗位的记录
select * from t_test where (select count(distinct(position)) from jsonb_to_recordset(work_experience_data) as jsonbset(position text)) > 1