SELECT count(*) num FROM (SELECT
app_user_device_data_time FROM `app_user_device_data` a LEFT JOIN app_user_device aud ON a.app_user_device_id = aud.app_user_device_id
WHERE aud.app_device_id = 14 AND a.app_user_device_data_time BETWEEN #{startTime} AND #{endTime}
AND
CASE WHEN
JSON_VALID(a.app_user_device_data)
THEN
JSON_EXTRACT( JSON_EXTRACT( app_user_device_data, '$.objectData' ), '$.yw' ) = '有烟雾'
取数据库中json类型 json_valid 判断存在不存在
存在则 json_EXTRACT 去解析json objectData
我这个是套用json需要解析两次
例子
1 {"gwid":"fc6bf0fffe6a7241","rssi":-40,"snr":9.25,"freq":470.9,"dr":5,"adr":true,"fCnt":13,"fPort":8,"confirmed":false,"data":"kVUCAAAGoKBMQBcSASECI/8lAAAAAAD///8ASaoAAAD///+qABD///+qAAAAAAAAAAD//6qqqozl","**objectData**":{"dataTime":1610444400000,"di":"06000002","phaseAVoltage":230.2,"phaseBVoltage":0.0,"phaseCVoltage":0.0,"phaseACurrent":0.025,"phaseBCurrent":0.0,"phaseCCurrent":0.0,"frequency":49.0,"totalActivePower":0.0,"phaseAActivePower":0.0,"phaseBActivePower":0.0,"phaseCActivePower":0.0,"totalReactivePower":0.0,"phaseAReactivePower":0.0,"phaseBReactivePower":0.0,"phaseCReactivePower":0.0,"totalPowerFactor":1.0,"phaseAPowerFactor":0.0,"phaseBPowerFactor":0.0,"phaseCPowerFactor":0.0,"totalForwardActiveEnergy":0.0,"totalReverseActiveEnergy":0.0,"combinedReactivePower1TotalElectricEnergy":0.0,"combinedReactivePower2TotalElectricEnergy":0.0,"firstQuadrantIdleElectricEnergy":null,"secondQuadrantIdleElectricEnergy":null,"thirdQuadrantIdleElectricEnergy":null,"fourthQuadrantIdleElectricEnergy":null,"currentIdleElectriceEnergy":null,"currentActiveElectriceEnergy":null},"class":"C"} 2021-09-06 17:49:27 10 0 16
2 {"gwid":"fc6bf0fffe6a7241","rssi":-35,"snr":10.75,"freq":470.7,"dr":0,"adr":true,"fCnt":1,"fPort":8,"confirmed":false,"data":"kQoCBAAEAQAAAAAA","objectData":{"dataTime":null,"di":"04000402","phaseAVoltage":null,"phaseBVoltage":null,"phaseCVoltage":null,"phaseACurrent":null,"phaseBCurrent":null,"phaseCCurrent":null,"frequency":null,"totalActivePower":null,"phaseAActivePower":null,"phaseBActivePower":null,"phaseCActivePower":null,"totalReactivePower":null,"phaseAReactivePower":null,"phaseBReactivePower":null,"phaseCReactivePower":null,"totalPowerFactor":null,"phaseAPowerFactor":null,"phaseBPowerFactor":null,"phaseCPowerFactor":null,"totalForwardActiveEnergy":null,"totalReverseActiveEnergy":null,"combinedReactivePower1TotalElectricEnergy":null,"combinedReactivePower2TotalElectricEnergy":null,"firstQuadrantIdleElectricEnergy":null,"secondQuadrantIdleElectricEnergy":null,"thirdQuadrantIdleElectricEnergy":null,"fourthQuadrantIdleElectricEnergy":null,"currentIdleElectriceEnergy":null,"currentActiveElectriceEnergy":null},"class":"C"} 2021-04-20 09:19:10 10 0 16
3 {"gwid":"fc6bf0fffe6a7241","rssi":-37,"snr":9.75,"freq":470.3,"dr":0,"adr":true,"fCnt":2,"fPort":8,"confirmed":false,"data":"kRoCAAEGABgSASEZI/8lAAAQAAD///8ASQ==","objectData":{"dataTime":1610445600000,"di":"06010002","phaseAVoltage":231.90001,"phaseBVoltage":0.0,"phaseCVoltage":0.0,"phaseACurrent":0.025,"phaseBCurrent":0.010000001,"phaseCCurrent":0.0,"frequency":49.0,"totalActivePower":null,"phaseAActivePower":null,"phaseBActivePower":null,"phaseCActivePower":null,"totalReactivePower":null,"phaseAReactivePower":null,"phaseBReactivePower":null,"phaseCReactivePower":null,"totalPowerFactor":null,"phaseAPowerFactor":null,"phaseBPowerFactor":null,"phaseCPowerFactor":null,"totalForwardActiveEnergy":null,"totalReverseActiveEnergy":null,"combinedReactivePower1TotalElectricEnergy":null,"combinedReactivePower2TotalElectricEnergy":null,"firstQuadrantIdleElectricEnergy":null,"secondQuadrantIdleElectricEnergy":null,"thirdQuadrantIdleElectricEnergy":null,"fourthQuadrantIdleElectricEnergy":null,"currentIdleElectriceEnergy":null,"currentActiveElectriceEnergy":null},"class":"C"} 2021-04-20 09:19:15 10 0 16
4 {"gwid":"fc6bf0fffe6a7241","rssi":-33,"snr":12.0,"freq":470.5,"dr":0,"adr":true,"fCnt":3,"fPort":8,"confirmed":false,"data":"kSECAAIGABgSASEFAAD///8=","objectData":{"dataTime":1610445600000,"di":"06020002","phaseAVoltage":null,"phaseBVoltage":null,"phaseCVoltage":null,"phaseACurrent":null,"phaseBCurrent":null,"phaseCCurrent":null,"frequency":null,"totalActivePower":4.9999997E-4,"phaseAActivePower":0.0,"phaseBActivePower":0.0,"phaseCActivePower":0.0,"totalReactivePower":0.0,"phaseAReactivePower":0.0,"phaseBReactivePower":0.0,"phaseCReactivePower":0.0,"totalPowerFactor":null,"phaseAPowerFactor":null,"phaseBPowerFactor":null,"phaseCPowerFactor":null,"totalForwardActiveEnergy":null,"totalReverseActiveEnergy":null,"combinedReactivePower1TotalElectricEnergy":null,"combinedReactivePower2TotalElectricEnergy":null,"firstQuadrantIdleElectricEnergy":null,"secondQuadrantIdleElectricEnergy":null,"thirdQuadrantIdleElectricEnergy":null,"fourthQuadrantIdleElectricEnergy":null,"currentIdleElectriceEnergy":null,"currentActiveElectriceEnergy":null},"class":"C"} 2021-04-20 09:19:23 10 0 16
5 {"gwid":"fc6bf0fffe6a7241","rssi":-33,"snr":9.25,"freq":471.7,"dr":0,"adr":true,"fCnt":4,"fPort":8,"confirmed":false,"data":"kRECAAMGABgSASEAEP///w==","objectData":{"dataTime":1610445600000,"di":"06030002","phaseAVoltage":null,"phaseBVoltage":null,"phaseCVoltage":null,"phaseACurrent":null,"phaseBCurrent":null,"phaseCCurrent":null,"frequency":null,"totalActivePower":null,"phaseAActivePower":null,"phaseBActivePower":null,"phaseCActivePower":null,"totalReactivePower":null,"phaseAReactivePower":null,"phaseBReactivePower":null,"phaseCReactivePower":null,"totalPowerFactor":1.0,"phaseAPowerFactor":0.0,"phaseBPowerFactor":0.0,"phaseCPowerFactor":0.0,"totalForwardActiveEnergy":null,"totalReverseActiveEnergy":null,"combinedReactivePower1TotalElectricEnergy":null,"combinedReactivePower2TotalElectricEnergy":null,"firstQuadrantIdleElectricEnergy":null,"secondQuadrantIdleElectricEnergy":null,"thirdQuadrantIdleElectricEnergy":null,"fourthQuadrantIdleElectricEnergy":null,"currentIdleElectriceEnergy":null,"currentActiveElectriceEnergy":null},"class":"C"} 2021-04-20 09:19:27 10 0 16
6 {"gwid":"fc6bf0fffe6a7241","rssi":-33,"snr":11.25,"freq":471.7,"dr":0,"adr":true,"fCnt":5,"fPort":8,"confirmed":false,"data":"kRkCAAQGABgSASEAAAAAAAAAAP//","objectData":{"dataTime":1610445600000,"di":"06040002","phaseAVoltage":null,"phaseBVoltage":null,"phaseCVoltage":null,"phaseACurrent":null,"phaseBCurrent":null,"phaseCCurrent":null,"frequency":null,"totalActivePower":null,"phaseAActivePower":null,"phaseBActivePower":null,"phaseCActivePower":null,"totalReactivePower":null,"phaseAReactivePower":null,"phaseBReactivePower":null,"phaseCReactivePower":null,"totalPowerFactor":null,"phaseAPowerFactor":null,"phaseBPowerFactor":null,"phaseCPowerFactor":null,"totalForwardActiveEnergy":0.0,"totalReverseActiveEnergy":0.0,"combinedReactivePower1TotalElectricEnergy":0.0,"combinedReactivePower2TotalElectricEnergy":0.0,"firstQuadrantIdleElectricEnergy":null,"secondQuadrantIdleElectricEnergy":null,"thirdQuadrantIdleElectricEnergy":null,"fourthQuadrantIdleElectricEnergy":null,"currentIdleElectriceEnergy":null,"currentActiveElectriceEnergy":null},"class":"C"} 2021-04-20 09:19:35 10 0 16
mysql 取数据库里json中的某个对象进行判断
最新推荐文章于 2024-07-28 19:31:32 发布