PostgreSQL-JSON使用

数据字段payload，类型json
{"extInfo":{"deviceCode":"10022004a0bd1d6cdd90","deviceName":"人脸抓拍机设备","deviceIP":"xxxx.xxxx.xxxx.xxxx","picUri":"XXXX/Mdd/D7/F3/CmVGWF6WVnqAbtExAAKDFYdD4yM262.jpg","cameraCode":"1111a0bd1d6cdd900001","cameraName":"IPC","faceDatabaseId":""}}
查询json结构数据：
select payload::json->'extInfo'->'deviceCode' from vgc.vgc_event_log l;
select '[1,2,3]'::json->2;
select '{"a":1,"b":2}'::json->'b';
select '{"a":1,"b":2}'::json->>'b';
select event_body from xx.yyyyyy  where delete_flag = 1 and event_body::json->>'address' = '过滤地址' ;

select l.start_time,l.create_time,l.* from vgc.vgc_event_log l where delete_flag = 1 and (l.payload::json#>>'{extInfo,userType}')::text = '7' ;
select * from vgc.vgc_event_log l where delete_flag = 1 and (l.payload::json#>>'{extInfo,facePicId}')::text = 'group1/M00/55/05/CmVG9lw1lAuAfjJnAABkK3wJuhA177.jpg' order by l.create_time desc ;
select payload::json->'extInfo'->'userType' from vgc.vgc_event_log where (payload::json#>>'{extInfo,userType}')::text = '1';
select payload::json#>>'{extInfo,userType}' from vgc.vgc_event_log where (payload::json#>>'{extInfo,userType}')::text = '1';

[{"houseId": "67e7c8bc9ec14497a238a3d794f3ea82", "houseAddr": "凤凰花园2栋1期1单元0层-104"}, {"houseId": "fa9cd6719c7645d4adc49ab3382c8d94", "houseAddr": "凤凰花园2栋1期2单元1层-廉租房"}]
select * from lcc.ladder_house_rel dd where dd.link_device_id = '10082016080070FE0222' and
(select count(*) from jsonb_array_elements(dd.house_info) as jae where jae::json->>'houseId' in ('67e7c8bc9ec14497a238a3d794f3ea82','77e7c8bc9ec14497a238a3d794f3ea82')) > 0 ;

数据字段daily_schedule，类型text
{"playSpan":[{"beginTime":"20200515T092658+08","endTime":"20200515T102458+08","id":1,"programNo":16}]}
SELECT date_part('second',((daily_schedule :: json->'playSpan'->0->'beginTime')::text::timestamp - '2020-05-14 08:47:35'::timestamp))
FROM id.id_schedule ss WHERE ss.delete_flag = 1 AND ss.uuid = '8b537d2c8d164e488572fb14c372cded';

SELECT * FROM id.id_schedule ss WHERE ss.delete_flag = 1 AND ss.uuid = '8b537d2c8d164e488572fb14c372cded'
and (daily_schedule :: json->'playSpan'->0->'beginTime')::text::timestamp::date - '2020-05-14 08:47:35'::timestamp::date> 0;

SELECT * FROM id.id_schedule ss WHERE ss.delete_flag = 1 AND ss.uuid = '8b537d2c8d164e488572fb14c372cded'
and date_part('second',((daily_schedule :: json->'playSpan'->0->'beginTime')::text::timestamp - '2020-05-14 08:47:35'::timestamp)) > 0;

SELECT * FROM id.id_schedule ss WHERE ss.delete_flag = 1 AND ss.uuid = '8b537d2c8d164e488572fb14c372cded'
and date_part('second',('2020-05-15 11:47:35'::timestamp - (daily_schedule :: json->'playSpan'->0->'beginTime')::text::timestamp)) > 0;

SELECT * FROM id.id_schedule ss WHERE ss.delete_flag = 1 AND ss.uuid = '8b537d2c8d164e488572fb14c372cded'
and (date_part('second',((daily_schedule :: json->'playSpan'->0->'beginTime')::text::timestamp - '2020-05-14 08:47:35'::timestamp)) > 0 or date_part('second',('2020-05-15 11:47:35'::timestamp - (daily_schedule :: json->'playSpan'->0->endTime)::text::timestamp)) > 0);

json结构带数据处理方式
[{"dayOfWeek":"monday","id":1,"playSpan":[{"beginTime":"20190321T165925+08","endTime":"20190321T175925+08","id":1,"programNo":1},{"beginTime":"20190321T205925+08","endTime":"20190321T215925+08","id":1,"programNo":1}]},{"dayOfWeek":"tuesday","id":2,"playSpan":[{"beginTime":"20190321T170019+08","endTime":"20190321T180019+08","id":2,"programNo":1}]},{"dayOfWeek":"thursday","id":3,"playSpan":[{"beginTime":"20190321T165926+08","endTime":"20190321T175926+08","id":3,"programNo":1}]},{"dayOfWeek":"friday","id":4,"playSpan":[{"beginTime":"20190321T165927+08","endTime":"20190321T175927+08","id":4,"programNo":1}]},{"dayOfWeek":"sunday","id":5,"playSpan":[{"beginTime":"20190321T165928+08","endTime":"20190321T175928+08","id":5,"programNo":1}]}]

select * from (select ss.uuid as uuid,ss.schedule_name as schedule_name,json_array_elements_text(json_array_elements_text(weekly_schedule::json)::json->'playSpan') as json_weektime FROM id.id_schedule ss WHERE ss.delete_flag = 0 AND ss.uuid = 'd180758672b64057b86a4780a3a88bbc')aa where
date_part('second',((aa.json_weektime :: json->'beginTime')::text::timestamp - '2019-03-21 18:47:35'::timestamp)) > 0 ;

select * from id.id_schedule ids where ids.uuid in (select distinct uuid from (select ss.uuid as uuid,ss.schedule_name as schedule_name,json_array_elements_text(json_array_elements_text(weekly_schedule::json)::json->'playSpan') as json_weektime FROM id.id_schedule ss WHERE ss.delete_flag = 0 AND ss.uuid = 'd180758672b64057b86a4780a3a88bbc')aa where
date_part('second',((aa.json_weektime :: json->'endTime')::text::timestamp::time - '2019-03-21 10:47:35'::timestamp::time)) > 0
and date_part('second',('2019-03-21 20:47:35'::timestamp::time - (aa.json_weektime :: json->'beginTime')::text::timestamp::time)) > 0);

 

字段类型jsonb使用

字段数据值：

[{"houseId": "b8bbe3013b1e45949a0f5dd341e7f722", "houseAddr": "微信测试项目（一）一期01栋1单元11层-1103"}, {"houseId": "a132c894cb5743bba7e710858f47bb5f", "houseAddr": "微信测试项目（一）一期01栋1单元5层-0507"}, {"houseId": "bf7618c991954c0bbf448eed42766ac4", "houseAddr": "微信测试项目（一）一期01栋1单元0层-1104"}, {"houseId": "6a21852b437742d1a22b18584b7265df", "houseAddr": "微信测试项目（一）一期01栋1单元11层-1104"}]

数据库SQL：

单个按jsonb里面的字段过滤：

select * from xxx.yyyyyy where house_info @> ('1111111111111111111111111111')::jsonb ;

按jsonb里面的字段in过滤：

select * from xxx.yyyyyy where (select count(*) from jsonb_array_elements(house_info) as jae where jae::json->>'houseId' in ('xxxxxxxxxxxxxxxxxxxx','yyyyyyyyyyyyyyyy') > 0 ;

Mybaties：

select * from xxx.yyyyyy where house_info @> (#{houseId,jdbcType=VARCHAR})::jsonb ;

select * from xxx.yyyyyy where (select count(*) from jsonb_array_elements(house_info) as jae where jae::json->>'houseId' in 

<foreach collection="houseList" item="item" index="index" open="(" close=")" separator=",">
#{item,jdbcType=VARCHAR}
</foreach>) > 0;