需求:需要将下边json字段中的endTime 截取成2023-12-27的形式,然后并对参数进行判断
{"duration":20,"deviceType":"减压赋能舱","orderCode":"2023122717092684615","expectedEndTime":"2023-12-27 17:29:27","beginTime":"2023-12-27 17:09:27","endTime":"2023-12-27 17:17:15","list":[{"relax_date":"2023-12-27 17:13:04","heartRate":51,"relaxation":92,"tired":27,"compression":94},{"relax_date":"2023-12-27 17:16:42","heartRate":97,"relaxation":91,"tired":42,"compression":68}],"account":"SYH00294"}
分享一下SUBSTRING截取的坑
一开始用怎么截取都是会报错的,然后各种搜索,看是哪里的问题始终没解决,后来才知道
上边这种截取方式 会把 " 双引号截取出来,然后就截取10位,一直报错,后来截取11位 参数加了个双引号,就满足条件了,不过 这不是最优解。正解如下:
正解1:
SELECT
*
FROM
tenant_sleep_pod_data tspd
WHERE
tspd.is_deleted = 0 AND
tspd.tenant_id = 'f210b877525c2ca4e8da9a5f4c2056d0' and right(left(JSON_EXTRACT(tspd.sleep_pod_json_value, '$.endTime'),11),10)
= '2023-12-21';
正解2:
SELECT
*
FROM
tenant_sleep_pod_data tspd
WHERE
tspd.is_deleted = 0 AND
tspd.tenant_id = 'f210b877525c2ca4e8da9a5f4c2056d0' and left(tspd.sleep_pod_json_value->> '$.endTime',10)
= '2023-12-21';
基本是 left函数的语法问题,-> 带引号,->> 不带引号