最近碰到一个问题,需要查询mysql的JSON字段,需求是类型为某种生日且月份为当月。因为某种生日的位置不固定,无法通过$[position].date筛选数据。
解决方案:找到最长长度,查出来出现的位置,然后用or将所有可能写出。
select anniversaries,date_format(DATE_ADD(now(),interval 8 hour), '%Y%m') monthkey,
2 comm_type,
belong_to_contactID,unique_contact_id,
case position when 0 then concat('儿童生日:',DATE_FORMAT(anniversaries->>'$[0].date','%m.%d'),'|ChildBT_',TIMESTAMPDIFF(YEAR, anniversaries->>'$[0].date', DATE_ADD(now(),interval 8 hour))) when 1 then concat('儿童生日:',DATE_FORMAT(anniversaries->>'$[1].date','%m.%d'),'|ChildBT_',TIMESTAMPDIFF(YEAR, anniversaries->>'$[0].date', DATE_ADD(now(),interval 8 hour))) when 2 then concat('儿童生日:',DATE_FORMAT(anniversaries->>'$[2].date','%m.%d'),'|ChildBT_',TIMESTAMPDIFF(YEAR, anniversaries->>'$[0].date', DATE_ADD(now(),interval 8 hour))) end comm_info,
case position when 0 then concat(date_format(DATE_ADD(now(),interval 8 hour), '%Y'),DATE_FORMAT(anniversaries->>'$[0].date','-%m-%d'),' 23:59:59') when 1 then concat(date_format(DATE_ADD(now(),interval 8 hour), '%Y'),DATE_FORMAT(anniversaries->>'$[1].date','-%m-%d'),' 23:59:59') when 2 then concat(date_format(DATE_ADD(now(),interval 8 hour), '%Y'),DATE_FORMAT(anniversaries->>'$[2].date','-%m-%d'),' 23:59:59') end expire_date,
contact_name,
case position when 0 then concat(date_format(DATE_ADD(now(),interval 8 hour), '%Y'),DATE_FORMAT(anniversaries->>'$[0].date','-%m-%d'),' 23:59:59') when 1 then concat(date_format(DATE_ADD(now(),interval 8 hour), '%Y'),DATE_FORMAT(anniversaries->>'$[1].date','-%m-%d'),' 23:59:59') when 2 then concat(date_format(DATE_ADD(now(),interval 8 hour), '%Y'),DATE_FORMAT(anniversaries->>'$[2].date','-%m-%d'),' 23:59:59') end business_date from (
select anniversaries, SUBSTRING_INDEX( SUBSTRING_INDEX(JSON_SEARCH(anniversaries, 'all', 'CHILD_BIRTH_DATE'),'[',-1) ,']',1) position ,belong_to_contactID,unique_contact_id,contact_name
from contact c where c.removed=0
)allTotal where position=0 and month(anniversaries ->> '$[0].date')=month(DATE_ADD(now(),interval 8 hour))
or position=1 and month(anniversaries ->> '$[1].date')=month(DATE_ADD(now(),interval 8 hour))
or position=2 and month(anniversaries ->> '$[2].date')=month(DATE_ADD(now(),interval 8 hour))