【postgresql】json数组进行查找

1.查找

json数组数据结构

["无异常"]
["空指针","线程异常","类型不匹配","转换异常"]

统计有异常总数

SELECT 
     sum(JSONB_ARRAY_LENGTH(check_result::jsonb)),
     sum(case WHEN check_result::jsonb @> '["无异常"]' THEN JSONB_ARRAY_LENGTH(check_result::jsonb) else 0 END)
FROM 
  business.nuctech_ray_machine 
where 
check_time BETWEEN  '2020-08-05 00:00:00' AND '2020-08-05 23:59:59';

分组统计各个异常出现次数

SELECT 
   count(1),
   wuping
FROM (
   SELECT
			json_array_elements_text(check_result :: json) AS wuping
		FROM
			business.nuctech_ray_machine
	 WHERE check_time BETWEEN  '2020-08-05 00:00:00' AND '2020-08-05 23:59:59'
 ) 
tmp GROUP BY wuping;

查询指定异常

SELECT 
  * 
FROM 
     business.nuctech_ray_machine 
where 
    check_time BETWEEN  '2020-07-01 00:00:00' AND '2020-07-28 23:59:59'
AND check_result::jsonb @> '["无异常"]';

参考： postgresql官方json操作文档